Still baffled by the Intersect operator

[JenniferMurphy]

This table has some data from an analysis of the game of tennis. Row 4 shows the names assigned to each column. Columns D & E are assigned the names PGS_A & PGS_B, which stands for the probability of Player A/B returning a groundstroke. Column F (NumTrials) shows the number of trials (iterations) the simulator ran. Column G (Wins_A) shows the number of wins for Player A. And column H (PR_AA) shows the probability that A will win the point if A goes first.

In each pair of rows, the first row shows the expected values calculated by a formula and the second row shows the results of a computer simulation. I don't know how to draw borders around selected rows, so I separated the pairs with blank rows. These are not in the actual table.

I want to calculate the expected number of wins by multiplying the expected win percentage times the actual number of simulated points.
[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]PGS_A[/TD]
[TD="align: center"]PGS_B[/TD]
[TD="align: center"]NumTrials[/TD]
[TD="align: center"]Wins_A[/TD]
[TD="align: center"]PR_AA[/TD]
[TD="align: center"]Formulas[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]Calc→[/TD]
[TD="align: center"]25%[/TD]
[TD="align: center"]25%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]24,179[/TD]
[TD="align: center"]20.00%[/TD]
[TD]G5: =(NumTrials 6:6)*PR_AA[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]Sim→[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]120,893[/TD]
[TD="align: center"]24,167[/TD]
[TD="align: center"]19.99%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]Calc→[/TD]
[TD="align: center"]50%[/TD]
[TD="align: center"]50%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]33.33%[/TD]
[TD]G7: =ROW(G7)+1[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]Sim→[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]382,874[/TD]
[TD="align: center"]126,804[/TD]
[TD="align: center"]33.12%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]Calc→[/TD]
[TD="align: center"]75%[/TD]
[TD="align: center"]75%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Error[/TD]
[TD="align: center"]42.86%[/TD]
[TD]G9: =NumTrials ROW(G9)+1:ROW(G9)+1[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]Sim→[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]622,526[/TD]
[TD="align: center"]266,472[/TD]
[TD="align: center"]42.81%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]Calc→[/TD]
[TD="align: center"]90%[/TD]
[TD="align: center"]90%[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Error[/TD]
[TD="align: center"]47.37%[/TD]
[TD]G11: =NumTrials (ROW(G9)+1) : ROW(G9)+1)[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]Sim→[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]118,000[/TD]
[TD="align: center"]56,004[/TD]
[TD="align: center"]47.46%[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

For example, in the first set (rows 5-6), I want to calculate the expected number of wins (G5). The simulator ran 120,893 trials (F6) and the expected win percentage is 20.00% (H5), so the expected number of wins would be H5 x F6. The formula in G5 works. It contains the named range NumTrials, but the literal "6:6". I want an expression that contains no literals other than "G6".

In G7, I show that the expression "=row(G7)+1" returns the correct row number (8), but in G9 and G11, my attempts to use that to replicate G5 all get an error.

Can someone show me the correct way to multiply these values?

 

[Tetra201]

... I want an expression that contains no literals other than "G6"...

Assuming the entire column F is named as NumTrials, try:

=INDEX(NumTrials,ROW(G6))*PR_AA

Otherwise, if you still want to use the intersect, try:

=(NumTrials INDIRECT(ROW(G9)+1&":"&ROW(G9)+1))*PR_AA

 

[JenniferMurphy]

Assuming the entire column F is named as NumTrials, try:

=INDEX(NumTrials,ROW(G6))*PR_AA

Perfect. The actual expression I need is,

=INDEX(NumTrials,ROW(G6)+1)*PR_AA

But why don't any of these work?

=NumTrials (ROW(G9)+1):(ROW(G9)+1)
=NumTrials ((ROW(G9)+1):(ROW(G9)+1))
=NumTrials (ROW(G9)+1 & ":" & ROW(G9)+1)

Otherwise, if you still want to use the intersect, try:

=(NumTrials INDIRECT(ROW(G9)+1&":"&ROW(G9)+1))*PR_AA

I didn't have any requirement that it be Intersect. It just seemed like the logical choice. Your solution is better.

Thanks