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

4 Replies
best response confirmed by Courtney93 (New Contributor)
Solution

# Re: IF function when reference cell is a blank Cell with Formula

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)

# Re: IF function when reference cell is a blank Cell with Formula

Hans,

Thank you! Using 0 instead of "0" did work.

# Re: IF function when reference cell is a blank Cell with Formula

@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.

# Re: IF function when reference cell is a blank Cell with Formula

@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.