pivot table: how to use already calculated columns?

[#1]

Hi there,

i hope you can help me:

Column1  |    Column2   |    Column3  |    Column4

1                   3                   5                   formula = column1 + column3

In the Column 1 and 3 i am using complexe formulas and i dont want to recalculate them in colum 4.
Is there a possibility to access the already calculated field values without using the same formulas like column 1 and 3?

I have already tried out the function column(n), but it doesnt work.

Thank and best regards,

Sabrina

[rubenmarin]

Hi Sabrina, Column(1)+Column(3) should work, but also can fail in some conditions, you can also use the column names enclosed in brackets:

[Column1]+[Column3] // If there are no espaces the brackets aren't needed

This also can fail, but it's quick to try.

[alexandros17 · Accepted solution]

You can access columns (expressions) in 2 ways:

referencing the column number so in you case in column 4 you will write Column(1) + Column(2), pay attention because if you change the order of the columns formula will not work as you want.

The second way is referencing the columns name so [Column1] + [Column2] (with brakets) bu tthis works only if columns names have NOT computed parts (So you cannot reference for example Column1 & Year(Today())

hope this helps

[datanibbler]

Hi,

I can only add a little bit to what Ruben has already said. That's just the way to go. You can build one column on the ones you have calculated before. This, however, only works well when the column_names do not have spaces and do not run over several lines.

In case they do, you will have to duplicate the formula for one column, give it a simpler name (no spaces, one line) and just disable the display of that second one (on the "presentation" tab). You can still use it to build more columns on it.

[Colin-Albert]

You can use the expression labels to refer to the calculation.

If you have expressions named "Sales Value"  and "No of Orders"

then you can add an expression for Average Value as [Sales Value] / [No of Orders]

If you edit the column labels, the new column name is not updated in any expressions, the expressions will just error, so make sure you have named the columns correctly first.