IF function when reference cell is a blank Cell with Formula

[Courtney93]

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!

[#2 · Accepted solution]

@Courtney93 

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)

[#3]

Hans,

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

[#4]

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

[#5]

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