15 day moving average doesn't update.

[Brian Ellis]

Hi
I am using a 15-day moving average function in one workbook. It updates the average figure when I enter todays reading. Now I have opened a new workbook and entered the same function but it does not update when I enter the daily reading. Something simple I have missed.
The function is
(Fx) =IF(ROW()<15,NA(),IF(B2>AVERAGE(OFFSET(B2,-13,0,14,1)),NA()))

B column contains the daily update of the readings.
D column contains the moving average.
How do I get the moving average to update when the daily reading is entered, please?

 

[Myrna Larson]

I see a possible problem with your formula, or what you have posted here,
anyway. You have OFFSET(B2,-13,0,14,1). A valid offset from B2 can't be less
than -1, can it? Is the error with the reference to B2 or with the -13?

Hi
I am using a 15-day moving average function in one workbook. It updates the

average figure when I enter todays reading. Now I have opened a new workbook
and entered the same function but it does not update when I enter the daily
reading. Something simple I have missed.

The function is
(Fx) =IF(ROW()<15,NA(),IF(B2>AVERAGE(OFFSET(B2,-13,0,14,1)),NA()))

B column contains the daily update of the readings.
D column contains the moving average.
How do I get the moving average to update when the daily reading is entered,

please?

 

[Brian Ellis]

In the workbook that operates correctly, the function shown appears when I
select cell D2. The answer in the cell is #N/A as I would expect for the
first 15 days.
Cells D15 onwards have numerical answers as expected and the B2 in the
Function changes to B15 Everything else in the functio stays the same.(
Column D is the Moving Average answer column.). My next data entry in B16>
Enter results in the function changing to B16 and the answer appears in D16
There must be a tick box or something which instructs the program to
recalculate and enter the new answer each time new data is entered.?
Unfortunately the original setup was done by someone else. I have examined
the commas and brackets closely but they are accurate. The OFFSET is copied
correctly although I do not understand what it means.

I see a possible problem with your formula, or what you have posted here,
anyway. You have OFFSET(B2,-13,0,14,1). A valid offset from B2 can't be less
than -1, can it? Is the error with the reference to B2 or with the -13?

 

[Myrna Larson]

The OFFSET formula means to start at B2, move UP 13 rows (-13), don't change
the column (0), and starting at that new cell, select a range that is 14
[SIC!]rows high and 1 column wide. There aren't 13 rows above row 2, so when I
put just the OFFSET part of formula in D2 and copy it down, I get a !REF#
error until I get to cell D14. All of this is explained in Help, of course.

The full formula says to return a !NA# error above row 15, so the invalid
reference isn't interfering with the formula -- you are just substituting a
different error result, NA, instead of REF. Personally, I would not have the
formula in cells D214 -- just leave those cells blank, then write the
formula more simply by omitting the outer IF formula.

Another problem is you say you want a 15-day moving average, but the formula
is taking only 14 days, not 15.

When I put what you posted in D2, then copy it down, in D15, I see the
following:

=IF(ROW()<15,NA(),IF(B15>AVERAGE(OFFSET(B15,-13,0,14,1)),NA()))

Looking at the FALSE part of the first IF formula, it says to look at B15 and
compare it with the average of the range B2:B15. [Note that the range is only
14 days, not 15, that you said you want]. If B2 is greater than that average,
return the NA() error. It doesn't say what to return if B15 is <= the 14-day
average, so in that case you see FALSE. You say you see numerical restuls in
D. The formula you posted won't do that at all. It returns either NA or FALSE,
not a number.

Assuming there are headers in row 1, and the numbers start at B2, and you want
to see the 15-day moving average, cells D215 should be blank. In cell D16
you should have the formula =AVERAGE(OFFSET(B16,-14,0,15,1))

But if you are sure the formulas are correct, and the only problem is the lack
of updating, then go to Tools menu, select Options, and on the Calculation tab
be sure it's set to Automatic. If that doesn't help, post back.

In the workbook that operates correctly, the function shown appears when I
select cell D2. The answer in the cell is #N/A as I would expect for the
first 15 days.
Cells D15 onwards have numerical answers as expected and the B2 in the
Function changes to B15 Everything else in the functio stays the same.(
Column D is the Moving Average answer column.). My next data entry in B16>
Enter results in the function changing to B16 and the answer appears in D16
There must be a tick box or something which instructs the program to
recalculate and enter the new answer each time new data is entered.?
Unfortunately the original setup was done by someone else. I have examined
the commas and brackets closely but they are accurate. The OFFSET is copied
correctly although I do not understand what it means.