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

 

[mabbutt]

Just Google or search the forum for nested IF statements.

IF(condition1, result1, IF(condition2, result2, IF(condition3, result3, result4)))

 

[ssbhatban]

D

Just Google or search the forum for nested IF statements.

IF(condition1, result1, IF(condition2, result2, IF(condition3, result3, result4)))

Dear friend
I am beginner in excel & googled. I got some syntax for nested multiple if, I have got it right for two numbers, but I have 4 nos for each Criteria. I am not able to solve it

 

[mabbutt]

This link should be what you need and then nest the statements accordingly.

 

[Peter_SSs]

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

Are you sure those values are correct. It sounds like you are awarding high points for getting close to 70? However, the values on either side of 70 are not evenly matched as shown below.
Green ones match each side but amber ones do not.

Book1
	H	I	J	K	L	M	N	O	P	Q	R
11	68.95	68.99	69	69.94	69.95	70	70.05	70.06	70.1	70.11	70.15
12	1.05	1.01	1	0.06	0.05		0.05	0.06	0.1	0.11	0.15
Points

.. or have I missed the point?

 

[Saba Sabaratnam]

Or you can use the following formula:

=IFS(AND(D5>=69.95,D5<=70.05),10,AND(D5>=69.94,D5<=69),9,AND(D5>=70.06,D5<=70.1),9,AND(D5>=68.99,D5<=68.95),7,AND(D5>=70.11,D5<=70.15),7)

Please note IFS formula is available from 2016 Excel.

Kind regards

Saba

 

[ssbhatban]

Or you can use the following formula:

=IFS(AND(D5>=69.95,D5<=70.05),10,AND(D5>=69.94,D5<=69),9,AND(D5>=70.06,D5<=70.1),9,AND(D5>=68.99,D5<=68.95),7,AND(D5>=70.11,D5<=70.15),7)

Please note IFS formula is available from 2016 Excel.

Kind regards

Saba

Thanks guys, got it, working fine. Thanks for all of your help

 

[Peter_SSs]

Thanks guys, got it, working fine

Good news! What did you end up using?

 

[ssbhatban]

yes you are right, the values had little problem, thanks for bringing it to notice

i used this

=IF(AND(D5>=69.95,D5<=70.05),"10",IF(AND(D5>=69.9,D5<=70.1),"9",IF(AND(D5>=69.85,D5<=70.15),"7",IF(AND(D5>=69.8,D5<=70.2),"5",IF(AND(D5>=0,D5<=100),"2",)))))

 

[Peter_SSs]

i used this

=IF(AND(D5>=69.95,D5<=70.05),"10",IF(AND(D5>=69.9,D5<=70.1),"9",IF(AND(D5>=69.85,D5<=70.15),"7",IF(AND(D5>=69.8,D5<=70.2),"5",IF(AND(D5>=0,D5<=100),"2",)))))

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})