Maximum value in a list, for a given criteria

[ceecld]

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

 

[tchatzi]

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

 

[ceecld]

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.

 

[xlbo]

=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

 

[ceecld]

Cheers!

How does this work? What does entering it as an array formula actually mean?

 

[ceecld]

I can get the Max to work, however when i use the min function it just displays 0. any ideas?

 

[xlbo]

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

 

[ceecld]

Thats works thanks!



A bit strange that the array min doesnt work, neither does average. sum and max do though.

 

[xlbo]

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

 

[ceecld]

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)?

 

[Zathras]

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.

 

[ceecld]

Okay that looks good, Cheers

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

 

[Zathras]

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!

 

[ceecld]

Cheers! for all your help guys

 

[xlbo]

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

Rgds, Geoff

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

Please read FAQ222-2244 before you ask a question

 

[Zathras]

As the good book says, iron sharpens iron.

That's what makes this site so great!