Skip to content
Advertisement

How can I optimise this code for my Google sheet macro?

I would love to make a macro that helps me average 7 values from 7 different cells(currently placeholders) but only when those are greater than 0, it should be fairly simple but I’m not an expert in JS just yet.

function macro1(){
    var total = 0;
    var ess = [];
    ess[0] = 0;
    ess[1] = 1;
    ess[2] = 0;
    ess[3] = 2;
    ess[4] = 0;
    ess[5] = 3;
    ess[6] = 0;

    if (ess[0] >= 1){
        (total++)
    }

    if (ess[1] >= 1){
        (total++)
    }

    if (ess[2] >= 1){
        (total++)
    }

    if (ess[3] >= 1){
        (total++)
    }

    if (ess[4] >= 1){
        (total++)
    }

    if (ess[5] >= 1){
        (total++)
    }

    if (ess[6] >= 1){
        (total++)
    }

    if (total <= 0){
        ( total++ )
    }

    var avg = ( ess[0] + ess[1] + ess[2] + ess[3] + ess[4] + ess[5] + ess[6] ) / ( total );
    
    console.log(avg);
}

Advertisement

Answer

Try this:

function macro1(){
    var total= 0, count = 0;
    var ess = [0, 1, 0, 2, 0, 3, 0];

    for( x in ess ){

        if(ess[x] > 0){
            total += ess[x];
            count++;
        }

    }

    var avg = total / count;

    console.log(avg);
}
Advertisement