Return Maximum value

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

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

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.

>-----Original Message-----
>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
>.
>

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



"=?Utf-8?B?Um9uIENvZGVycmU=?=" <[email protected]> wrote in message news:<[email protected]>...
> 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

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!

In article <[email protected]>,
[email protected] (Soapydux) wrote:

> 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

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