Add more cols to a pivot table

[Fambrius]

Hello.

I want to make some calculations using the information showed by a pivot
table. The problem is that the pivot table's size varies, so I would like to
add more columns to the pivot table, so I will have no problem if the pivot
becomes bigger (because there are more cols or rows) or smaller.

I have tried to add a new column to the pivot table make some calculations
based on the information the pivot table shows, but I can't and I'll explain
why.

I have configured the pivot table so that the data shown is an average (not
a sum). I want the calculated value to use that average value and multiply
it by 5, but unfortunately it doesn't multiply the average, it multiplies
the sum.

How can I use the results shown in the pivot table to create calculated
values?

I whish I have explained it correctly

Thanks in advance.

 

[Roger Govier]

Hi

Create a Dynamic Range to give to the Pivot Table as the source data, in
place of a fixed range.
Assuming your data starts in A1, then use I
Insert>Name>Define>Name Mydata
Refers to =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

Invoke the PT wizard from the PT toolbar>Back> in the source box, enter
= MyData in place of the range currently entered.

For more help on Dynamic Ranges take a look at Debra Dalgleish's site
http://www.contextures.com/xlNames01.html#Dynamic

 

[Fambrius]

Hello Roger.

Thanks for the answer, but that's not exactly what I need.

Imagine the following data in a worksheet:
A1 B1 C1
------------------------
car company 20
bus company 13

I create a Pivot Table with that data. I enter A1 as row, B1 as column and
C1 as data (average).

Now I want to add a column which multiplies that C1 (average) by 5, but
Excel only offers mi to multiply C1 (sum) by 5.

I want to use the cell which shows the C1 average created by the pivot
table... but I don't know how to do it.

Regards.

 

[Jesper Lützen]

Refers to =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

Would that work, if there are missing data in column A? Counta counts
the number of cells that are not empty, but if A1:A5 contains data, A6
is empty and A7:A10 contains data, your suggestion would only include
A1:A9, since there are only 9 cells in column A with data..?

It is therefore important to notice that the COUNTA($A:$A) should
always refer to a column with NO missing data in the data area.

Is this a correct observation?

Kind regards

Jesper Lützen

 

[Roger Govier]

Hi

That observation is absolutely correct.
I had (perhaps falsely) assumed that the OP's data would be a contiguous
set of rows and columns with no cells on row 1 beyond the extent of his
data having any entries. Obviously there could not be blank columns
within the range, otherwise his existing PT would not work.

--
Regards

Roger Govier

Refers to =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

Would that work, if there are missing data in column A? Counta counts
the number of cells that are not empty, but if A1:A5 contains data, A6
is empty and A7:A10 contains data, your suggestion would only include
A1:A9, since there are only 9 cells in column A with data..?

It is therefore important to notice that the COUNTA($A:$A) should
always refer to a column with NO missing data in the data area.

Is this a correct observation?

Kind regards

Jesper Lützen

 

[Roger Govier]

Hi

In that case, one way you could do it, would be in a cell outside of the
Pivot Table, alongside your first row of data, enter
=GETPIVOTDATA("c",$A$15,"a",$A17,"b","company")*5
and copy down as required.
I used column headings of a, b and c and A17 was the cell containing
"Bus" in the PT report.