Calculating latest running 3 month total

  • Thread starter Thread starter 5oclock
  • Start date Start date
5

5oclock

Hi everyone,

It's my first time posting so I hope I am asking a not too basic
question...I tried searching for previous posting relating to this but
had no luck. My question is, I have a worksheet with months
starting in July 07 - June 08. Each month, as data become available,
a new month's of data will be added to the worksheet. If possible, I'd
like another column in my worksheet to always calculate the lastest
three months. For the first couple of months, July 07 and August 07,
as there will be less than three month's worth of data, it would only
calculate what's available or display blank.

I hope that makes sense. Any help would be very much appreciated!!
Thanks!!

TL
 
This assumes the data starts in C2

SUM(N(OFFSET(C2,0,LARGE(IF(C2:N2<>"",COLUMN(C2:N2)-MIN(COLUMN(C2:N2))),ROW(INDIRECT("1:"&MIN(3,COUNT(C2:N2))))))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top