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:

[TABLE="width: 345"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Points[/TD]
[TD]Award[/TD]
[/TR]
[TR]
[TD]35+[/TD]
[TD]Bananas[/TD]
[/TR]
[TR]
[TD]30-34[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]25-29[/TD]
[TD]Oranges
[/TD]
[/TR]
[TR]
[TD]0-24

Max Points[/TD]
[TD](blank)

WINNER!![/TD]
[/TR]
</tbody>[/TABLE]


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
Score	Prize
Oranges
Apples
Bananas	Amy
Bob
Cindy
Doug	Oranges
Elaine	Oranges
Frank	Apples
Gail	Apples
Hank	Bananas
Iris	Bananas
Jack	Winner!!

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]25[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]30[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]35[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]24[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]25[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]26[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]30[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]31[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]35[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]38[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]55[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet14​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I5[/TH]
[TD="align: left"]=IF(H5=MAX($H$5:$H$14),"Winner!!",VLOOKUP(H5,$A$2:$B$5,2))&""[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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
Points	Award
Bananas
Winner!!
Apples

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]24[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]36[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]38[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]30[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=IF(A2=MAX(A:A),"Winner!!",LOOKUP(A2,{0,25,30,35},{"","Oranges","Apples","Bananas"}))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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:

[TABLE="width: 345"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Points[/TD]
[TD]Incentive Award[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]Bananas[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]Oranges[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[rubybegonia]

Works Great JoeMo! Thank you!

 

[JoeMo]

Works Great JoeMo! Thank you!

You are welcome - thanks for the reply.