SOLVED

IF function when reference cell is a blank Cell with Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2700014%22%20slang%3D%22en-US%22%3EIF%20function%20when%20reference%20cell%20is%20a%20blank%20Cell%20with%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2700014%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20attached%20a%20screen%20shot%20of%20my%20spreadsheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20feel%20like%20this%20is%20a%20simple%20problem%20but%20it%20is%20driving%20me%20crazy!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20Cell%20G11%20I%20have%20the%20formula%20%3DIF(F11%26gt%3B40%2CF11-40%2C%220%22)%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20value%20of%20cell%20F11%20is%200%2C%20but%20it%20does%20have%20the%20formula%26nbsp%3B%3DIF(SUM(F4%3AF10)%26gt%3B0%2CSUM(F4%3AF10)%2C%220%22).%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20formula%20in%20G11%20thinks%20the%20value%20in%20cell%20F11%20is%20larger%20than%2040%20and%20is%20subtracting%2040%20when%20it%20should%20actually%20be%20%220%22.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20question%20is%2C%20how%20can%20I%20use%20cell%20F11%20in%20my%20formula%20in%20cell%20G11%20to%20do%20what%20I%20want%20when%20it%20thinks%20there%20is%20a%20value%20there%20when%20there%20isn't%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20hard%20time%20putting%20my%20question%20into%20words%2C%20so%20hopefully%20I%20am%20not%20too%20confusing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2700014%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2700137%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20function%20when%20reference%20cell%20is%20a%20blank%20Cell%20with%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2700137%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1141650%22%20target%3D%22_blank%22%3E%40Courtney93%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUse%200%20instead%20of%20%220%22%20in%20the%20formulas.%20You%20can%20use%20the%20MAX%20function%20instead%20of%20an%20IF%20formula%3A%3C%2FP%3E%0A%3CP%3EIn%20F11%3A%20%3DMAX(SUM(F4%3AF10)%2C0)%3C%2FP%3E%0A%3CP%3EIn%20G11%3A%20%3DMAX(F11-40%2C0)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2700213%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20function%20when%20reference%20cell%20is%20a%20blank%20Cell%20with%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2700213%22%20slang%3D%22en-US%22%3EHans%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you!%20Using%200%20instead%20of%20%220%22%20did%20work.%3C%2FLINGO-BODY%3E
New Contributor

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

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

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