Min Max Question

[carl]

My data looks like this (there is actually 5k rows of data)

Stock Price
AA 1
AA 2
AA 3
AA 4
BB 1
BB 2
BB 3


I am looking for a formula that will produce this table:

Stock Low High
AA 1 4
BB 1 3

Thank you in advance.

 

[joeu2004]

I am looking for a formula that will produce this table:

Stock Low High
AA 1 4
BB 1 3

Assuming you enter the names of the stocks starting in A2 of a summary
worksheet and your data is in a worksheet called Data starting in A2, enter
the following array formulas [*] starting in B2 and C2 of the summary
worksheet and copy down:

B2: =MIN(IF(Data!$A$2:$A$5000=A2,Data!$B$2:$B$5000))
C2: =MAX(IF(Data!$A$2:$A$5000=A2,Data!$B$2:$B$5000))

Note: In XL2007 and later, you might be able to use the unbounded ranges
Data!$A:$A and Data!$B:$B. I'm not sure.

[*] Enter an array formula by pressing ctrl+shift+Enter instead of Enter.
Excel will display an array formula surrounded by curly braces in the
Formula Bar, i.e. {=formula}. You cannot type the curly braces yourself.
If you make a mistake, select the cell, press F2 and edit, then press
ctrl+shift+Enter.


----- original message -----

 

[Don Guillett]

I am looking for a formula that will produce this table:

Stock Low High
AA 1 4
BB 1 3

Assuming you enter the names of the stocks starting in A2 of a summary
worksheet and your data is in a worksheet called Data starting in A2, enter
the following array formulas [*] starting in B2 and C2 of the summary
worksheet and copy down:

B2:  =MIN(IF(Data!$A$2:$A$5000=A2,Data!$B$2:$B$5000))
C2:  =MAX(IF(Data!$A$2:$A$5000=A2,Data!$B$2:$B$5000))

Note:  In XL2007 and later, you might be able to use the unbounded ranges
Data!$A:$A and Data!$B:$B.  I'm not sure.

[*] Enter an array formula by pressing ctrl+shift+Enter instead of Enter.
Excel will display an array formula surrounded by curly braces in the
Formula Bar, i.e. {=formula}.  You cannot type the curly braces yourself.
If you make a mistake, select the cell, press F2 and edit, then press
ctrl+shift+Enter.

----- original message -----

My data looks like this (there is actually 5k rows of data)

Stock Price
AA 1
AA 2
AA 3
AA 4
BB 1
BB 2
BB 3

I am looking for a formula that will produce this table:

Stock Low High
AA 1 4
BB 1 3

Thank you in advance.

Modify this to suit. Not necessary to CSE
=SUMPRODUCT(MAX(($A$2:$A$8=A2)*($B$2:$B$8)))

 

[carl]

Assuming you enter the names of the stocks starting in A2 of a summary
worksheet and your data is in a worksheet called Data starting in A2, enter
the following array formulas [*] starting in B2 and C2 of the summary
worksheet and copy down:

B2:  =MIN(IF(Data!$A$2:$A$5000=A2,Data!$B$2:$B$5000))
C2:  =MAX(IF(Data!$A$2:$A$5000=A2,Data!$B$2:$B$5000))

Note:  In XL2007 and later, you might be able to use the unbounded ranges
Data!$A:$A and Data!$B:$B.  I'm not sure.

[*] Enter an array formula by pressing ctrl+shift+Enter instead of Enter.
Excel will display an array formula surrounded by curly braces in the
Formula Bar, i.e. {=formula}.  You cannot type the curly braces yourself.
If you make a mistake, select the cell, press F2 and edit, then press
ctrl+shift+Enter.

----- original message -----

Modify this to suit. Not necessary to CSE
=SUMPRODUCT(MAX(($A$2:$A$8=A2)*($B$2:$B$8)))- Hide quoted text -

- Show quoted text -

Thank you all.

 

[Bernd P]

Hello,

I suggest to use my UDF Pstat:
http://sulprobil.com/html/pstat.html

Regards,
Bernd