Multiple if conditions or validation

[ssbhatban]

Dear friends
I am evaluating the dimensions achieved in a metal Shaft & entering it in excel sheet. Points out of 10 are to be alloted depending upon the dimension achieved
I am entering the dimension achieved in D5 & points should come in E5 depending on the dimension entered. I have three conditions. For example
If the value is between 69.95 and 70.05 then return value is 10
Value between 69.94 & 69.00 & value between 70. 06 & 70 10 return value is 9
Value between 68.99 & 68.95 & value between 70.11 & 70.15 return value is 7

Kindly help me with the syntex. Thanks in advance

 

[ssbhatban]

Unless you are specifically trying to return text values, you would be best to remove the quotes from around all the numerical values to return (I've highlighted just a few)

From what I can tell, you could also use this much shorter formula with no IFs at all.

=LOOKUP(ABS(70-D5),{0,0.0501,0.101,0.1501,0.201},{10,9,7,5,2})

Thank u very much, i will remove the quotes, but I did not fully understand the second syntax as how it works. As far as my understanding, leaving out 70 u have given all the decimals on +ve side only. Mine may go - ve also. For example value can be 69.96 or 70.07

 

[Peter_SSs]

u have given all the decimals on +ve side only. Mine may go - ve also.

That is covered by my use of ABS() which converts any negative difference to a positive one. If you test, you should find it works on the negative side as well.

 

[ssbhatban]

That is covered by my use of ABS() which converts any negative difference to a positive one. If you test, you should find it works on the negative side as well.

 

[ssbhatban]

Thanks, i will try it out