software

I have a spreadsheet of a few hundred stocks with each stock in a row, from 5
through 600. Each column is a variable (price, EPS, Market Cap, etc) . I
would like to group these stocks by a variety of these variables and then
perform some calculations on the stocks in each group. I have macros to
group and hide, but I would like a furmula that changes cell references
depending on how many are in each group. The problem is that the number of
stocks that fit a certain screen will change all the time so formulas that
reference to specific rows will miss certain stocks. How do I create a
formula ie =median(c5:C600) that only counts the filtered stocks - could be
=median(C5:C140) or =median(C5:C88) depending on how many stocks fit the
screen? I have a cell that tells me the number of rows that fit the filter
so I will always know how many rows to count and what the starting row is
(those numbers are in cells) but how do I get the number that is in a cell to
enter into a cell reference? ie my filter result shows 45 stocks so I would
want to take =median(C5:C49) where the 49 is determined from an active cell?

Ned,

You could use the offset function.
=MEDIAN(OFFSET(c5,0,0,numRows,1))

Replace numRows with the formula you are using to calculate the number of
rows in your grouping.
--
HelpExcel.com
1-888-INGENIO
1-888-464-3646
x0197758quot;nedquot; wrote:

gt; I have a spreadsheet of a few hundred stocks with each stock in a row, from 5
gt; through 600. Each column is a variable (price, EPS, Market Cap, etc) . I
gt; would like to group these stocks by a variety of these variables and then
gt; perform some calculations on the stocks in each group. I have macros to
gt; group and hide, but I would like a furmula that changes cell references
gt; depending on how many are in each group. The problem is that the number of
gt; stocks that fit a certain screen will change all the time so formulas that
gt; reference to specific rows will miss certain stocks. How do I create a
gt; formula ie =median(c5:C600) that only counts the filtered stocks - could be
gt; =median(C5:C140) or =median(C5:C88) depending on how many stocks fit the
gt; screen? I have a cell that tells me the number of rows that fit the filter
gt; so I will always know how many rows to count and what the starting row is
gt; (those numbers are in cells) but how do I get the number that is in a cell to
gt; enter into a cell reference? ie my filter result shows 45 stocks so I would
gt; want to take =median(C5:C49) where the 49 is determined from an active cell?

Excelent! Thanks for the help.

quot;galimiquot; wrote:

gt; Ned,
gt;
gt; You could use the offset function.
gt; =MEDIAN(OFFSET(c5,0,0,numRows,1))
gt;
gt; Replace numRows with the formula you are using to calculate the number of
gt; rows in your grouping.
gt; --
gt; HelpExcel.com
gt; 1-888-INGENIO
gt; 1-888-464-3646
gt; x0197758
gt;
gt;
gt; quot;nedquot; wrote:
gt;
gt; gt; I have a spreadsheet of a few hundred stocks with each stock in a row, from 5
gt; gt; through 600. Each column is a variable (price, EPS, Market Cap, etc) . I
gt; gt; would like to group these stocks by a variety of these variables and then
gt; gt; perform some calculations on the stocks in each group. I have macros to
gt; gt; group and hide, but I would like a furmula that changes cell references
gt; gt; depending on how many are in each group. The problem is that the number of
gt; gt; stocks that fit a certain screen will change all the time so formulas that
gt; gt; reference to specific rows will miss certain stocks. How do I create a
gt; gt; formula ie =median(c5:C600) that only counts the filtered stocks - could be
gt; gt; =median(C5:C140) or =median(C5:C88) depending on how many stocks fit the
gt; gt; screen? I have a cell that tells me the number of rows that fit the filter
gt; gt; so I will always know how many rows to count and what the starting row is
gt; gt; (those numbers are in cells) but how do I get the number that is in a cell to
gt; gt; enter into a cell reference? ie my filter result shows 45 stocks so I would
gt; gt; want to take =median(C5:C49) where the 49 is determined from an active cell?