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?