Need help with moving average function that picks up cells out of range

[sandroid]

Hi all,

I have a budget spreadsheet where I need to provide the moving 3 month average relative to the last entered month, whenever the spreadsheet is opened. The sequence of values which need to be averaged go from left to right on a row, however, the range does *not* start on the first column in my spreadsheet, causing me some complication. Right now, until the fourth month is entered, the rolling average is calculating a wrong value because it's picking up values to the left of the first intended value in the range. The formula I've come up with so far is this:

Please Login or Register  to view this content.

Where D4 is the first column with values intended for this range, and O4 is the last one. There are values in B4 and C4, but they're for something else, and therein lies my problem.

Until I have values in at least D4-D6, the moving average calculation is wrong, because it's incorporating values from B4 and C4. This is likely due to the -3 added to the COUNTA function, but I don't know another way to make this work for the rest of the rows.

What I'd like is some way for the function not to evaluate. Evaluating to 0 will not help as it will still impact what I plan to do with a series of these moving averages. Does anyone have a suggestion?

Thanks

[Max, Singapore]

> ... There are values in B4 and C4 but they're for something else ...
Just some thoughts. Simpler to physically move B4:C4 out of the way (of your formula calcs) to another part of the sheet?
If it is required for visual/printing purposes, you could try having a linked picture of the 2 cells (which are placed somewhere else) positioned to fit within the original area