Return Maximum value

[Soapydux]

Hi

Looking to find a formula that will calculate a maximum bid figure
from an array.

Example data.
Products Bidder Amount
Product 1 John £50
Product 2 David £50
Product 1 William £55
Product 1 Jill £45
Product 3 Tom £60
Product 3 Gwen £30

So when I put s table together of all Product I get the highest
bidders for each e.g.

Products Bidder Amount
Product 1 William £55
Product 2 David £50
Product 3 Tom £60

Any ideas?

Thanks

 

[Guest]

1)List your products from E1:E3

2)Put this formula in Cell D1:
=MAX(IF($A$2:$A$7=E1,$C$2:$C$7,0))
(Note: Commit that array formula by pressing [Ctrl]+[Shifr]+[Enter])

3)Copy the formula into Cells D2 and D3

I hope that helps.

Regards,
Ron

 

[Guest]

Ron:
Thanks for the formula for the Amount
What formula will bring back the corresponding
Bidder (Name) for the Max Amt?
Tks in advance...
Have a nice day.

 

[Soapydux]

Hi Thanks for that.

That works to produce the max value per product i.e. Product 1 - £55
but I also need it to identify the highest bidder. Product 1 £55
William

Sorry if that wasn't clear enough before.

Cheers

 

[Domenic]

With your list of products in Column E, starting at E2...

Bidder...

F2, copied down:

=INDEX($B$2:$B$7,MATCH(1,($A$2:$A$7=E2)*($C$2:$C$7=G2),0))

....confirmed with CONTROL+SHIFT+ENTER.

Amount...

G2, copied down:

=MAX(IF($A$2:$A$7=E2,$C$2:$C$7))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Guest]

To return the name of the high bidder (using my previous example) put this
formula in cell F1:
=INDEX($B$1:$B$7,MATCH(E1&D1,($A$1:$A$7)&($C$1:$C$7),0))

(Note: Commit that array formula by pressing [Ctrl]+[Shifr]+[Enter])

Then copy it down to Cells F2 and F3

I hope that helps.

Regards,
Ron