calculating rolling average for the last four months

[humvie]

Can anyone direct me with this one.

I have an excel file with a list of averages for every month.

Whse Location Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec etc ... (sorry cant seem to get the number to line up with the months)
warehouse 1 10 10 15 12 15 11 14
warehouse 2 9 7 5 6 4 8 9

What I can't figure out is the "rolling" average for the last four months. So if Aug is empty, then figure the average for Jul Jun May and Apr. When Aug is completed then figure the average for Aug Jul Jun and May etc ....

Obtaining the average across the board is relatively easy ......

Any help would be appreciated

 

[SkipVought]

Hi,

You need to check out the OFFSET function. The OFFSET function has 5 arguments.

The FIRST is an anchor reference.

The next 2 are row/col offset values from the anchor reference

The last 2 are the SIZE of the range in Rows/Columns.

So you VARY one or both of the row/col offset values to move your range that you will average on.

You said, "when Aug is empty" Does that mean the Aug in ROW 1 or do you mean the DATA corresponding to Aug?

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[humvie]

Thanks for the tip Skip (sorry couldn't hesitate).
I'll look into that right away. As for the question about "when Aug is empty", yes I did mean the DATA corresponding to Aug.

Thanks again
Ray

 

[SkipVought]

Here's the formula for the Insert/Name/Define
[tt]
=Sum(OFFSET(Sheet2!$A2
,0
,IF(COUNT(Sheet2!B2:M2)<4,1,COUNT(Sheet2!B2:M2)-3)
,1
,IF(COUNT(Sheet2!B2:M2)<4,COUNT(Sheet2!B2:M2),4)
))
[/tt]
where col A are your warehouses
cols B:M are Jan - Dec

I named mine YTD

then in Col N
[tt]
=YTD
[/tt]
sums for that row for the prevoius 4 months (excluding thos months that have less than 3 months of data)

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[humvie]

Nice ! This kind of quick response is no doubt a classic "been there done that" kind of thing.

Thnks very much for the help Skip

Ray