SOLVED

# IF Formula / #Value! Error

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?

10 Replies

# Re: IF Formula / #Value! Error

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

# Re: IF Formula / #Value! Error

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

# Re: IF Formula / #Value! Error

Please explain in words what the formula should do.

# Re: IF Formula / #Value! Error

But there is no arithmetic operation with E18.

# Re: IF Formula / #Value! Error

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

best response confirmed by erin-5304 (Contributor)
Solution

# Re: IF Formula / #Value! Error

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))

# Re: IF Formula / #Value! Error

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

# Re: IF Formula / #Value! Error

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)
)``````

# Re: IF Formula / #Value! Error

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

You're correct.