if statement help

[Brianfree]

hi I would like to modify the following statement so if H2 is null then nothing is displaed.

=IF(AND(H2<C2,H2>0),"LOW STOCK",IF(AND(H2>=C2,H2<>""),"HIGH STOCK","NO STOCK"))

Please can anyone advise?

Many thanks

Brian

 

[zelgar]

I'm not certain, but the following might work. I'm not certain about the "NO STOCK" option since based on your original code it could only occur if H2>=C2 and is either blank or equal to 0.

=IF(ISBLANK(H2),,IF(H2>=C2,IF(H2=0,"NO STOCK","HIGH STOCK),"LOW STOCK"))

 

[randy700]

Something like this?

=[RED][B]IF(H2="","NO CHANGE",[/B][/RED]IF(AND(H2<C2,H2>0),"LOW STOCK",IF(AND(H2>=C2,H2<>""),"HIGH STOCK","NO STOCK"))[B][RED])[/B][/RED]

Randy

 

[Deniall]

You have four situations for cell H2:[&nbsp;] null, 0, <C2, >=C2.[&nbsp;] Test them in that order.[&nbsp;] Thus
=IF(ISBLANK(H2),"", IF(H2=0,"No stock", IF(H2<C2,"Low stock",High stock")))

 

[kjv1611]

zelgar's solution has a typo, and doesn't give the desired result:[ol 1]
[li]Formula missing an end parenthesis, could have been a copy/paste issue, but just realize that if you try it, and Excel 2010 will autocorrect it for you if you allow it to, so that becomes a moot point.[/li]
[li]The result is 0, not blank/null.[/li]
[/ol]

randy700's solution would work if edited. He put "NO CHANGE" where the desired value would simply be ""

Deniall's solution works, and seems to give the desired outcome if you correct the one typo error:

=IF(ISBLANK(H2),"", IF(H2=0,"No stock", IF(H2<C2,"Low stock",[highlight]"[/highlight]High stock")))

(It was missing one quotation mark on the final value). At least Excel 2010 sees this and fixes it if you accept the modification.

Brianfree, Please post back with what solution you go with, whether one here, or if you found or came up with one on your own.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57