Possible solution using Array Formula

[Don]

I have a single column of numbers that represents 365 days of a specific
stock's price in descending date order.

I want to calculate the average price for the 20 prior days for each of the
most recent say 100 days. This is called a 20day moving average in stock
talk.

Then I want to create one average of those 20day moving averages, let's call
this a super-average.

This can be pretty simple if I use one more column to calculate the 20day
moving average for each of the most recent 100 days, and then average that
new column at the bottom cell to create the super-average.

However, is there a way to compute the super-average without using an
additional column and instead using only one additional cell? Perhaps with
an Array Formula?

My actual problem is much more complicated than the above and involves maybe
1000 stocks so I don't really want additional columns for each stock. BTW,
the only number I need is the super-average.

Thanks
Don


..

 

[David Heaton]

I have a single column of numbers that represents 365 days of a specific
stock's price in descending date order.

I want to calculate the average price for the 20 prior days for each of the
most recent say 100 days. This is called a 20day moving average in stock
talk.

Then I want to create one average of those 20day moving averages, let's call
this a super-average.

This can be pretty simple if I use one more column to calculate the 20day
moving average for each of the most recent 100 days, and then average that
new column at the bottom cell to create the super-average.

However, is there a way to compute the super-average without using an
additional column and instead using only one additional cell? Perhaps with
an Array Formula?

My actual problem is much more complicated than the above and involves maybe
1000 stocks so I don't really want additional columns for each stock. BTW,
the only number I need is the super-average.

Thanks
Don

.

Don,

Here is an option using a UDT

Press ALT+F8 and insert a user module, then paste in the code below

Function SuperAverage(rng As Range) As Double

Dim i As Integer
Dim RowCount As Integer
Dim tmpRng As Range
Dim Avg20 As Double
RowCount = 1
For i = rng.End(xlDown).Row - 100 To rng.End(xlDown).Row
Set tmpRng = Range(Cells(i - 19, rng.Column), Cells(i,
rng.Column))
Avg20 = Avg20 + Application.Average(tmpRng)
RowCount = RowCount + 1
Next i
SuperAverage = Avg20 / RowCount

End Function

Then in your worksheet use =SuperAverage(A1:A500) (replace the range
with whatever your is)

hth

David

 

[Don]

David,

Thank you..

I understand your concept, but it doesn't seem to give the correct result.

I was able to set a breakpoint at the "Set tmpRng = ....." statement and
I=115. Also, rng.End(xlDown).Row=215. Neither of these values make sense to
me.

The range I defined when calling the function was g2:g101.

As an experiment, I changed the range to g2:g10 and got the same values as
above(115 and 215). I think the "rng.End(xlDown).Row" statement is returning
the last non-empty cell in the column.

Any advice is appreciated.

Thanks,

Don

I have a single column of numbers that represents 365 days of a specific
stock's price in descending date order.

I want to calculate the average price for the 20 prior days for each of
the
most recent say 100 days. This is called a 20day moving average in stock
talk.

Then I want to create one average of those 20day moving averages, let's
call
this a super-average.

This can be pretty simple if I use one more column to calculate the 20day
moving average for each of the most recent 100 days, and then average that
new column at the bottom cell to create the super-average.

However, is there a way to compute the super-average without using an
additional column and instead using only one additional cell? Perhaps with
an Array Formula?

My actual problem is much more complicated than the above and involves
maybe
1000 stocks so I don't really want additional columns for each stock. BTW,
the only number I need is the super-average.

Thanks
Don

.

Don,

Here is an option using a UDT

Press ALT+F8 and insert a user module, then paste in the code below

Function SuperAverage(rng As Range) As Double

Dim i As Integer
Dim RowCount As Integer
Dim tmpRng As Range
Dim Avg20 As Double
RowCount = 1
For i = rng.End(xlDown).Row - 100 To rng.End(xlDown).Row
Set tmpRng = Range(Cells(i - 19, rng.Column), Cells(i,
rng.Column))
Avg20 = Avg20 + Application.Average(tmpRng)
RowCount = RowCount + 1
Next i
SuperAverage = Avg20 / RowCount

End Function

Then in your worksheet use =SuperAverage(A1:A500) (replace the range
with whatever your is)

hth

David