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?

 

[shuvodey]

Hi Donna,

I changed your points range to the following (G5:I11):

[TABLE="width: 231"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Lower bound[/TD]
[TD]Upper bound[/TD]
[TD]Points[/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD="align: right"]85[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]70[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

Assuming your scores are in B and you're populating the points in C against each score, you could try the following:

=SUMIFS($I$6:$I$11,$G$6:$G$11,"<="&B6,$H$6:$H$11,">="&B6)

OR

=SUMPRODUCT(($G$6:$G$11<=B6)*($H$6:$H$11>=B6)*$I$6:$I$11)

OR

=INDEX($I$6:$I$11,MATCH(B6,$H$6:$H$11,-1))

Hope this helps.