Forum Discussion
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 answ...
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))
Please explain in words what the formula should do.
- 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 $0As 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) )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))
I guess with "abc" in E18 result will be incorrect.
