Finding the MAX Value in a list

[carlos]

I have a table like so:

A B C
Product Units
BOX1 22
BOX2 4
BOX1 100
BOX2 500

I would like to have a formula in C3:C6 that will look at
A1:A3 and return True for the MAX Value

I would like to create a 2nd table that shows me the MAX
Units per Product

Product Max Units
BOX1 100
BOX2 500

Thank you in advance for your help.

 

[Frank Kabel]

Hi
to create such a report automatically: use a pivot table. For a formula
solution:
on your second sheet in B1 enter the array formula (entered with
CTRL+sHIFT+ENTER):
=MAX(IF('sheet1'!$A$1:$A$100=A1,'sheet1'!$B$1:$B$100))
and copy down

 

[Peo Sjoblom]

The max value for BOX1 is

=MAX((A1:A3="BOX1")*(B1:B3))

entered with ctrl +shift & enter

replace with BOX2 for the other box

No need really for the C1:C3 solution since the array formula will give you
the
answer in one fell swoop



--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[JE McGimpsey]

One way:

For the second part, a Pivot Table is the perfect solution:

http://peltiertech.com/Excel/Pivots/pivotstart.htm