Aug 23 2020 07:29 AM
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,"")
Sep 24 2020 01:20 AM - edited Sep 24 2020 01:39 AM
@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,"")
Sep 24 2020 03:48 AM - edited Sep 24 2020 03:51 AM
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.
Sep 24 2020 09:24 AM
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.
Sep 24 2020 11:43 PM - edited Sep 24 2020 11:44 PM