Pivot Table Calculation

Hi there

I have a worksheet which contains a list of the products we analyse each
month and columns for the date the product was submitted for testing and the
date the product was released from testing. I also have a cycle time column
which is the number of days it took for the product to be released from the
date it was submitted.

I have created a pivot table from this data so I can look at individual (or
groups) products to see how many were released each month (count of release
month) and what the average cycle time for the testing was each month.

What I need now is to calculate the average cycle time per product per month
to use as a baseline however when I add a calculated field to the pivot table
and create the formula "average cycle time"/"count release month" I get
incorrect figures.

For example, "average cycle time" = 8 days "count release month" = 4
products which should give me average cycle time / product of 2 days - I get
something like 0.004.

Can someone tell me what I might be doing wrong or whether it's possible to
do this calculation in a pivot table?

Thank you, Silena

Héctor
Sat Mar 15 01:35:26 CDT 2008

hi, Silena !

instead of using a calculated field...
- back to step 3 of 4 (pivot table wizard)
- add another "average cycle time" into de data area
- change the function to: -> average

you will see the "correct" average per product/month/group

however... in the grand-total row/column (it depends on your PT layout)
you will NOT see "the correct" total/sum of averages (per product/month/group)
what you will get (or PT shows) is the "sum of averages" divided by the "sum of counts"

AFAIK, pivots has not the friendly behavior of a (say) sumproduct formula :-((

hth,
hector.

__ original post __
> I have a worksheet which contains a list of the products we analyse each month
> and columns for the date the product was submitted for testing
> and the date the product was released from testing.
> I also have a cycle time column which is the number of days it took for the product
> to be released from the date it was submitted.
>
> I have created a pivot table from this data so I can look at individual (or groups) products
> to see how many were released each month (count of release month)
> and what the average cycle time for the testing was each month.
>
> What I need now is to calculate the average cycle time per product per month to use as a baseline
> however when I add a calculated field to the pivot table and create the formula
> "average cycle time"/"count release month" I get incorrect figures.
>
> For example, "average cycle time" = 8 days "count release month" = 4 products
> which should give me average cycle time / product of 2 days - I get something like 0.004.
>
> Can someone tell me what I might be doing wrong or whether it's possible to do this calculation in a pivot table?

SilenaKK
Sat Mar 15 22:23:01 CDT 2008

Hi Hector

How do I change the function to: -> average?

Thanks Silena



"Héctor Miguel" wrote:

> hi, Silena !
>
> instead of using a calculated field...
> - back to step 3 of 4 (pivot table wizard)
> - add another "average cycle time" into de data area
> - change the function to: -> average
>
> you will see the "correct" average per product/month/group
>
> however... in the grand-total row/column (it depends on your PT layout)
> you will NOT see "the correct" total/sum of averages (per product/month/group)
> what you will get (or PT shows) is the "sum of averages" divided by the "sum of counts"
>
> AFAIK, pivots has not the friendly behavior of a (say) sumproduct formula :-((
>
> hth,
> hector.
>
> __ original post __
> > I have a worksheet which contains a list of the products we analyse each month
> > and columns for the date the product was submitted for testing
> > and the date the product was released from testing.
> > I also have a cycle time column which is the number of days it took for the product
> > to be released from the date it was submitted.
> >
> > I have created a pivot table from this data so I can look at individual (or groups) products
> > to see how many were released each month (count of release month)
> > and what the average cycle time for the testing was each month.
> >
> > What I need now is to calculate the average cycle time per product per month to use as a baseline
> > however when I add a calculated field to the pivot table and create the formula
> > "average cycle time"/"count release month" I get incorrect figures.
> >
> > For example, "average cycle time" = 8 days "count release month" = 4 products
> > which should give me average cycle time / product of 2 days - I get something like 0.004.
> >
> > Can someone tell me what I might be doing wrong or whether it's possible to do this calculation in a pivot table?
>
>
>

Héctor
Sat Mar 15 22:54:12 CDT 2008

hi, Silena !

> How do I change the function to: -> average?

op1: doble-click the field-button in the data area (step 3 of 4 pivot wizard)

op2: right-click the field title-row on the PT and choose "field..." command

and choose average in the "resume by" list options

hth,
hector.

>> instead of using a calculated field...
>> - back to step 3 of 4 (pivot table wizard)
>> - add another "average cycle time" into de data area
>> - change the function to: -> average
>>
>> you will see the "correct" average per product/month/group
>>
>> however... in the grand-total row/column (it depends on your PT layout)
>> you will NOT see "the correct" total/sum of averages (per product/month/group)
>> what you will get (or PT shows) is the "sum of averages" divided by the "sum of counts"
>>
>> AFAIK, pivots has not the friendly behavior of a (say) sumproduct formula :-((
>> __ original post __
>>> I have a worksheet which contains a list of the products we analyse each month
>>> and columns for the date the product was submitted for testing
>>> and the date the product was released from testing.
>>> I also have a cycle time column which is the number of days it took for the product
>>> to be released from the date it was submitted.
>>>
>>> I have created a pivot table from this data so I can look at individual (or groups) products
>>> to see how many were released each month (count of release month)
>>> and what the average cycle time for the testing was each month.
>>>
>>> What I need now is to calculate the average cycle time per product per month to use as a baseline
>>> however when I add a calculated field to the pivot table and create the formula
>>> "average cycle time"/"count release month" I get incorrect figures.
>>>
>>> For example, "average cycle time" = 8 days "count release month" = 4 products
>>> which should give me average cycle time / product of 2 days - I get something like 0.004.
>>>
>>> Can someone tell me what I might be doing wrong or whether it's possible to do this calculation in a pivot table?

SilenaKK
Mon Mar 17 01:48:01 CDT 2008

Hi Héctor

Thanks for helping me with this but I'm still stuck

Adding another average cycle time field in the pivot table only gives me the
same values as the first average cycle time, so I thought it might help if I
tried to explain what my pivot table looks like.

I have the following set-up:
Data
Release Month Product Name Count of Release Month Avg
Cycle Time
Jul-07 Isot 40 2
9
Jul-07 Total 2
9
Aug-07 Isot 20 6
12
Aug-07 Isot 40 1
20
Aug-07 Total 7
(forgot??)

etc...

What I need is a column in the pivot table to give me the avg cycle
time/product/month ie:

Jul-07 Isot 40 (Avg Cycle Time/Count of Rel Month) = 9/2 = 4.5
Aug-07 Isot 20 = 12/6 = 2
Aug-07 Isot 4 = 20/1 = 20

etc...

Does this explain it better??

Thanks so much, Silena


"Héctor Miguel" wrote:

> hi, Silena !
>
> > How do I change the function to: -> average?
>
> op1: doble-click the field-button in the data area (step 3 of 4 pivot wizard)
>
> op2: right-click the field title-row on the PT and choose "field..." command
>
> and choose average in the "resume by" list options
>
> hth,
> hector.
>
> >> instead of using a calculated field...
> >> - back to step 3 of 4 (pivot table wizard)
> >> - add another "average cycle time" into de data area
> >> - change the function to: -> average
> >>
> >> you will see the "correct" average per product/month/group
> >>
> >> however... in the grand-total row/column (it depends on your PT layout)
> >> you will NOT see "the correct" total/sum of averages (per product/month/group)
> >> what you will get (or PT shows) is the "sum of averages" divided by the "sum of counts"
> >>
> >> AFAIK, pivots has not the friendly behavior of a (say) sumproduct formula :-((
> >> __ original post __
> >>> I have a worksheet which contains a list of the products we analyse each month
> >>> and columns for the date the product was submitted for testing
> >>> and the date the product was released from testing.
> >>> I also have a cycle time column which is the number of days it took for the product
> >>> to be released from the date it was submitted.
> >>>
> >>> I have created a pivot table from this data so I can look at individual (or groups) products
> >>> to see how many were released each month (count of release month)
> >>> and what the average cycle time for the testing was each month.
> >>>
> >>> What I need now is to calculate the average cycle time per product per month to use as a baseline
> >>> however when I add a calculated field to the pivot table and create the formula
> >>> "average cycle time"/"count release month" I get incorrect figures.
> >>>
> >>> For example, "average cycle time" = 8 days "count release month" = 4 products
> >>> which should give me average cycle time / product of 2 days - I get something like 0.004.
> >>>
> >>> Can someone tell me what I might be doing wrong or whether it's possible to do this calculation in a pivot table?
>
>
>