Forum Discussion
IF function when reference cell is a blank Cell with Formula
I have attached a screen shot of my spreadsheet.
I feel like this is a simple problem but it is driving me crazy!
In Cell G11 I have the formula =IF(F11>40,F11-40,"0")
The value of cell F11 is 0, but it does have the formula =IF(SUM(F4:F10)>0,SUM(F4:F10),"0").
My formula in G11 thinks the value in cell F11 is larger than 40 and is subtracting 40 when it should actually be "0".
My question is, how can I use cell F11 in my formula in cell G11 to do what I want when it thinks there is a value there when there isn't?
I have a hard time putting my question into words, so hopefully I am not too confusing.
Thank you!
Use 0 instead of "0" in the formulas. You can use the MAX function instead of an IF formula:
In F11: =MAX(SUM(F4:F10),0)
In G11: =MAX(F11-40,0)
4 Replies
Use 0 instead of "0" in the formulas. You can use the MAX function instead of an IF formula:
In F11: =MAX(SUM(F4:F10),0)
In G11: =MAX(F11-40,0)
- Hans,
Thank you! Using 0 instead of "0" did work.- Courtney93, here's why 0 works and "0" doesn't.
To Excel, 0 is classified as a "Number" while "0" is classified as "Text" or "General". The value of number 0 is zero. The value of text 0 is 48. Thus the test IF(F11>40,F11-40,0) sees a character whose value at some level of logic is greater 40, so the test comes out TRUE. (It shouldn't, but for some reason, that is what is happening.) . However, when the [logical test] portion of your formula renders TRUE, then the [value_if_true] portion calculates. The calculation works only on number values, not text values. The calculation sees "nothing" minus 40 and puts -40 into G11 as a result.
