Multiple IF Statement - Where am I going wrong?!

[tlc53]

Hi there,

Can anyone tell me why the following IF statement is not working? Regardless of the values in cells G51 and H51 (which are formulated) the IF statement result doesn't change from "Total Under Claimed".

=IF(G51>0,"Total Under Claimed",IF(H51>0,"Total Over Claimed",""))

Thank you!

 

[jtakw]

Hi,

Since you're testing G51 and H51 for >0, which means G51 and H51 should contain Numbers, that suggests your formulas in G51 and H51 are returning Text rather than Numbers.

So, would need to see your G51 and H51 formulas to try to fix the issue at the "cause",

Or you can try to convert the Text numbers within your formula:

=IF(G51+0>0,"Total Under Claimed",IF(H51+0>0,"Total Over Claimed",""))

 

[jtakw]

Also, a formula Blank ( "" ) in G51 and H51 would also cause your Formula above to error out, if that's the case, you need to test for G51<>"" and G51>0, likewise with H51.

 

[tlc53]

Hi. Thanks. I see what you are getting at.

I have now amended the formulas in G51 and H51, so that if false equals "0" rather than "". With this and your revised formula, it now works.

However, I really wanted G51 and H51 to return Blank Cell if zero, rather than zero.

Here's the code in cell G51: =IF(G50>H50,G50-H50,"0")

I could do a conditional formatting to white out any zero's in G51 and H51 but I was just wondering if you knew a way around it by amending the formula further.

Thanks!

 

[jtakw]

No need, keep your original formula in G51 and H51 to return a formula Blank ( "" ), and use this version of the formula in your OP:

=IF(N(G51)>0,"Total Under Claimed",IF(N(H51)>0,"Total Over Claimed",""))

The added N function will convert the formula Blank to 0, and leave All numbers intact.

 

[tlc53]

That's clever! Thank you, works perfectly now

 

[jtakw]

You're welcome, glad it works for you, thanks for the feedback.

 

[tlc53]

Hi again! I'm working on another formula and I'm wondering whether this N formula will help me out again but I can't get it to work.

This is what I have put but it doesn't make a difference;

Cell G277 =IF(N(H22)>0,H22,"")

The above cell is referred to in two other cells
Cell K277 =IF(F227+G227>0,Preparer,"")
Cell L277 =IF(F227+G227>0,TODAY(),"")

If in cell G277 I put =IF(H22>0,H22,"") it returns #VALUE ! in the above two cells but if I put =IF(H22>0,H22,"0") the above two cells correctly return blank. However, I don't want "0" showing in my G277 cell.

Can I use the N function here and if so, can you please tell me how I apply it correctly?

Thank you!

 

[jtakw]

Could H22, and F227 result in a Formula Blank ( "" ) ?

 

[jtakw]

Assuming they do:

Book1
	I	J
1	for G277-->
2	for K277-->
3	for L277-->
Sheet571

Cell Formulas
Range	Formula
J1	=IF(N(H22)>0,H22,"")
J2	=IF(N(F227)+N(G227)>0,"Preparer","")
J3	=IF(N(F227)+N(G227)>0,TODAY(),"")