Help with IF ( AND and MAX

[rubybegonia]

Here is my formula currently that is returning a statement of FALSE:

=IF(AND(H5>=25,H5<=29),"Oranges",IF(AND(H5>=30,H5<=34),"Apples",IF(AND(H5>=35),"Bananas",IF(H5=MAX(H:H),"WINNER!!"))))

Here is what I'm wanting it to do:

Points	Award
35+	Bananas
30-34	Apples
25-29	Oranges
0-24 Max Points	(blank) WINNER!!

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

I've gone over and over this equation for a day, and cannot get it to function properly. Too many arguments, too few arguments. I cannot seem to get it right. It seems to actually only calculate the person with the max points as the winner. The rest of the cells are all FALSE. Any help would be greatly appreciated!

 

[63falcondude]

What is the value in H5?

 

[Eric W]

I'd put a small lookup table somewhere, like this:

	A	B	C	D	E	F	G	H	I
1	Score	Prize
2	0
3	25	Oranges
4	30	Apples
5	35	Bananas					Amy	0
6							Bob	1
7							Cindy	24
8							Doug	25	Oranges
9							Elaine	26	Oranges
10							Frank	30	Apples
11							Gail	31	Apples
12							Hank	35	Bananas
13							Iris	38	Bananas
14							Jack	55	Winner!!
15

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet14​

Worksheet Formulas Cell Formula I5 =IF(H5=MAX($H$5:$H$14),"Winner!!",VLOOKUP(H5,$A$2:$B$5,2))&"" <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

You could incorporate the points/awards into the formula, but it would be bulkier.

Hope this helps.

 

[rubybegonia]

Value in H5 = Sum of points from Pivot Table

 

[63falcondude]

Excel 2010

	A	B
1	Points	Award
2	24
3	36	Bananas
4	38	Winner!!
5	30	Apples

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B2 =IF(A2=MAX(A:A),"Winner!!",LOOKUP(A2,{0,25,30,35},{"","Oranges","Apples","Bananas"})) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[JoeMo]

Maybe:

=IF(H5=MAX(H:H),"WINNER!!",IF(AND(H5>=25,H5<=29),"Oranges",IF(AND(H5>=30,H5<=34),"Apples",IF(AND(H5>=35),"Bananas",""))))

 

[rubybegonia]

I do have a range i can use called 'incentives' and it looks like this:

Points	Incentive Award
35	Bananas
30	Apples
25	Oranges
0

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

 

[rubybegonia]

Works Great JoeMo! Thank you!

 

[JoeMo]

Works Great JoeMo! Thank you!

You are welcome - thanks for the reply.