What is a Google Apps Script to Calculate the Probability of Independent Events?

Question

I am trying to create a script that calculates the probability of 0-20 wins in a 20-game season, with the results going in Column F of this spreadsheet.

Finding the probability of 0 and 20 wins is simple and quick, because there is only one way to achieve each. Calculating the probability of 1:19 wins is simple but takes much longer, because there are 1,048,576(2^20-2) different ways that can play out. There is an awesome VBA/Excel solution for this problem here, but I would like to use GAS.

This is the my GAS attempt:

function multiplyArrays2() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sht = ss.getSheetByName("ProbWin");  
var probs = sht.getRange("B3:C22").getValues();  
    for (var i = 0; i < 2^20-1; i++) { //2^20=total combinations
       var ctr = 0; //I deleted the duplicate of this exact line below var wk and VBA ran
       var wk = 1; //Added "var" for GAS
        for (var j; i < 20; i++) {
             if ix[j] = 1 {  //this is the first line GAS underlines red. I don't know how to deal with the "x"
                wk = wk * probs[j, 1] //changed () to [] to deal with array
                ctr = ctr + 1
              } else {
                wk = wk * probs[j, 2]
              }
        }     
      var outprobs[ctr, 1] = outprobs[ctr, 1] + wk;  //added "var"
        for (var j; i < 20; i++) {
            ix[j] = ix[j] + 1 //again, I don't know how to deal with the "x"
            if ix[j] = 1 {
            ix[j] = 0
        }
        }   
   } 
  sht.getRange("F2:F22").setValues(outprobs);
};

Any help is greatly appreciated.

Answer 1

In your situation, how about the following modification?

Modified script:

function multiplyArrays2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sht = ss.getSheetByName("ProbWin");
  var probs = sht.getRange("B3:C22").getValues();
  var ix = Array(20).fill(0);
  var outprobs = [...Array(21)].map(_ => [0]);
  for (var i = 0; i <= Math.pow(2, 20) - 1; i++) {
    var ctr = 0;
    var wk = 1;
    for (var j = 0; j < 20; j++) {
      if (ix[j] == 1) {
        wk *= probs[j][0];
        ctr++;
      } else {
        wk *= probs[j][1];
      }
    }
    outprobs[ctr][0] += wk;
    for (var j = 0; j < 20; j++) {
      ix[j]++;
      if (ix[j] == 1) break;
      ix[j] = 0;
    }
  }
  sht.getRange("F2:F22").setValues(outprobs);
}

Result:

When this script is run for your sample Spreadsheet, the following result is obtained.

Note:

• When I saw your sample Spreadsheet, the sheet name is ProbWin. Please be careful this.
• This modified script is for your sample Spreadsheet. So when you changed the Spreadsheet, this script might not be able to be used. Please be careful this.