cavalier13
New Member
- Joined
- Apr 2, 2019
- Messages
- 3
In an effort to automate an "expected value" problem, I have created a sub (MakeLogicTables) that generates two separate tables based on a single cell input. The cell input is just the number of rows with data from an original table (titled "DataTable") that includes two columns, 1 titled "P% Win" and the other titled "Value". This cell input (ex. 3) gives the number of columns in the two new tables and 2^cell input (ex. 2^3=8) is the number of rows in these tables (not including headers). The values of the cells in the two new tables are all 1's and 0's and give all the possible combinations of 1's and 0's across rows. For example, if the cell input is 2, there would be 4 rows in each table (2^2), each row giving a different outcome of 1's and 0's. (10,11,01,00) Also, for one table, there is an extra "total" column that computes the sum across rows. For the other table, there is an extra "total" column that computes the product across rows.
However, now I need the 1's and 0's to be multiplied by corresponding values in the original "DataTable". For the table that has an extra total column that computes the sum across rows, I want each 1 or 0 in a single column to be multiplied by the values in column "Value" from "DataTable" in order. For example, each cell in the first column would be multiplied by the first data value in column "value", each cell in the second column would be multiplied by the second data value in column "value" and so forth with the pattern continuing for subsequent columns.
For the table that has an extra total column that computes product across rows, for the cells in the first column, if its a 1, I want it to effectively change to the first data value in column "P% Win" (Which I guess is the same as multiplying it by the first value in column "P% Win"), if its a 0, I want that cell to effectively change to 1 MINUS the first data value in column "P% Win" (Which is just the complement of that probability). For the second column, I want the same to occur, except this time with regards to the second data value in column "P% Win", with the same pattern continuing for each subsequent column.
Lastly, the original input value (# of data rows in "DataTable") and the cell values themselves are subject to change, so I'm hoping for this solution to be dynamic.
I have placed the code that generates the two tables with outcomes of 1/0 below. Please let me know what needs to be added/ altered in this code. I would be extremely grateful if anyone could provide me with some help.
However, now I need the 1's and 0's to be multiplied by corresponding values in the original "DataTable". For the table that has an extra total column that computes the sum across rows, I want each 1 or 0 in a single column to be multiplied by the values in column "Value" from "DataTable" in order. For example, each cell in the first column would be multiplied by the first data value in column "value", each cell in the second column would be multiplied by the second data value in column "value" and so forth with the pattern continuing for subsequent columns.
For the table that has an extra total column that computes product across rows, for the cells in the first column, if its a 1, I want it to effectively change to the first data value in column "P% Win" (Which I guess is the same as multiplying it by the first value in column "P% Win"), if its a 0, I want that cell to effectively change to 1 MINUS the first data value in column "P% Win" (Which is just the complement of that probability). For the second column, I want the same to occur, except this time with regards to the second data value in column "P% Win", with the same pattern continuing for each subsequent column.
Lastly, the original input value (# of data rows in "DataTable") and the cell values themselves are subject to change, so I'm hoping for this solution to be dynamic.
I have placed the code that generates the two tables with outcomes of 1/0 below. Please let me know what needs to be added/ altered in this code. I would be extremely grateful if anyone could provide me with some help.
Code:
Sub MakeLogicTables(size As Long)
Const numSheets As Integer = 2
Dim sheets(numSheets) As Object
Dim tbl As ListObject
Dim row As ListRow
Dim col As ListColumn
Dim x As Long
Dim y As Long
Dim sheetNum As Long
For sheetNum = 1 To 2
Set sheets(sheetNum) = ActiveWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count))
Set tbl = sheets(sheetNum).ListObjects.Add
tbl.ListColumns(1).Name = "COL1"
For y = 2 To size
tbl.ListColumns.Add.Name = "COL" & y
Next y
For x = 1 To 2 ^ size
Set row = tbl.ListRows.Add
For y = 1 To size
row.Range.Cells(, y).Value = Mid(WorksheetFunction.Dec2Bin(x - 1, size), y, 1)
Next y
Next x
tbl.ListColumns.Add.Name = "Total"
Next sheetNum
'create totals columns -- sheet 1 finds the sum and sheet 2 finds the product
Set col = sheets(1).ListObjects(1).ListColumns("Total")
col.DataBodyRange.FormulaR1C1 = "=sum(@[@[COL1]:[COL" & size & "]])"
Set col = sheets(2).ListObjects(1).ListColumns("Total")
col.DataBodyRange.FormulaR1C1 = "=product(@[@[COL1]:[COL" & size & "]])"
End Sub