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.

R/C	C	D	E	F	G	H	I
4		PGS_A	PGS_B	NumTrials	Wins_A	PR_AA	Formulas
5	Calc→	25%	25%		24,179	20.00%	G5: =(NumTrials 6:6)*PR_AA
6	Sim→			120,893	24,167	19.99%
7	Calc→	50%	50%		8	33.33%	G7: =ROW(G7)+1
8	Sim→			382,874	126,804	33.12%
9	Calc→	75%	75%		Error	42.86%	G9: =NumTrials ROW(G9)+1:ROW(G9)+1
10	Sim→			622,526	266,472	42.81%
11	Calc→	90%	90%		Error	47.37%	G11: =NumTrials (ROW(G9)+1) : ROW(G9)+1)
12	Sim→			118,000	56,004	47.46%

<tbody>
</tbody>

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