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.

Score Range		=		# of Pts
95to100% 85to94% 70to84% 50to69% 30to49% <=29%				25 20 15 10 5 0

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

How can I fix this?

 

[shuvodey]

Hi Donna,

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

Lower bound	Upper bound	Points
95	100	25
85	94	20
70	84	15
50	69	10
30	49	5
1	29	0

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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.