IF Formula / #Value! Error

[erin-5304]

Having trouble figuring out what is causing the #Value! error in the following formula:

=IF(E18="Flat Rate",IF(C46>360,SUM(C46/2-150)*8),IF(E18>=0.01,IF(C46>300,SUM(C46/2-150)*8),"")

The answer will be decided using 2 different cells of information, E18 and C46.  Any ideas?

[#2]

@erin-5304 

Most likely C46 contains text. Check with ISNUMBER() or ISTEXT().

[#3]

C46 would only contain a number. E18 is the only cell that could possibly have text.

[#4]

@erin-5304 

Please explain in words what the formula should do.

[#5]

@erin-5304 

But there is no arithmetic operation with E18.

[#6]

First possibility:
If E18 says "Flat Rate" and if C46 is greater than 360 then divide the number in C46 by 2, take away 150, then multiply that total by 8; if E18 says "Flat Rate" and C46 is less than or equal to 360 then it should be $0.00.

Second possibility:
If E18 has a number instead of text that is greater than $0.01 and if C46 is greater than 300 then divide the number in C46 by 2, take away 150, then multiply that total by 8; if E18 has a number instead of text that is greater than $0.01 but C46 is less than or equal to 300 then it should be $0.00.

If E18 equals $0, then it should be $0

[#7 · Accepted solution]

@erin-5304 

Try

=IF(E18="Flat Rate",IF(C46>360,(C46/2-150)*8,0),IF(E18>=0.01,IF(C46>300,(C46/2-150)*8,0),0))

[#8]

Works great, and I see where I messed up now. Thanks for your help!

[#9]

@erin-5304 

As variant

=IF(
    ISTEXT(E18),
    IF(E18 = "Flat Rate", (C46 > 360) * (C46 / 2 - 150) * 8, ""),
    IF(E18 >= 0.01, (C46 > 300) * (C46 / 2 - 150) * 8, 0)
)

[#10]

@HansVogelaar 

I guess with "abc" in E18 result will be incorrect.

[#11]

@SergeiBaklan 

You're correct.