VoseRollingStats

 

VoseRollingStats({data},statistic)

 

 

Example model

VoseRollingStats is an array function that uses efficient algorithms to calculate a rolling statistic of a column of data. It is sometimes helpful to see how sample statistics evolve with the accumulation of data, e.g. from experiments or a simulation run, to get a feel of whether the statistic has stabilized.

  • {data} - the data to calculate the rolling statistic on.

  • Statistic - This can be the text of statistic's name between double parentheses: "Mean"; 'Variance", "Stdev"; "Skewness"; "Kurtosis". The function is not case-sensitive.
    Alternatively the Statistic parameter can be a percentile value: For example, using the value 0.8 would return the rolling 80th percentile of the data set.

Whilst this is easy to do with Excel functions (e.g. using AVERAGE), for a large data set it can be very slow since each calculation repeats the same analysis.

Excel's VAR, STDEV, SKEW, KURT are slightly less accurate. Excel's PERCENTILE gives peculiar results, for example reporting a 95th percentile with just one data point and linearly interpolating between values

For example, the model shown on the right calculates the rolling mean for the data set in B2:B10.

C2: = 2/1

C3: = (2+3)/2

C4: = (2+3+4)/3

etc.

Uses

The VoseRollingStats function is most useful when plotted in an x-y scatter plot, where the sequential order of the data point (1 = 1st observation, 2 = 2nd observation, etc) is plotted on the horizontal axis against the rolling statistic of interest on the vertical axis. For example, the following Excel plot shows various rolling statistics up to the 4989th observation:

The mean has stabilized quickly, the kurtosis is still exhibiting a little volatility, etc. The VoseRollingStats function is helpful in reviewing simulation output values that have been exported to a spreadsheet, or to look at whether a data set is sufficient to provide stable moment and percentile estimates.

Note that the function returns #NA in initial places in the array where the statistic cannot be calculated. For example, at least four values are required to calculate the kurtosis of a data set.

VoseRollingStats does not subtract 3 in its kurtosis calculation, so normally distributed data would give a kurtosis of 3, rather than zero - the latter convention being used by Excel's KURT function.

 

ModelRisk

Monte Carlo simulation in Excel. Learn more

Tamara

Adding risk and uncertainty to your project schedule. Learn more

Navigation

FREE MONTE CARLO SIMULATION SOFTWARE

For Microsoft Excel

Download your free copy of ModelRisk Basic today. Professional quality risk modeling software and no catches

Download ModelRisk Basic now

FREE PROJECT RISK SOFTWARE

For Primavera & Microsoft Project

Download your free copy of Tamara Basic today. Professional quality project risk software and no catches.

Download Tamara Basic now
-->