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.
--
http://HelpExcel.com
1-888-INGENIO
1-888-464-3646
x0197758


"ned" wrote:

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?

Excelent! Thanks for the help.

"galimi" wrote:

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.
--
http://HelpExcel.com
1-888-INGENIO
1-888-464-3646
x0197758


"ned" wrote:

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?