Why doesn't my If And formula work?

[DonnaJoh]

Good Morning:

I am trying to utilize the following If And formula but it doesn't seem to be working.

=IF(AND(O8>="95",<="100","25",IF(AND(08>="85",<="94","20",IF(AND(O8>="70",<="84","15",IF(AND(O8>="50",<="69","10",IF(AND(O8>="30",<="49","5",IF(O<="29","0","null")))))))))))

I am trying to display a set value of points for a score with a range.


[TABLE="width: 232"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Score Range[/TD]
[TD][/TD]
[TD]=[/TD]
[TD]


[/TD]
[TD]

# of Pts[/TD]
[/TR]
[TR]
[TD]95to100% 85to94%
70to84% 50to69% 30to49% <=29% [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]25 20 15 10 5 0[/TD]
[/TR]
</tbody>[/TABLE]

How can I fix this?

 

[FormR]

Yes, that at least solves the Sum question.....thank you sooo much!!

Thinking more, wouldn't just =AVERAGE(Q8:AB8) suffice?

Now if I can only resolve the original equation issue

Did you try?

=5*(MATCH(O8,{0,30,50,70,85,95}/100)-1)

 

[DonnaJoh]

Yes, I tried both options posted and "#Name?" is displayed in both events. The value of O8 is 96% which is tabulated from the following formula:=SUM(Q8:AB8)/COUNT(Q8:AB8)

 

[FormR]

"#Name?" is displayed in both events.

If you are not using Excel with english language settings then you will need to make the necessary translation for the MATCH() function.

Excel 2013/2016
	O	P
8	96%	25
Sheet1

Cell Formulas
Range	Formula
P8	=5*(MATCH(O8,{0,30,50,70,85,95}/100)-1)

 

[DonnaJoh]

I just tried " =5*(MATCH(O8,{0,30,50,70,85,95}/100)-1)

" and it only displayed a 0 when O9 was 89%. This should have yielded an answer of 20, not 0. ​

 

[DonnaJoh]

I am using excel with English language settings

 

[FormR]

and it only displayed a 0 when O9 was 89%. This should have yielded an answer of 20, not 0

Hi, O8 or O9? - btw it returns 20 for me with 89% in O8.

Excel 2013/2016
	O	P
8	89%	20
Sheet1

Cell Formulas
Range	Formula
P8	=5*(MATCH(O8,{0,30,50,70,85,95}/100)-1)

I am using excel with English language settings

I'm guessing the #NAME ? error has resolved itself

 

[DonnaJoh]

Dahhh...that's it! I tried it in a different cell and did not change the formula to reflect the cell I was now in. Yes, it now works!!! Thank you so very much!!! You all made my day!!!!

 

[DonnaJoh]

Yes the

=5*(​

MATCH(O8,{0,30,50,70,85,95}/100)-1​

) formula resolved the #NAME ? error problem. Thank you very much!!!​

 

[markmzz]

Hi DonnaJoh,

Try the formulas below too:

In O8 and copy down

=IFERROR(AVERAGE(Q8:AB8),"")

In P8 and copy down

=IF(O8="","null",LOOKUP(O8,{0,0;0.3,5;0.5,10;0.7,15;0.85,20;0.95,25}))

[TABLE="class: grid, width: 775"]
<tbody>[TR]
[TD][/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[TD]AA[/TD]
[TD]AB[/TD]
[TD]AC[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]Average[/TD]
[TD]Result[/TD]
[TD]Data01[/TD]
[TD]Data02[/TD]
[TD]Data03[/TD]
[TD]Data04[/TD]
[TD]Data05[/TD]
[TD]Data06[/TD]
[TD]Data07[/TD]
[TD]Data08[/TD]
[TD]Data09[/TD]
[TD]Data10[/TD]
[TD]Data11[/TD]
[TD]Data12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD="align: right"]65%[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]46%[/TD]
[TD="align: right"]73%[/TD]
[TD][/TD]
[TD="align: right"]69%[/TD]
[TD="align: right"]37%[/TD]
[TD="align: right"]74%[/TD]
[TD="align: right"]95%[/TD]
[TD][/TD]
[TD="align: right"]95%[/TD]
[TD="align: right"]70%[/TD]
[TD][/TD]
[TD="align: right"]22%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]96%[/TD]
[TD="align: right"]46%[/TD]
[TD][/TD]
[TD="align: right"]32%[/TD]
[TD="align: right"]34%[/TD]
[TD="align: right"]70%[/TD]
[TD="align: right"]32%[/TD]
[TD][/TD]
[TD="align: right"]7%[/TD]
[TD="align: right"]51%[/TD]
[TD][/TD]
[TD="align: right"]52%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]null[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD="align: right"]39%[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]11%[/TD]
[TD="align: right"]58%[/TD]
[TD][/TD]
[TD="align: right"]18%[/TD]
[TD="align: right"]56%[/TD]
[TD="align: right"]43%[/TD]
[TD="align: right"]36%[/TD]
[TD][/TD]
[TD="align: right"]19%[/TD]
[TD="align: right"]66%[/TD]
[TD][/TD]
[TD="align: right"]41%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD="align: right"]58%[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]74%[/TD]
[TD="align: right"]8%[/TD]
[TD][/TD]
[TD="align: right"]64%[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]66%[/TD]
[TD="align: right"]89%[/TD]
[TD][/TD]
[TD="align: right"]20%[/TD]
[TD="align: right"]96%[/TD]
[TD][/TD]
[TD="align: right"]89%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD="align: right"]47%[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]26%[/TD]
[TD="align: right"]21%[/TD]
[TD][/TD]
[TD="align: right"]95%[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]36%[/TD]
[TD="align: right"]0%[/TD]
[TD][/TD]
[TD="align: right"]56%[/TD]
[TD="align: right"]60%[/TD]
[TD][/TD]
[TD="align: right"]29%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD="align: right"]64%[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]73%[/TD]
[TD="align: right"]70%[/TD]
[TD][/TD]
[TD="align: right"]64%[/TD]
[TD="align: right"]11%[/TD]
[TD="align: right"]77%[/TD]
[TD="align: right"]84%[/TD]
[TD][/TD]
[TD="align: right"]87%[/TD]
[TD="align: right"]32%[/TD]
[TD][/TD]
[TD="align: right"]82%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD="align: right"]36%[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]33%[/TD]
[TD="align: right"]63%[/TD]
[TD][/TD]
[TD="align: right"]18%[/TD]
[TD="align: right"]21%[/TD]
[TD="align: right"]49%[/TD]
[TD="align: right"]1%[/TD]
[TD][/TD]
[TD="align: right"]60%[/TD]
[TD="align: right"]65%[/TD]
[TD][/TD]
[TD="align: right"]16%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD]null[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD="align: right"]51%[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]35%[/TD]
[TD="align: right"]73%[/TD]
[TD][/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]52%[/TD]
[TD="align: right"]92%[/TD]
[TD="align: right"]20%[/TD]
[TD][/TD]
[TD="align: right"]84%[/TD]
[TD="align: right"]96%[/TD]
[TD][/TD]
[TD="align: right"]1%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]**[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]******[/TD]
[TD]******[/TD]
[TD]******[/TD]
[TD]******[/TD]
[TD]******[/TD]
[TD]******[/TD]
[TD]******[/TD]
[TD]******[/TD]
[TD]******[/TD]
[TD]******[/TD]
[TD]******[/TD]
[TD]******[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[DonnaJoh]

I got it working with the =5*(

MATCH(O8,{0,30,50,70,85,95}/100)-1 formula. Thank you

​