Hi again

When I verified this formula, I had a problem.

The earliest data is in column B and the latest is in V. The next column to
be filled is in W. This formula doesn't work well if Column W is left blank.

I've attached a sample from my file.

Months Category 1 Category 2 Category 3
Apr 06 438 2429 187
May 06 468 2509 188
Jun 06 386 2356 115
Jul 06 439.99 2494.934
Aug 06 418.738 2623.68
Sep 06 476.762 2818.631
Oct 06 384.501 2800.796
Nov 06 500.444 2805.878 1286.587
Dec 06 411.816 2857.546 1048.737
Jan 07 595.142 3119.351 1357.796
Feb 07 440.891 3119.204 1611.12
Mar 07 548.954 3349.091 1877.482
Apr 07 501.714 3103.273 1369.555
May 07 526.133 2728.437
Jun 07 536.357 2538.707
Jul 07 485.514
Aug 07 457.713
Sep 07 404.142
Oct 07 459.103
Nov 07 418.293
Dec 07 350.3


"12 month
rolling" 477.0213333 2863.294 1106.772375
Check 477.0213333 2993.0105 1553.98825

J

"Max" wrote:

Can I use this** formula if I only want to create a rolling average based
on
the 12 most recent months (even if it includes blanks)?

**That's exactly what the formula I gave does when you enter the col labels
in row2 progressively each month from left to right within the range B2:U2.

**Put in AB3, array-enter the formula, copied down:
=AVERAGE(OFFSET(B3,,MAX(($B$2:$U$2<"")*(COLUMN($B $2:$U$2)))-2,,-12))

The "MAX(...)-2" part of it in the OFFSET anchors/fixes the rightmost
startpoint, ie the most "recent" col (col param) while the -12 (width param)
then grabs the 12 cols range to the left of this anchor col. The minus
in -12 means to the left.

If you carefully select just the OFFSET(...) part of it within the formula
bar and then press F9 to evaluate, the 12 "recent" cols range will be
revealed. Use this as a visual check.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---