calculating moving averages..

[marcus lance]

i'm trying to calc 15 moving averages from my data that is set up in 2
columns. the data i'm using are stock prices for from different equities
markets. so, technically, holidays aren't consistent across the the two
columns (ie., could have value in 1/14/08 in one column but not the other).
in other words two columns, some cells with #s, others are blank. I'd like to
calc the preceding 15 average wherever there was actually a number (i.e., the
numerator must contain the most recent 15 numbers, and the denominator is
always 15). any ideas?

 

[RagDyer]

If your range is not too large, try this *array* formula:

=AVERAGE(INDEX(A1:A100,LARGE(ROW(1:100)*(ISNUMBER(A1:A100)),15)):A100)
--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

If you have a very large range, you might try this *non*-array version:

=AVERAGE(INDEX(A1:A100,INDEX(LARGE(ISNUMBER(A1:A100)*ROW(A1:A100),15),)):A100)

Both of these formulas will *not* wait for 15 numbers to start averaging.
They will average any amount of numbers as they are entered, with the
restraint to use *only* the last 15.

 

[marcus lance]

RagDyer, thanks for the response, it has taken me a while to figure out what
your formulas are trying to do... i def need a refresher on the index
function.

anyhoot, i was able to test your formual (non-array version) but
unfortunately it isn't exactly what i'm after. the 15 day moving average must
contain data from the previous 15 days where there is data (blanks omitted)l

to further explain, if there are blank cells (for wknds or holidays) there
will be no data, thus, i'd like to use the precending days value(s). to give
you an example, lets assume i have data for jul 1 - jul 31, on jul31st i'm
trying to calc the 15 moving average which if i had #s for every day would be
jul 17 - jul 31st. now because jul 18-19 and jul 25-26 are wknd days, there
is no data, so i'd like to be able to use the preceding 4 days of data (ie.,
jul 16, 15, 14, 13). on aug 1st, the answer would be the same as jul 31st,
likewise for aug 2nd. on Aug 3rd, the 15-dma would use jul 14 - aug 3rd.

i hope this helps. thanks again for your help/insight.

 

[Lars-Åke Aspelin]

If your data, with possible blanks here and there is in column A
starting on row 1, you may try the following formula in cell B1

=AVERAGE(OFFSET(A1,LARGE(ROW($1:1)*ISNUMBER(A$1:A1),15)-ROW(),,
ROW()-LARGE(ROW($1:1)*ISNUMBER(A$1:A1),15)+1))

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than ENTER.

Copy the formula down column B as far as needed.

Note: The result will be #NUM! as long as there are not 15 data to
average. If you like to have the average of the available data even if
they are fewer than 15, try the following modification.

=AVERAGE(OFFSET(A1,LARGE(ROW($1:1)*ISNUMBER(A$1:A1),
MIN(15,SUM(--ISNUMBER(A$1:A1))))-ROW(),,ROW()-
LARGE(ROW($1:1)*ISNUMBER(A$1:A1),MIN(15,SUM(--ISNUMBER(A$1:A1))))+1))

Also this should be entered as an array formula.

Hope this helps / Lars-Åke

 

[marcus lance]

i'm pretty sure that accomplished it! holy smokes! brilliant.

thanks very much lars! & thanks RagDyer - i prob didn't give you enough info
to help you help me solve my issue.

now, if i can only figure out what the heck the formula says...