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.

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

--
Regards

Roger Govier


"Fambrius" <fambrius---@hotmail.com> wrote in message
news:evZs$(E-Mail Removed)...
> 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.
>
>
>

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.


"Roger Govier" <(E-Mail Removed)> escribió en el mensaje
news:eWu$(E-Mail Removed)...
> 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
>
> --
> Regards
>
> Roger Govier
>
>
> "Fambrius" <fambrius---@hotmail.com> wrote in message
> news:evZs$(E-Mail Removed)...
>> 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.
>>
>>
>>
>
>

> 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

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


"Jesper Lützen" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

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.

--
Regards

Roger Govier


"Fambrius" <fambrius---@hotmail.com> wrote in message
news:(E-Mail Removed)...
> 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.
>
>
> "Roger Govier" <(E-Mail Removed)> escribió en el mensaje
> news:eWu$(E-Mail Removed)...
>> 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
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Fambrius" <fambrius---@hotmail.com> wrote in message
>> news:evZs$(E-Mail Removed)...
>>> 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.
>>>
>>>
>>>
>>
>>
>
>