Accessing a cell in a named column

[JenniferMurphy]

In the following table, I need the formulas in columns H & I to refer to the data in column G but at one row down. Column E is assigned the name PP_A, column F the name PP_B, and column G the name Iterations.

I would like to replace the references to column G in the formulas with the name of that column, but if I do, I get the cell in G on the same row as the calling cell.

R/C	C	D	E	F	G	H	I	J	K
5	PGS_A	PGS_B	PP_A	PP_B	Iterations	Wins_A	Wins_B	Formulas	Formulas
6	52.00%	50.00%	51.35%	48.65%		61,620	58,380	H6: =G7*PP_A	I6: =G7*PP_B
7					120,000
8	62.00%	60.00%	51.59%	48.41%		185,724	174,276	H8: =G9*PP_A	I8: =G9*PP_B
9					360,000
10	72.00%	70.00%	52.02%	47.98%		249,696	230,304	H10: =G11*PP_A	I10: =G11*PP_B
11					480,000

<tbody>
</tbody>

I have tried using Cell, Address, Offset, Row, Column and other functions, but have not been able to get anything to work.

Does anyone have any magic I can use?

Thanks

 

[sandy666]

if I understood well maybe:
=IF($G6="","",$G6*E5)
and
=IF($G6="","",$G6*F5)

 

[JenniferMurphy]

if I understood well maybe:
=IF($G6="","",$G6*E5)
and
=IF($G6="","",$G6*F5)

I don't understand these expressions, but I am trying to eliminate literal cell references (G6, $G6, G$6, or $G$6). I want to replace the "G7" in the expression in H6 with some form of the named range (column) "Iterations". I want the result in H6 to be the product of G7 & E6 but using named ranges, not literal cell addresses.

 

[sandy666]

without any references but with PowerQuery (Get&Transform)

PGS_A	PGS_B	PP_A	PP_B	Iterations
52.00%​	50.00%​	51.35%​	48.65%​
				120,000​
62.00%​	60.00%​	51.59%​	48.41%​
				360,000​
72.00%​	70.00%​	52.02%​	47.98%​
				480,000​
PGS_A	PGS_B	PP_A	PP_B	Iterations	Wins_A	Wins_B
52.00%​	50.00%​	51.35%​	48.65%​		61620​	58380​
				120000​
62.00%​	60.00%​	51.59%​	48.41%​		185724​	174276​
				360000​
72.00%​	70.00%​	52.02%​	47.98%​		249696​	230304​
				480000​

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Duplicate = Table.DuplicateColumn(Source, "Iterations", "Iterations - Copy"),
    Fill = Table.FillUp(Duplicate,{"Iterations - Copy"}),
    WinsA = Table.AddColumn(Fill, "Wins_A", each [#"Iterations - Copy"]*[PP_A]),
    WinsB = Table.AddColumn(WinsA, "Wins_B", each [#"Iterations - Copy"]*[PP_B]),
    RC = Table.RemoveColumns(WinsB,{"Iterations - Copy"}),
    Type = Table.TransformColumnTypes(RC,{{"PGS_A", Percentage.Type}, {"PGS_B", Percentage.Type}, {"PP_A", Percentage.Type}, {"PP_B", Percentage.Type}, {"Wins_A", type number}, {"Wins_B", type number}})
in
    Type[/SIZE]

or

PGS_A	PGS_B	PP_A	PP_B	Iterations	Wins_A	Wins_B
52.00%​	50.00%​	51.35%​	48.65%​		61620​	58380​
				120,000​
62.00%​	60.00%​	51.59%​	48.41%​		185724​	174276​
				360,000​
72.00%​	70.00%​	52.02%​	47.98%​		249696​	230304​
				480,000​

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Duplicate = Table.DuplicateColumn(Source, "Iterations", "Iterations - Copy"),
    Fill = Table.FillUp(Duplicate,{"Iterations - Copy"}),
    WinsA = Table.AddColumn(Fill, "Wins_A", each [#"Iterations - Copy"]*[PP_A]),
    WinsB = Table.AddColumn(WinsA, "Wins_B", each [#"Iterations - Copy"]*[PP_B]),
    Type = Table.TransformColumnTypes(WinsB,{{"PGS_A", Percentage.Type}, {"PGS_B", Percentage.Type}, {"PP_A", Percentage.Type}, {"PP_B", Percentage.Type}, {"Wins_A", type number}, {"Wins_B", type number}}),
    ROC = Table.SelectColumns(Type,{"Wins_A", "Wins_B"})
in
    ROC[/SIZE]

 

[JenniferMurphy]

without any references but with PowerQuery (Get&Transform)

Wow!!! I'm afraid that is way over my head.

I was able to find a solution along the lines that I can comprehend. This expression eliminates literal cell references (except for the calling cell, which is OK, and uses the named column "Iterations".

=INDIRECT(ADDRESS(ROW(H6)+1,COLUMN(Iterations)))*PP_A

I was hoping for something simpler, but this will do.

Excel really needs a simpler, more compact cell addressing syntax.

 

[sandy666]

Wow!!! I'm afraid that is way over my head.

the devil’s not so black as he is painted

I was able to find a solution

I'm glad you found a solution

Have a nice day

 

[Rick Rothstein]

I was able to find a solution along the lines that I can comprehend. This expression eliminates literal cell references (except for the calling cell, which is OK, and uses the named column "Iterations".

=INDIRECT(ADDRESS(ROW(H6)+1,COLUMN(Iterations)))*PP_A

I was hoping for something simpler, but this will do.

This formula will produce the same result as the one you posted above...

=(7:7 Iterations)*(6:6 PP_A)

Just so you know, the space character as I used it above (with a row reference on one side a column reference on the other side) is the Intersect operator (it retrieves the value at the intersection of that row and column).

 

[JenniferMurphy]

This formula will produce the same result as the one you posted above...

=(7:7 Iterations)*(6:6 PP_A)

Just so you know, the space character as I used it above (with a row reference on one side a column reference on the other side) is the Intersect operator (it retrieves the value at the intersection of that row and column).

Perfect. I fiddled around with the Intersect operator, but couldn't get it to work. Thanks!