if logical formula

[debbieharvey]

Hi excel gurus
I need a formula that will return a value of 1 in a cell, if the contents of another cell are between 2 ranges, ie, it needs to return a value of 1 if the test cell contains any number between 60 and 64 or 86 and 90. if the value in the test cell is not within these 2 ranges, it needs to return a value of 0... I have tried using if(and but can only get it to work for 1 range of values, not 2. perhaps there is an easier way? this is for setting up colour formatting in KPIs.. so once I get this sorted, the rest should be a breeze.. thanks in advance
DH

 

[Toadstool]

Does this work?

=IF(OR(AND(F2>=60,F2<=64),AND(F2>=86,F2<=90)),1,0)

[TABLE="class: grid, width: 128, align: left"]
<colgroup><col style="width:48pt" width="64" span="2"> </colgroup><tbody>[TR]
[TD="width: 64, align: center"]Result
[/TD]
[TD="width: 64, align: center"]Number[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]55[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]66[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]77[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]88[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]99[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]60[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]64[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]86[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]90[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Another option
=--OR(AND(A2>=60,A2<=64),AND(A2>=86,A2<=90))

 

[debbieharvey]

thanks. so grateful

 

[Fluff]

Glad we could help & thanks for the feedback