Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
array revisited
Hi, Sorry to ask again. Im asigning values to a vba static array, when
I got 100 values, is there a way to calculate the average of the last 50 values, then if I add further values, Iwant to average only the last 50 added. Regards Robert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
array revisited
You can determine the average by looping through the Array and computing the
arithmetical mean.... For x = 1 to 50 xSum = xSum + myArray(x) Next x xMean = xSum / 50 (above assumes Option Base = 1) The question posed, to calculate a moving average, depends on the ability to track the last value in the array. The array has 100 values, any or all of which could be set to zero. You could scan the array looking for the first empty value and then use that to compute the mean of the preceding 50 values. Sub aTest() Dim x As Integer, LastEntry As Integer Dim xSum As Double Dim xSpan As Integer xSpan = 50 ' sets the number of values to compute the mean over ' determine the last non-empty value in the array For x = 1 To 100 If IsEmpty(myArray(x)) Then LastEntry = x - 1 Exit For End If Next x ' compute the mean for the last 50 ( xSpan) of values in array If LastEntry = xSpan Then For x = LastEntry To LastEntry - xSpan + 1 Step -1 xSum = xSum + myArray(x) Next x MsgBox "Mean: " & xSum / xSpan Else MsgBox "Insufficient entries to compute" End If End Sub -- Cheers Nigel wrote in message oups.com... Hi, Sorry to ask again. Im asigning values to a vba static array, when I got 100 values, is there a way to calculate the average of the last 50 values, then if I add further values, Iwant to average only the last 50 added. Regards Robert |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
array revisited
Nigel, Thanks for your reply. Very good. Working on it now. Thanks for
pointing me in right direction. Regard Robert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
14 Day Average REVISITED | Excel Discussion (Misc queries) | |||
Help with averages revisited | Excel Worksheet Functions | |||
EXCEL Stability Revisited | Excel Discussion (Misc queries) | |||
Last row, last column revisited | Excel Programming | |||
Revisited: If Cell in row has data then do this: HELP | Excel Programming |