Nested If Statement Involving Calculations - Having Issues

[tomcat212]

Hello Everyone,

I am new to the community and am reaching out for help with this one! My spreadsheet is testing for the following conditions:

- If cell A1 is < 30, cell B1 must be within plus or minus 2 of cell A1
- If cell A1 is between 31 - 100, cell B1 it must be within plus or minus 3 of cell A1
- If cell A1 is > 100, cell B1 must be within plus or minus 6 of cell A1

For context, I am doing a series of measurements where the designated measured values are in cell A1 and my measured results are in cell B1. The designated measured values can range from 25 - 120. I am able to have the formula work for range of values 31 - 100 using the following:

IF(AND(A1>=31,A1<=100),IF(OR(B1<=A1-3,B1>=A1+3),"*Fail","Pass"))

However, I am having problems when combining all ranges and criteria into a single formula. Any help or suggestions would be very much appreciated!!

Thank you!

[XLent]

would the below work for you?

=IF(ABS(A1-B1)<=LOOKUP(A1,{25,31,101},{2,3,6}),"Pass","Fail")

have assumed you only have whole numbers (implied in your post)

[etaf]

i think this will do
=IF(OR(AND(A1<=30,B1>=(A1-2),B1<=(A1+2)),AND(A1>30,A1<=100,B1>=(A1-3),B1<=(A1+3)), AND(A1>100,B1>=(A1-6),B1<=(A1+6)) ),"pass","fail")

[sean.d.reeves]

=IF(ABS(A1-B1)<=LOOKUP(A1,{25,31,101},{2,3,6}),"Pass","Fail")

That's slick!

[tomcat212]

Thank you very much for your suggestions and help, it's working now!

Thanks!!