Question

0

267

Views

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

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.

about 4 years ago · Juan Pablo Isaza

1 answers

Answer question

0

Answer question

Find remote jobs

Accepted Answer · 2022-07-04T10:10:55.889Z

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.

0

267