+ Reply to Thread
Results 1 to 11 of 11

Pivot Table issues !

  1. 10-31-2016, 02:15 PM #1
    ExcelSi100
    ExcelSi100 is offline
    Registered User
    Join Date
    10-31-2016
    Location
    UK
    MS-Off Ver
    2011 14.6.8
    Posts
    5

    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
    Reply With QuoteRegister To Reply
  2. 10-31-2016, 03:16 PM #2
    Kasper(Spreadsheeto)
    Kasper(Spreadsheeto) is offline
    Registered User
    Join Date
    08-22-2016
    Location
    Copenhagen, Denmark.
    MS-Off Ver
    Excel 2016
    Posts
    28

    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.
    Reply With QuoteRegister To Reply
  3. 10-31-2016, 03:27 PM #3
    noboffinme
    noboffinme is offline
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,067

    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
    Attached Files Attached Files
    Remember you are unique, like everyone else
    Reply With QuoteRegister To Reply
  4. 10-31-2016, 03:43 PM #4
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Pivot Table issues !

    Hi,

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

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  5. 10-31-2016, 04:19 PM #5
    ExcelSi100
    ExcelSi100 is offline
    Registered User
    Join Date
    10-31-2016
    Location
    UK
    MS-Off Ver
    2011 14.6.8
    Posts
    5

    Re: Pivot Table issues !

    Ok, can anyone tell me how to upload a file - the upload button in go advanced does nothing...
    Reply With QuoteRegister To Reply
  6. 10-31-2016, 04:25 PM #6
    ExcelSi100
    ExcelSi100 is offline
    Registered User
    Join Date
    10-31-2016
    Location
    UK
    MS-Off Ver
    2011 14.6.8
    Posts
    5

    Re: Pivot Table issues !

    ah figured it out... sorry chaps. thanks for so many replies already !!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 10-31-2016, 04:43 PM #7
    noboffinme
    noboffinme is offline
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,067

    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.
    Reply With QuoteRegister To Reply
  8. 10-31-2016, 04:44 PM #8
    noboffinme
    noboffinme is offline
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,067

    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.
    Reply With QuoteRegister To Reply
  9. 10-31-2016, 04:49 PM #9
    ExcelSi100
    ExcelSi100 is offline
    Registered User
    Join Date
    10-31-2016
    Location
    UK
    MS-Off Ver
    2011 14.6.8
    Posts
    5

    Re: Pivot Table issues !

    Thanks. How can I use the pivot table to give an average based on the volume used ?
    Last edited by ExcelSi100; 10-31-2016 at 04:55 PM.
    Reply With QuoteRegister To Reply
  10. 10-31-2016, 04:58 PM #10
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Pivot Table issues !

    Quote Originally Posted by ExcelSi100 View Post
    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
    Reply With QuoteRegister To Reply
  11. 10-31-2016, 05:22 PM #11
    ExcelSi100
    ExcelSi100 is offline
    Registered User
    Join Date
    10-31-2016
    Location
    UK
    MS-Off Ver
    2011 14.6.8
    Posts
    5

    Re: Pivot Table issues !

    Hi Richard, Great thanks. That worked thanks ! legend
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Issues with a pivot table
    By The Pirate Chad in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-09-2015, 11:32 AM
  2. Duplication Issues With Pivot Table
    By xybadog in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 02-27-2015, 07:25 AM
  3. Pivot table issues
    By Fbtettey in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-28-2015, 11:14 PM
  4. Pivot Table Formula Issues
    By omerbaturay in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-17-2014, 01:14 AM
  5. Excel 2007 : Identifying issues in pivot table
    By aldek in forum Excel General
    Replies: 0
    Last Post: 06-30-2011, 02:16 PM
  6. Pivot table formatting issues
    By shellb in forum Excel General
    Replies: 0
    Last Post: 03-24-2010, 01:52 PM
  7. Pivot table macro issues
    By arvin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-19-2009, 02:31 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1