Calculating latest running 3 month total

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

5oclock - 17 Jul 2007 17:43 GMT

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

Reply to this Message

Bob Phillips - 17 Jul 2007 17:58 GMT

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.

Signature

HTH

Bob

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

> Hi everyone,
>
[quoted text clipped - 12 lines]
>
> TL

Reply to this Message