 Highlighted

# 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
Highlighted

# Re: Excel formula

The last one is the closest
=IF(AND(A1>0,A1<100),1,"")
Highlighted

# Re: Excel formula

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

Highlighted

# Re: Excel formula

As variant

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

# Re: Excel formula

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.

Highlighted

# Re: Excel formula

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.

Highlighted

# Re: Excel formula

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 !