Possible If/Then formula? Calculate between 2 numbers

[kittymongo]

Does anyone know how to calculate between 2 numbers. Example -

one report has to pay accurately which is between 95%-102% (we count these as accurate). another report has to pay the same way (these 2 never match since they pull from different sources). I need a formula to show if these 2 calculate within the same data range. i have a formula in there but it is just stating if the 2 reports match per line but i am getting false matches since it is matching everything out of range as well.

report 1
cell a1 = 30% accurate
cell a2 = 98% accurate
cell a3 = 100% accurate
cell a4 = 94% accurate
cell a5 = 99% accurate

report 2
cell a1 = 30% accurate
cell a2 = 100% accurate
cell a3 = 100% accurate
cell a4 = 22% accurate
cell a5 = 91% accurate

what sort of formula can be used to show both reports are within the accurate threshold range by line?

i hope this makes sense. if not, let me know and i will try to clarify more.

Thank you so much!

 

[dreid1011]

Based on your sample, what should the results be line by line?

 

[kittymongo]

report 1
cell a1 = 30% accurate
cell a2 = 98% accurate
cell a3 = 100% accurate
cell a4 = 94% accurate
cell a5 = 99% accurate

report 2
cell b1 = 30% accurate
cell b2 = 100% accurate
cell b3 = 100% accurate
cell b4 = 22% accurate
cell b5 = 91% accurate

What formula can i use in cell c1, c2, c3, etc (it is a line by line calculation) to show if both reports are displaying between 95%-102% range?

 

[dreid1011]

Is this what you expect to see?


[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]30%[/TD]
[TD]30%[/TD]
[TD]Out of Range[/TD]
[/TR]
[TR]
[TD]98%[/TD]
[TD]100%[/TD]
[TD]In Range[/TD]
[/TR]
[TR]
[TD]100%[/TD]
[TD]100%[/TD]
[TD]In Range[/TD]
[/TR]
[TR]
[TD]94%[/TD]
[TD]22%[/TD]
[TD]Out of Range[/TD]
[/TR]
[TR]
[TD]99%[/TD]
[TD]91%[/TD]
[TD]Out of Range[/TD]
[/TR]
</tbody>[/TABLE]

 

[petertenthije]

=and(and($a1>=0.95;$a1<=1.02),and($b1>=0.95,$b1<=1.02))

 

[kittymongo]

Yes, this is what I want to see. What formula can I use to get this result? Pretty Please

Is this what you expect to see?


[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]30%[/TD]
[TD]30%[/TD]
[TD]Out of Range[/TD]
[/TR]
[TR]
[TD]98%[/TD]
[TD]100%[/TD]
[TD]In Range[/TD]
[/TR]
[TR]
[TD]100%[/TD]
[TD]100%[/TD]
[TD]In Range[/TD]
[/TR]
[TR]
[TD]94%[/TD]
[TD]22%[/TD]
[TD]Out of Range[/TD]
[/TR]
[TR]
[TD]99%[/TD]
[TD]91%[/TD]
[TD]Out of Range[/TD]
[/TR]
</tbody>[/TABLE]

 

[dreid1011]

Building from petertenthije's formula, try this:

=IF(and($a1>=0.95;$a1<=1.02,$b1>=0.95,$b1<=1.02),"In Range","Out of Range")

 

[kittymongo]

Perfect! Thank you so much! This worked like a charm and is exactly what I am looking for.

 

[dreid1011]

You're welcome.