MAX

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i´m trying to obtain the max value, but i need to have a reference
ex:

client value
123 1560
123 2000
123 8520
156 1564
154 4666

I need to group client 123 in order to obtain is max value - 8520

Can someone please help me (it´s a list of 40000 clients, and i don´t want
to do manualy)
 
If you are saying that you want the max value in col G based on the client
in col F then use this formula which must be entered using ctrl+shift+enter
=MAX(IF(F2:F22=123,G2:G22))
 
Try:

=MAX((A2:A4000=123)*(B2:B4000))

Array entered by holding Crtl & Shift pressed while you press Enter or:

=SUMPRODUCT(MAX((A2:A4000=123)*(B2:B4000)))

Entered with just a simple enter

But lookout for Harlan! (in joke with Harlan)


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
One quick alternative is to use a pivot table (PT). In a matter of seconds,
the PT can provide both the required uniques listing of the Clients and the
corresponding max values

Select any cell within the source table. Click Data > PivotTable .. Click
Next > Next. In step 3 of the wizard, click Layout. Drag n drop Client within
the ROW area. Drag n drop Value within the DATA area. It'll appear as "Sum of
value". Double click on it, choose "Max" under Summarize by, click OK. Click
Finish. That's it! Hop over to the PT sheet for the results.
 
Back
Top