# Pivot Table issues !

1. ## Pivot Table issues !

Dear clever excel people...

I have a problem with the average feature on Pivot table.

I am trying to get an average of the Average Sale price.

Input data :

Company A:
- total sales £ 60480
- Units sold 315
- unit price - £ 192

Company B:
- Total sales £ 1050
- units sold 5
- unit price - £ 210

For some when I ask the pivot table for an average i am getting £ 194.3076923

But when I calculate it I want - (overall sales)/(total quantity) = £ 192.28125

Where am I going wrong and what average is the pivot table pulling ?!!

This has been driving me mental - please help !!

thanks
Si

2. ## Re: Pivot Table issues !

Try and double left-click on the average you're getting from the Pivot Table. Then take all the data and average it and see if there's a discrepancy.

3. ## Re: Pivot Table issues !

Hi ExcelSi100,

I've tried to replicate your problem but I need to know what you are dividing to get your average.

Can you look at the attached & change it to suit what you did or explain how you got your answers?

Cheers

4. ## Re: Pivot Table issues !

Hi,

You need to upload your workbook so that we may see your problem in context.

5. ## Re: Pivot Table issues !

Ok, can anyone tell me how to upload a file - the upload button in go advanced does nothing...

6. ## Re: Pivot Table issues !

ah figured it out... sorry chaps. thanks for so many replies already !!

7. ## Re: Pivot Table issues !

When using the Average in Pivots, be aware that the average in the Pivot is average of the two (or more) results.

So, the 201 is the average of 192 & 210.

8. ## Re: Pivot Table issues !

When using the Average in Pivots, be aware that the average in the Pivot is average of the two (or more) results.

So, the 201 is the average of 192 & 210.

9. ## Re: Pivot Table issues !

Thanks. How can I use the pivot table to give an average based on the volume used ?

10. ## Re: Pivot Table issues !

Originally Posted by ExcelSi100
ah figured it out... sorry chaps. thanks for so many replies already !!
The correct average of 194.55 can be obtained by adding a Calculated Field with the following definition: =Amount /Qty
The incorrect 201.6 number is just the simple average of all the Sale price cells and ignores the Quantity weighting factor

11. ## Re: Pivot Table issues !

Hi Richard, Great thanks. That worked thanks ! legend

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1