Forum Discussion

Copper Contributor

Aug 23, 2020

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...

SergeiBaklan

Diamond Contributor

Sep 24, 2020

KBCHFRE

As variant

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

Rajesh_Sinha
Iron Contributor
Sep 24, 2020
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.
- SergeiBaklan
  Diamond Contributor
  Sep 24, 2020
  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.
  - Rajesh_Sinha
    Iron Contributor
    Sep 25, 2020
    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 !