Ad hoc Geek

Someone was asking whether or not it’s possible to call Excel functions from a Flex project because they needed to use VAR, COVAR and COREL. The short answer is, of course, no (at least not to my knowledge, and, even if it were possible, I don’t think it’s something you really want to encourage). A better answer would explain that these functions aren’t especially complex and that an Actionscript implementation is fairly straightforward. The wikipedia page on covariance is a little daunting if you’ve never really thought about what these functions entail (or have forgotten), but essentially it boils down to this :

private function corel(X:Array, Y:Array):Number
{
    //correlation coeff between two random variables X and Y is defined as :
    //correlation(X,Y) = covar(X,Y)/(sqrt(Var(X)) * sqrt(Var(Y)))
    //
    //var(X) = covar(X,X);
    //covar(X,Y) = E((X-xm)(Y-ym)); where xm, ym are the population means.
    return covar(X, Y)/Math.sqrt(covar(X, X) * covar(Y, Y));
}
 
private function covar(X:Array, Y:Array):Number
{
    //Sample covariance is Sum((X-xm)(Y-ym))/n-1
    //where n is the sample size and xm & ym are sample means.
    //I'll assume that X and Y are the same size...
    var total:Number = 0;
    var xm:Number = average(X);
    var ym:Number = (X == Y)? xm : average(Y);
    for (var i:int=0; i<X.length; i++)
        total += (X[i]-xm)*(Y[i]-ym);
    return total/(X.length - 1); 
}
 
private function average(X:Array):Number
{
    //Sample mean or average = Sum(X)/(sample size)
    var total:Number = 0;
    for each(var x:Number in X)
        total += x;
    return total/X.length;
}

Of course, it’s not quite that simple – my covar won’t return the same value as COVAR in Excel, because Excel uses the biased estimator (i.e. it divides by n rather than (n-1)), but this cancels out when calculating the correlation coefficient, and correcting covar to the biased estimator is trivial (multply by (n-1)/n). Personally, I think Excel is wrong for defaulting to the biased estimator for COVAR, especially since VAR uses the unbiased one (and, as I’ve written in the comments, VAR(X)=COVAR(X,X)).