Dynamic formulas based on other cell formula results

Question

I have 10 years of monthly data for 300 plants. While Excel can do moving averages for various time periods I am looking to do a moving median value for this data set.

I can obviously figure out the formula through brute force but what I was looking for was the ability to use a formula that can be modified based on the results of another cell.

If the formula I have for a 4 quarter moving median value for the 10 years of data is =median(c3:c951) then the next period would be =median(c952:c1900) etc.

Rather than rewrite each formula in the cells it would seem I should be able to reference a string of numbers or results to replace the actual number in the formula.

Seems to me many years ago i saw that done but of course can’t recall how to do that.

Answer 1

As far as I can tell you're using every (n = 948)th row? Should be something like this -

=MEDIAN(INDIRECT("C"&(ROW()-1)*948+ROW(C2)+1&":C"&ROW()*948+ROW(C3)))

So, if you put it in E1, it would come out to

=MEDIAN($C$3:$C$951)

dragged to E2, it would return

=MEDIAN($c$952:$c$1900)

etc

---

To avoid INDIRECT you could use something like -

=MEDIAN(OFFSET(C$3,ROWS(E$1:E1)*948-948,0,948))

---

To use calendar years and account for leap years - I don't know.

Answer 2

Use the OFFSET function. You can do your offsetting math there. Maybe in combination with the ROW function.

If data is in column A and your median labels (eg: 2010, 2011, etc) in column F, try something like:

=MEDIAN( OFFSET( $A$1; 365* ROW( F1 ); 0; 365; 1) )

And copy-paste down.

This calculates the median for each block of 365 data items. Adjust cell references and magic numbers to your needs.