Generating new table cell values by multiplying each cell by values in an original input table

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.

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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,886
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top