CODEWITHSUNDEEP
javascriptexcel
Fish130
Fish130

Reputation: 11

Standard Deviation in Javascript, for MS Excel API

I am new to Javascript and am working on a Excel API for some simple stat functions, but I am stuck on how to write a Standard Deviation method for it, could anyone help to shed some light? Here is what I have so far:

function writedata(data) {
    Office.context.document.setSelectedDataAsync(data, function (asyncResult) {
        if (asyncResult.status === "failed") {
            writeToPage('Error: ' + asyncResult.error.message);
        }
    });
}     


function sumdata(begin, end) {
    var command ="=sum(" + begin + ":" + end + ")";
    Office.context.document.setSelectedDataAsync(command, function (asyncResult) {
        if (asyncResult.status === "failed") {
            writeToPage('Error: ' + asyncResult.error.message);
        }
    });
}

function meanvalue(begin, end) {
    var tot = "=total(" + begin + ":" + end + ")";
    var cnt = tot.valueOf.length;
    var average = tot / cnt;
    Office.context.document.setSelectedDataAsync(average, function (asyncResult) {
        if (asyncResult.status === "failed") {
            writeToPage('Error: ' + asyncResult.error.message);
        }
    });
}

Upvotes: 1

Views: 1272

Answers (1)

kennebec
kennebec

Reputation: 104780

To return return the standard deviation of an array, you need to first calculate the mean value, then apply the standard deviation formula for each itm's difference from the mean.. 

Math.mean= function(array){
    return array.reduce(function(a, b){ return a+b; })/array.length;
}

Math.stDeviation=function(array){
    var mean= Math.mean(array),
    dev= array.map(function(itm){return (itm-mean)*(itm-mean); });
    return Math.sqrt(dev.reduce(function(a, b){ return a+b; })/array.length);
}

// Example:
var A2= [6.2, 5, 4.5, 6, 6, 6.9, 6.4, 7.5];
Math.stDeviation(A2); /*value=> 0.899913190257816;*/
Math.mean(A2)/* value=>  6.0625 */

Upvotes: 2

Related Questions