Excel Macro Help Needed -- Rolling Average Issue 1

[kbarr0411]

I'd like to create a macro that calculates a 20 day moving average. Within the attached spreadsheet, I'm referring to column C which calculates a 20 day average of column B's orders per day. It starts over each 20 days/rows. The manual calculations in the attachment is fine for this output, but we plan to run this numerous times throughout the year with YTD data and will need a macro to run the calculations.

I understand how to write the macro to average of the first 20 days, but can't figure out how to calculate the next set (per day).

For example:
Up to the 20th day, the formula calculates all orders and divides by 20, like =SUM(B2:B21)/20. When the "21st day" comes, we only want to calculate the previous 20 days so the range needs to skip to =SUM(B3:B22)/20 and so on.

Any help is appreciated!

 

[AnotherHiggins]

Macro question best fit in forum707. If you need help with a macro, please repost your question there.

[highlight]BUT[/highlight] you don't need a macro for this.

I'll write up some thoughts and post back in a few minutes.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

Sorry about the delay - I got sidetracked. These silly people at work expect me to, you know, work. The nerve of some people.

I'd probably just use a Dynamic Named Range.

Try this (If you're in Excel 2003 or before, your menu paths will differ):
[ul]
[li]Select Range C2[/li][ul]
[li]It is [!]critical[/!] that C2 be active before proceeding[/li][/ul]
[li]Go to Formulas > Name Manager > New[/li]
[li]In Name, type a name that you'll remember - for this example I'll use rngRolling20[/li]
[li]In Refers To, type (or paste) [COLOR=blue white]=Indirect(Address(Max(Row(Data!C2) - 19, 2), Column(Data!C2) - 1) & ":" & Address(Row(Data!C2), Column(Data!C2) - 1))[/color][/li]
[li]Press OK[/li]
[li]Press Close[/li]
[li]Now in cell C2 type the formula [COLOR=blue white]=AVERAGE(rngRolling20)[/color][/li]
[/ul]

That Dynamic Named Range will always refer to 1 column to the left of the referring cell, and the always the last 20 rows, capped at the current row (for rows < 20).

In other words, you can use that exact formula (=AVERAGE(rngRolling20)) to produce a rolling-20-day average for Orders, Units and Dollars.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

Here's an edited copy of your file that I used for testing to be sure this produced identical results to what you already had.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[kbarr0411]

Thanks for the quick response! I reviewed the file and it appears to be doing exactly what we're needing. I'll pass it by the boss man and see if it tickles his fancy! Thanks again!!