Forum Discussion
Excel formula
Greetings, I'm seeking a formula that returns a 1 or blank if the value in the cell is >0 and <100 so 1-99=1 but 0 and 100 = blank. I have tried so many variations....
=IF(A1>0<100,1,"")
=IF(A1>0, A1<100,1,"")
=IF(AND(A1>0,<100,1,"")
6 Replies
This one is good one, but you need to explain how to adjust formula for different value range like, between 1 to 199 or 200 and 350.
Sorry, I didn't catch what exactly shall be explained. MOD(...,1) returns zero (or FALSE) on any integer, other words on any 100x{0,1,2,...} divided on 100. Otherwise it returns some non-zero number, aka TRUE.
KBCHFRE ,,,
Your formula =IF(AND(A1>0,A1<100),1,"") is the most appropriate, checks value between 1 and 99.
Also you may try these one:
=IF(AND(A1>=1,A1<=99),1,"") Or you may use this also =IF((A1>=1)*(A1<=99),1,"")- The last one is the closest
=IF(AND(A1>0,A1<100),1,"")
