Forum Discussion
[Excel Tables] Structured Refs - Column vs Rows
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]]
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!
Zacross , you are welcome, good luck with Tables.