Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Min Max Question
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Min Max Question
"carl" wrote:
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 ----- "carl" wrote in 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Min Max Question
On Nov 2, 11:15*am, "joeu2004" wrote:
"carl" wrote: 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 ----- "carl" wrote in 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))) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Min Max Question
On Nov 2, 2:29*pm, Don Guillett wrote:
On Nov 2, 11:15*am, "joeu2004" wrote: "carl" wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Min Max Question
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Macro/VB Question DDE Question | Excel Worksheet Functions | |||
where can I see my question and answer? Yesterday I ask a question | Excel Discussion (Misc queries) | |||
SUM Question | Excel Discussion (Misc queries) | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
The question is an excel question that I need to figure out howto do in excel. | Excel Worksheet Functions |