Row with highest value

[ngocpdn]

Hi!

Need some help with a formula to calculate a discount.

20% off all items EXCEPT the highest value item. Where 2 items are same value, only 1 gets the discount. Here's my example:

40	8
100	0
100	20
60	12

[td]price[/td]
[td]discount[/td]

This seems to work when there's no duplicates: =IF(A2=MAX(A2:A4),0,A2*0.2)

But I can't get this to work when there's 2 items with the same highest value.

Any tips on how I can get this working?

 

[i_nth]

Try: =IF(A2=MAX(A$2:A$4),0,A2*0.2)

 

[i_nth]

Try: =IF(A2=MAX(A$2:A$4),0,A2*0.2)

Actually, given that your table has four data rows, it should be =IF(A2=MAX(A$2:A$5),0,A2*0.2)

 

[Fluff]

My take on this
=IF(AND(A2=MAX(A$2:A$5),COUNTIF(A$2:A2,MAX(A$2:A$5))=1),0,A2*0.2)

 

[i_nth]

Quite right, I missed the "only 1 gets the discount" part.

 

[ngocpdn]

My take on this
=IF(AND(A2=MAX(A$2:A$5),COUNTIF(A$2:A2,MAX(A$2:A$5))=1),0,A2*0.2)

Worked like a charm! THANK YOU both for your time!!!!

 

[i_nth]

That doesn't quite work if there are more than two duplicates or the highest value occurs in the first row. How about:
=IF(AND(A2=MAX(A$2:A$5),COUNTIF(A2:A$2,MAX(A$2:A$5))>1),0,A2*0.2)

 

[Fluff]

Worked like a charm! THANK YOU both for your time!!!!

You're welcome & thanks for the feedback.
That said, i_nth has made a good point