Average, StDev with more than 65536 elements?

Question

I'm trying to calculate (in VBA Excel) the Average and StDev of an array with more than 65536 elements. Something like this:

Mitja = worksheetfunction.Average(array()) DesvTip = worksheetfunction.StDev(array())

While the dimension of the array is smaller than 65536 there is no problem but, when it's bigger it gives me an error!

I know that this VBA functions can't work with more than 65536 data so, how can I obtain this parameters in VBA?

Apreciate your comments. Thanks a lot! :))

Answer 1

You can calculate mean and standard deviation without having to store all the values. Just keep a running total of sum, sum of squares, and number of points. You can have as many points as integer number of points will allow that way.

Here's how I'd do it in Java. Feel free to crib.

package statistics;

/**
 * Statistics
 * @author Michael
 * @link http://stackoverflow.com/questions/11978667/online-algorithm-for-calculating-standrd-deviation/11978689#11978689
 * @link http://mathworld.wolfram.com/Variance.html
 * @since 8/15/12 7:34 PM
 */
public class Statistics {

    private int n;
    private double sum;
    private double sumsq;

    public void reset() {
        this.n = 0;
        this.sum = 0.0;
        this.sumsq = 0.0;
    }

    public synchronized void addValue(double x) {
        ++this.n;
        this.sum += x;
        this.sumsq += x*x;
    }

    public synchronized double calculateMean() {
        double mean = 0.0;
        if (this.n > 0) {
            mean = this.sum/this.n;
        }
        return mean;
    }

    public synchronized double calculateVariance() {
        double variance = 0.0;
        if (this.n > 0) {
            variance = Math.sqrt(this.sumsq-this.sum*this.sum/this.n)/this.n;
        }
        return variance;
    }

    public synchronized double calculateStandardDeviation() {
        double deviation = 0.0;
        if (this.n > 1) {
            deviation = Math.sqrt((this.sumsq-this.sum*this.sum/this.n)/(this.n-1));
        }
        return deviation;
    }
}

Answer 2

Use the following algorithm if the data is stored in an array x(1 to N, 1 to 1), where N is the number of data points

sum = 0# : sumsq = 0#
for i=1 to N
  sum = sum + x(i,1)
  sumsq = sumsq + x(i,1)^2
next i

average = sum/N
stddev = Sqr( sumsq/N^2 - sum^2/N^3 )

:Note: To fill the array use the notation

Dim r as Range, x() as Variant
Set r = Range("A1").Resize(N,1)
x = r.Value

Answer 3

Thanks for both comments. Finally we did something similar. I hope it will be usefull for someone with the same problem. Our code:

sum = 0
sumq = 0

For i = 0 To ((2 * N) - 1)
    sum = sum + h_normal(i)
Next i

media = sum / (2 * N)

For j = 0 To ((2 * N) - 1)
    sumsq = sumsq + (h_normal(j) - media) ^ 2
Next j

desviaci(h - 1) = Math.Sqr(sumsq / ((2 * N) - 1))