Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
MAX
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) |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
MAX
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)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Hugo C." wrote in message ... 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) |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
MAX
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 Replace @mailinator.com with @tiscali.co.uk "Hugo C." wrote in message ... 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) |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
MAX
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Hugo C." wrote: 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|