Looking up for maximum value

[ceecld]

Hi,

As the title suggests im wanting to lookup up a table and select the maximum value for the given criteria. My table for example could look like this (see below) and i would be wanting to extract the maximum values for SB1B1A, SB1E3 etc.

SB1B1A 30.662
SB1B1A 30.714
SB1B1A 30.6
SB1E3 11.839
SB1E3 12.953
SB1E4A 19.477
SB1E4B 20.088

Thanks

 

[lupins46]

Add the two fields to a new query and convert the query to a totals query. Group By the first field and change 'Group By' to Max for the second.

 

[lupins46]

Sorry - I don't know if my answer was relevant - I 've just noticed that you didn't say what programme you are using.

 

[ceecld]

Sorry ment to say this an excel worksheet that im dealing with

 

[xlbo]

Pivot Table over the top of the data with Subtotals set to MAX rather than "Automatic" or "Sum" will do the equivalent

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[BlueHorizon]

Hi,
If you don't need a "printed" max value, you can use the AutoCalculate feature which appears towards the right side of the status bar. Select your range of cells, right click on the "Sum" that appears. Select Max from the list.
HTH,


Best,
Blue Horizon

http://www.bluehorizontraining.com

 

[xlbo]

Blue - I think they want it per item

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

Hi ceecld,

If you want a formula ...

If your data as posted are in columns A and B, rows 1 through 200, say, then in cell C1 you can put ..

[blue][tt] =MAX(IF($A$1:$A$200=A1,$B$1:$B$200))[/tt][/blue]
.. ARRAY ENTERED (that is with Ctrl+Shift+Enter).

Copy this down to row 200 (in this example) and you will have the maximum in column C on each row corresponding to the value in column A of that row.

You can put the formula anywhere you like and change the reference (A1 in the above example) to get your totals in other cells, and based on values in other cells.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[ceecld]

Thanks!