moving the formula "average" over one column in a macro
 

How do I generate a moving average in a cell - say cell "b4" , i.e.,
"=average(a1:b1:c1)".....and then put it in a macro so when a number is
copied into d1, the formula moves to the adjacent cell c4 and displays
"=average(b1:c1:d1)"?


a b c d
data data data data

formula 1 formula 2 etc....


--
drumstu
------------------------------------------------------------------------
drumstu's Profile: http://www.excelforum.com/member.php...o&userid=26467
View this thread: http://www.excelforum.com/showthread...hreadid=398297

I'm not sure exactly what you're asking here, because it looks like you
can get to what you want through a simple worksheet function.

For example, if we take the example you've given, where you have A1,
B1, C1, D1 <data, AND a formula1, formula2, formula3, etc. in cells
B2, C2, D2, respectively, then if your B2 formula is written:
=average( $A1:B1) (exactly that way) and then copied over to columns C
and D, then its appearance there will be:
=average( $A1:C1) (in cell C2) and
=average( $A1:D1) (in cell D2).

If you copy these formulas down under another row of "Data" entries
(let's say, for example, your next Data appears on Row 6 (A6, B6, C6,
D6) and you copy the formulas from B2, C2, D2 to your next Formula row
(Row 7), the formulas will reference the same relative columns and
rows. That is, the formulas will be the same except the row numbers
will change to "6" from "1".

Of course, this can be done with a macro, too, but simpler is usually
better unless you have a compelling need for the macro...