Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Maximum value in a list, for a given criteria

Status
Not open for further replies.

ceecld

Technical User
Joined
Jul 17, 2003
Messages
68
Location
US
Hi,

I am looking to find the max or min value in a column for a gievn criteria.

Lets say in column A I have a list of transformers, in column B i have the size of this transformer, then in column C i have the current in the transformer. So what im wanting to do is find the max & min value of current(col. C) for each size of transformer (col B).

I thought there would be a reasonable easy way to do this but, maybe its just to early in the morning for me to think of an elegant solution,

Thanks
 
can you give a clue, how does your file looks like?


``The wise man doesn't give the right answers,
he poses the right questions.''
TIMTOWTDI
 
example:

TX ID TX Size Tx Current
R43 450 0.1
RH42 750 0.251
X3 250 0.05
SC18 450 0.15

and so on....


I would therefore be looking to extract the maximum value of current based on the Tx Size so for a Tx size of 450 the max current would be 0.15

Hope this helps.

 
=MAX((B1:B100=450)*(C1:C100))
entered as an ARRAY formula witrh CTRL+SHIFT+ENTER

This will enclose the formula in { } to show it has worked

Same syntax etc with MIN to get the lowest value

If you don't want to use array formulae, have a look in the helpfiles at DMAX and DMIN

Rgds, Geoff

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

Please read FAQ222-2244 before you ask a question
 
Cheers!

How does this work? What does entering it as an array formula actually mean?
 
I can get the Max to work, however when i use the min function it just displays 0. any ideas?
 
Have a look at DMIN then if the MIN doesn't work as an array

=DMIN(Database, "field" , CriteriaRange)

where the database is the range containing your data (including headers)
"field" is the field name you want to get the MIN of - it is case sensitive and needs to be surrounded by " "
CriteriaRange is a range of cells that specify the criteria so if it was Z1:Z2 for instance then in Z1 you would have the header for the "TX Size" and in z2, you would have 450

Rgds, Geoff

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

Please read FAQ222-2244 before you ask a question
 
Thats works thanks!



A bit strange that the array min doesnt work, neither does average. sum and max do though.
 
must be to do with the way it aggregates the functions - glad you've solved your problem anyway !! Probably better off using DMAX as well rather than the array formula

(See Zathras, I do learn)

Rgds, Geoff

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

Please read FAQ222-2244 before you ask a question
 
Is there a neat way to do the criteria for the DMIN DMAX function?

At the moment i have a summary sheet display parameters about the certain transformer. to get the criteria to work for the DMIN DMAX functions i need to have the column title in the cell above the criteria:

Min Max
Tx Size
450 .10 .15
Tx Size
750 .01 .251

etc.

=DMIN($A$2:$C$8,"Tx I",F4:F5)
=DMIN($A$2:$C$8,"Tx I",F6:F7)

this way it takes up two rows per transformer size, is it possible to just have the column title in one cell and refer to a particular critera cell (TX size)?
 
Geoff, glad you are learning. But I think this problem is better served by creating a pivot table. This took less than a minute to set up:
[tt]
Data
TX Size Max of Tx Current Min of Tx Current
250 0.05 0.05
450 0.15 0.1
750 0.251 0.251
Grand Total 0.251 0.05
[/tt]
ceecld, look up Pivot Tables in the help file to see how to do it.
 
Okay that looks good, Cheers

The help is a bit rubbish for pivot tables but ill see if i can manage it.

 
ceecld, the help is a bit rubbish for most everything, but the answers are there if you look around. And Pivot Tables are set up using a Wizard which makes it fairly easy.

If you have Excel 97, the Wizard takes you to the layout dialog automatically. I believe starting with Excel 2000, you have to select the layout dialog by clicking a button along the way.

Tip: when setting up the layout, double-click one of the button thingys after dragging it to the data space and you can make various modifications (such as min/max instead of the default sum). Look around and you will see other options such as formatting, and calculations that are available.

Happy learning!


 
Cheers! for all your help guys

 
Zathras - "blinded by my own light" - it's usually me advocating pivottables over formulae [rofl]

Rgds, Geoff

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

Please read FAQ222-2244 before you ask a question
 

As the good book says, iron sharpens iron.

That's what makes this site so great!

[wavey3]

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top