Skip navigation

## Question about multiplying values in columns

693 Views 27 Replies Latest reply: Feb 10, 2013 12:30 PM by Barry
• Level 7 (27,400 points)
Currently Being Moderated
Feb 8, 2013 4:41 AM (in response to Cancuino)

Cancuino,

I think the probability of a combination adding to 4 is clearly the number of ways of throwing a sum of 4 divided by the total number of possibilities, which I reported as 12/144. The probability of a particular combination, regardless of the sum, is 1/144 = 1/6 X 1/3 X 1/8.

Jerry

• Level 6 (10,515 points)
Currently Being Moderated
Feb 8, 2013 5:08 AM (in response to Cancuino)

Cancuino,

You used dice as an example. The assumption is your dice are fair; each face has an equal chance of being rolled.  If this is the case, Jerry's solution works. If the dice are not fair (like in the tables in your initial post), it does not work. Are the "dice" not fair?

• Level 6 (10,515 points)
Currently Being Moderated
Feb 8, 2013 5:29 AM (in response to Cancuino)

A brute force method for your original problem might look like this:

Table 1 column E = A +C

Table 1 column F = B * D

Table 2 cell A2 is entered manually

Table 2 cell B2 =SUMIF(Table 1 :: E,A2,Table 1 :: F)

Columns F and B aren't actually the "probability", I just didn't know what to call it.

This can be expanded to include a "Test C".

• Level 6 (10,515 points)
Currently Being Moderated
Feb 8, 2013 6:05 AM (in response to Cancuino)

You can use simple formulas to help construct the Test A and Test B columns of Table 1 (the big table) and use LOOKUP to fill in the probabilities from the Test A and Test B tables. Or you do a combination of typing and copy/paste to fill in the table. I used LOOKUP to fill in the probabilities because if the probabilities change, you only have to change them in one place (in the respective "Test" table) versus having to fix the big table.

The rest is like in my previous post. Now that it has actual probability data, this time the "probabilities" columns are actual probabilities.

• Level 7 (27,400 points)
Currently Being Moderated
Feb 8, 2013 6:24 AM (in response to Cancuino)

Badunit's last post covers your most recent explanation perfectly, I believe. Sorry it took so long to get to the root of your question, but it was an interesting exercise.

Jerry

• Level 6 (11,905 points)
Currently Being Moderated
Feb 8, 2013 8:09 AM (in response to Cancuino)

Assuming the tables at the top with the tests and the probablities are titles "TestA" and "TestB"...

The TestB column in the large table (in the middle) is:

C2=MOD(ROW()-2, MAX(TestB :: A)+1)

select C2 and fill down as needed

The TestA column in the large table (in the middle) is:

A2=FLOOR((ROW()-2)/(1+MAX(TestB :: \$A)), 1)

select A2 and fill down as needed

• Level 6 (10,515 points)
Currently Being Moderated
Feb 8, 2013 8:59 AM (in response to Cancuino)

I didn't do anything fancy.

For column Test A, I typed zeros in rows 2 through 4. In row 5 I entered the formula =A2+1. I filled down from there. That is, I selected cell A5 then dragged down on the little circle at the bottom right corner of the selection box.

For column Test B, I typed in the first set of numbers (0,1,2). The formula in row 4 is =C2. I filled down from there.

"Prob A" =LOOKUP(A,Test A :: A,Test A :: B)

"Prob B" =LOOKUP(C,Test B :: A,Test B :: B)

• Level 7 (28,815 points)
Currently Being Moderated
Feb 10, 2013 12:30 PM (in response to Cancuino)

Hi Cancuino,

Adding the third column is complicated only in that Badunit's large table gets (much) larger.

The three smaller table (and an additional one for Unit C) are pretty much the same as in BU's example.

The large table needs two columns to record the sums and probabilities of those sums, plus two columns for each unit, one for the score, the other for the probability of that score.

So for three units, the table needs 8 columns.

The table requires one header row, plus one row for each possible permutation of results—the product of the numbers of results possible results for each unit.

For your case, I'm assuming the third unit has 4 possible results, 0, 1, 2, 3, and have assigned arbitrary probabilities to each.

So this large table requires 6 * 3 * 4 + 1 = 73 rows, and 8 columns.

I've added a footer row to hold a SUM formula to check that the probabilities sum to 1, as they should.

Here are the four smaller tables, with the table names above them:

And the larger table, named Aux. Several rows have been hidden to make the table fit this space.

Formulas:

A2 - A13, C2 - C 13 and E2 - E 13 contain entered values.

B2, C2 and D2 contain the same VLOOKUP formula, edited to match the column to the left of their locations and the table name to which they look for the probabilities.

B2: =VLOOKUP(A2,Unit A :: A:B,2,FALSE)

D2: =VLOOKUP(C2,Unit B :: A:B,2,FALSE)

F2: =VLOOKUP(E2,Unit C :: A:B,2,FALSE)

G2: =A2+C2+E2

H2: =B2*D2*F2

Fill these five formulas down their respective columns to row 14 (the highlighted row)

Enter these formulas in the indicated cells.

A14: =A2+1

C14: =C2

E14: =E2

Now Select rows 14 to 73, then go Insert (menu) > Fill > Fill Down.

Small tables:

Unit A, Unit B, Unit C, and column A of Summary contain entered data.

Summary:

B2: =SUMIF(Aux :: G,A2,Aux :: H)

Fill down to last body row.

The Footer row contains =SUM(B), and should always total 1

Regards,

Barry

Previous Next

#### More Like This

• Retrieving data ...

#### Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.