Forum Discussion
Solved
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 F1...
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)
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.
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.- bwy1129 Thank you for explanation! This really helps, I figured it had to do with the number 0 being a value and "0" being text but I didn't know the rest.
