+ Reply to Thread
Results 1 to 7 of 7

Pivot Table Formula Issues

  1. #1
    Registered User
    Join Date
    07-16-2014
    Location
    Victoria, BC
    MS-Off Ver
    2013
    Posts
    4

    Pivot Table Formula Issues

    Hi guys,

    Trying to work with the Pivot Table to create a difference formula.

    It looks like this: = 'Impression #' - 'Targeted #'.

    I have hundreds of data points for each of those varying from 0.0 to 123.0.

    I'm looking for individual differences between the two points

    The result, however, is a sum of the numbers.

    Screen Shot 2014-07-16 at 4.47.55 PM.png

    Does anyone know how to fix this problem?

    I change the table to "Summarized by Sum, Count or Average" but it doesn't change the values of the new column at all.

    The columns on the attached table are as follows: Impression #, Targeted #, and Difference.

    Thank you everyone.

  2. #2
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Pivot Table Formula Issues

    Hi,

    I'm not sure that I fully understand your question (I thought I did, but I'm pretty sure that picture confuses the issue, which is why workbooks are MUCH better than images if you're going to upload an attachment). Despite this, I'm going to have a crack at it anyway:

    Try moving your difference calculation outside of the PivotTable. Then you won't be receiving a summary of information, you will get an appropriate value for each row in the PivotTable.

    I hope this helps If not, please upload your workbook (or at least a sample of it).

  3. #3
    Registered User
    Join Date
    07-16-2014
    Location
    Victoria, BC
    MS-Off Ver
    2013
    Posts
    4

    Re: Pivot Table Formula Issues

    Thank you for your response Ajryan88.

    I'd love to upload my workbook but for privacy purposes I can't (it's got proprietary information on it).

    Let me explain it once again.

    The pivot table is using a set of data on a separate workbook that is not a part of it: raw data.

    The raw data is divided into columns A, B, and C for example.

    These columns are separated by columns per event. Let's say Action A, Action B and Action C.

    So the first 10 data points on column A are Action A's, then, on the same column the next 30 points are Action B and so on. This happens on other columns in the same fashion.

    What I want my column in my table to do is, take data points from column A and column B and subtract those points from each other. However, the pivot table (with the formula, A - B) is returning sum values which are what you're seeing in the image. The image includes Column A and Column B on the left respectively. And the pivot table column on the right should be simply subtracting B from A.

    However this isn't happening.

    Any further ideas?

    Oh also moving the calculation outside the pivot table isn't reasonable because I need the calculation to change as more data points are added to the pivot table and still be dynamic. Meaning that if I "hide" Action A, the numbers won't break. Do you get what I mean?

  4. #4
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Pivot Table Formula Issues

    But a PivotTable is going to display a summary of data, and you're expecting to calculate the difference between the 2 columns for every row. So the PivotTable will have less rows than what you will need to display all of the data that you are hoping to calculate.

    To get what you want, you will NEED to display it outside of a PivotTable. However, as you can't upload your workbook, can you please make a dummy version with somewhat sensible numbers so that I can show you what I mean. It may result in a formula that is slightly more in-depth than a simple subtraction, but I should be able to achieve what you're looking for.

    In the sample workbook, please also include your expected results, so that I can do some work on it and compare my results with what you're expecting. This way both of us can save time and hopefully get you an answer sooner.

    Thanks

  5. #5
    Registered User
    Join Date
    07-16-2014
    Location
    Victoria, BC
    MS-Off Ver
    2013
    Posts
    4

    Re: Pivot Table Formula Issues

    Uhm...not sure where the "attachment button" is lol. I see for images etc but not for excel files.

  6. #6
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Pivot Table Formula Issues

    I have a problem with the Workbook you sent me which I think needs to be sorted before I can successfully help you...

    In your PivotTable, if you expand "BTR Single Page Auto-Submit (2) so that the dates are visible, and then look at the Average of impression eCPM ($) column for the 1/06/2014.
    Then go to the raw data table, and filter the campaigns for this same name, and then filter the dates for this same date. Then take an average of the values in column K.

    The average in the PivotTable is 24.28722281 but the average that I calculate in the raw data is 14.55 (or 29.10 if I exclude all 0 entries).

    Can you please verify that the PivotTable data is up-to-date with the raw data, and let me know what the cause of this discrepancy is?

    Thanks

    EDIT: Oh and to add an attachment, go to "Go Advanced" --> "Manage Attachments"

  7. #7
    Registered User
    Join Date
    07-16-2014
    Location
    Victoria, BC
    MS-Off Ver
    2013
    Posts
    4

    Re: Pivot Table Formula Issues

    Let me look into this. Probably won't reply for a few hours.

    Thanks anyways Ajryan88.

+ Reply to 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 charting pivot table
    By bogdn in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 06-03-2013, 09:02 AM
  2. Pivot Table Scroll Area Issues
    By bennon05 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-07-2012, 12:26 PM
  3. Excel 2007 : Identifying issues in pivot table
    By aldek in forum Excel General
    Replies: 0
    Last Post: 06-30-2011, 02:16 PM
  4. Pivot table formatting issues
    By shellb in forum Excel General
    Replies: 0
    Last Post: 03-24-2010, 01:52 PM
  5. 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1