Excel formula

Copper Contributor

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
The last one is the closest
=IF(AND(A1>0,A1<100),1,"")

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

 

 

@KBCHFRE 

As variant

=IF(MOD(A1/100,1),1,"")

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.

@Rajesh_Sinha 

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.

I want to say that what would be formula if the value to test are an odd combination,, like 250 to 350 , since A1/100 or A1/200 or A1/250 is straight forward 1-99, 1-199 & 1-249, will help to beginners !