# Return Maximum value

1. ## 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

2. ## RE: Return Maximum value

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

3. ## RE: Return Maximum value

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

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

4. ## Re: Return Maximum value

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=?=" <ronSKIPTHIScoderre@bigfoot.com> wrote in message news:<3CA0FBCB-0876-4F89-A902-D05F9F5970C3@microsoft.com>...
> 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

5. ## Re: Return Maximum value

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!

soapydux@gmail.com (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

6. ## Re: Return Maximum value

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1