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.
VBA that works in Excel:
Sub CountEm() Dim i As Long, j As Long, str1 As String, wk As Double Dim probs As Variant, outprobs(0 To 20, 1 To 1) As Double, ctr As Long Dim ix(20) As Byte
For i = 0 To 2 ^ 20 - 1
ctr = 0
wk = 1
ctr = 0
For j = 1 To 20
If ix(j) = 1 Then
wk = wk * probs(j, 1)
ctr = ctr + 1
Else
wk = wk * probs(j, 2)
End If
Next j
outprobs(ctr, 1) = outprobs(ctr, 1) + wk
For j = 1 To 20
ix(j) = ix(j) + 1
If ix(j) = 1 Then Exit For
ix(j) = 0
Next j
Next i
Range("F2:F22").Value = outprobs, End Sub
This is the my GAS attempt:
function multiplyArrays2() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sht = ss.getSheetByName("Script 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.