Excel formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1606609%22%20slang%3D%22en-US%22%3EExcel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1606609%22%20slang%3D%22en-US%22%3E%3CP%3EGreetings%2C%20I'm%20seeking%20a%20formula%20that%20returns%20a%201%20or%20blank%20if%20the%20value%20in%20the%20cell%20is%20%26gt%3B0%20and%20%26lt%3B100%20so%201-99%3D1%20but%200%20and%20100%20%3D%20blank.%26nbsp%3B%20I%20have%20tried%20so%20many%20variations....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(A1%26gt%3B0%26lt%3B100%2C1%2C%22%22)%3C%2FP%3E%3CP%3E%3DIF(A1%26gt%3B0%2C%20A1%26lt%3B100%2C1%2C%22%22)%3C%2FP%3E%3CP%3E%3DIF(AND(A1%26gt%3B0%2C%26lt%3B100%2C1%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1606726%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1606726%22%20slang%3D%22en-US%22%3EThe%20last%20one%20is%20the%20closest%3CBR%20%2F%3E%3DIF(AND(A1%26gt%3B0%2CA1%26lt%3B100)%2C1%2C%22%22)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1706975%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1706975%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F613207%22%20target%3D%22_blank%22%3E%40KBCHFRE%3C%2FA%3E%26nbsp%3B%2C%2C%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20formula%26nbsp%3B%3DIF(AND(A1%26gt%3B0%2CA1%26lt%3B100)%2C1%2C%22%22)%20is%20the%20most%20appropriate%2C%20checks%20value%20between%201%20and%2099.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%20you%20may%20try%20these%20one%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(AND(A1%26gt%3B%3D1%2CA1%26lt%3B%3D99)%2C1%2C%22%22)%0A%0AOr%20you%20may%20use%20this%20also%0A%0A%3DIF((A1%26gt%3B%3D1)*(A1%26lt%3B%3D99)%2C1%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1707427%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1707427%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F613207%22%20target%3D%22_blank%22%3E%40KBCHFRE%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(MOD(A1%2F100%2C1)%2C1%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1707542%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1707542%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20one%20is%20good%20one%2C%20but%20you%20need%20to%20explain%20how%20to%20adjust%20formula%20for%20different%20value%20range%20like%2C%20between%26nbsp%3B%201%20to%20199%20or%20200%20and%20350.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1709311%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1709311%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F93699%22%20target%3D%22_blank%22%3E%40Rajesh-S%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESorry%2C%20I%20didn't%20catch%20what%20exactly%20shall%20be%20explained.%20MOD(...%2C1)%20returns%20zero%20(or%20FALSE)%20on%20any%20integer%2C%20other%20words%20on%20any%20100x%7B0%2C1%2C2%2C...%7D%20divided%20on%20100.%20Otherwise%20it%20returns%20some%20non-zero%20number%2C%20aka%20TRUE.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1711795%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1711795%22%20slang%3D%22en-US%22%3EI%20want%20to%20say%20that%20what%20would%20be%20formula%20if%20the%20value%20to%20test%20are%20an%20odd%20combination%2C%2C%20like%20250%20to%20350%20%2C%20since%20A1%2F100%20or%20A1%2F200%20or%20A1%2F250%20is%20straight%20forward%201-99%2C%201-199%20%26amp%3B%201-249%2C%20will%20help%20to%20beginners%20!%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

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

@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

@KBCHFRE 

As variant

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

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

@Rajesh-S 

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