[Excel Tables] Structured Refs - Column vs Rows

[Zacross]

Hello everyone!

I'm trying optimize a few excel files for work and started using more actively the Excel Table to ensure maximum automatization
I was just wondering what's the difference between referencing a column or a specific row in a table formula as it appears that the column reference always take the row it is in
Let's take a simply example working within a table. I want to multiply Column A of the table by a given cell (say Q1)
My formula would be
=$Q$1*[Column A]

This formula would copy down automatically until the end of the table. However, the structured reference [Column A] will always take the row that the formula is typed in. So If I'm on row 2, the formula will evaluate to =$Q$1*A2 and if I'm on row 55, the formula will evaluate to =$Q$1*A55

So what exactly is the difference with
=$Q$1*[@[Column A]]

In practice both formula evaluate to exactly the same.

I'm asking because when doing more complex formula I have a doubt whether it is necessary to specify the row or not. For example if I write

=$A1/sum([Column A])
like

[Column A] / sum([Column A]) --> I feel it might get the wrong answer so I always write it

[@[Column A]] / sum([Column A])

I hope this is understandable. Put simply - I don't understand how the @Row and [Column] are different

Thanks already to all for your help!
Best

[#2]

@Zacross 

It depends on which version of Excel you are.

=$Q$1*[Column A] returns an array where entire column A is multiplied on Q1. If Excel doesn't support dynamic arrays, it silently do implicit intersection and returns only one element of this array for the current row.

If Excel supports dynamic arrays, above doesn't work and you will have SPILL error since Excel tries to return entire array and have no room for it in one cell.

Thus correct will be =$Q$1*[@[Column A]] which multiplies Q1 on the value in column A from current row and return that single value into the cell. Such explicit notation works in both versions.

To work with aggregation you may add Total row to table, buy default it aggregates the column as =SUBTOTAL(109,[Column A]) (i.e. sum of all not-filtered values of Column A), but you may use any other formula.

With that in each row percent to sum will be

=[@[Column A]]/Table1[[#Totals],[Column A]]

[#3]

@Sergei Baklan 

Hello Sergei and thanks for your answer!

Unless I'm mistaken, I'm on Office 365

Based on your answer it looks like it behaves like your initially mentions : it silently do implicit intersection and returns only one element of this array for the current row

In practice it doesn't mess up my calculations, but I agree that the correct synthax would be [@Column A]

I'll take care about this in the future! But your answer was really clear - Thanks again!

[#4]

@Zacross , you are welcome, good luck with Tables.