+ Reply to Thread
Results 1 to 8 of 8

Sum of values going wrong in pivot

  1. #1
    Registered User
    Join Date
    10-22-2019
    Location
    India
    MS-Off Ver
    2013
    Posts
    5

    Sum of values going wrong in pivot

    Hi All,
    I have data as below in excel. The data is coming in duration format i.e 2.05 is 2hrs and 5 min, 117.15 is 117 hrs and 15 min.
    1.00
    3.00
    2.05
    0.15
    0.30
    3.45
    1.05
    117.15
    0.50

    On this data, I have created a pivot to show the sum, but the result is shown as 128.65 instead of 129.45.
    Can someone please let me know how to solve this?

    Note: The data is dynamic in the report.

    Thanks

  2. #2
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,866

    Re: Sum of values going wrong in pivot

    As per data the total sum is 128.65
    How comes fig 129.45 plz explain.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  3. #3
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,913

    Re: Sum of values going wrong in pivot

    Your Pivot does not know to sum each value on Base 60

    One option would be to store the Decimal equivalent of your Base 60 values, e.g.

    =DOLLARDE(A2,60) where A2 holds your original value
    copied down

    then use the new field in your Pivot and use a Calculated Field of =DOLLARFR(<new source field name>,60)

    and use calculated field in your Pivot, which will aggregate to 129.45

    alternative would be to store as time, and format accordingly - but obviously if you can have negatives etc this would cause issues.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    10-22-2019
    Location
    India
    MS-Off Ver
    2013
    Posts
    5

    Re: Sum of values going wrong in pivot

    If you take the values in hours:mins format , then the expected value is 129 hrs and 45 mins.(1 hr+3 hr+2hr 05 min +15min+30min+3hr 45min+1hr 05min +117hr 15min+50min).
    128.65 (1 +3 +2.05 +0.15+0.30+3.45+1.05+117.15+0.50).is being calculated as numbers and not in hours and mins which is wrong.

    Please let me know if you need further details.

  5. #5
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Sum of values going wrong in pivot

    You're confusing times with decimal values.

    Assuming your values are in A2:A10, you could use:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    This will return the value in the same format, with minutes after the decimal point.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  6. #6
    Registered User
    Join Date
    10-22-2019
    Location
    India
    MS-Off Ver
    2013
    Posts
    5

    Re: Sum of values going wrong in pivot

    Thank you XLent this solution worked for me.
    I was looking for an option where I can mark the answer as "correct".

  7. #7
    Registered User
    Join Date
    10-22-2019
    Location
    India
    MS-Off Ver
    2013
    Posts
    5

    Re: Sum of values going wrong in pivot

    Quote Originally Posted by XLent View Post
    Your Pivot does not know to sum each value on Base 60

    ...
    The solution worked. Thank you.
    Last edited by AliGW; 10-22-2019 at 07:58 AM.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,107

    Re: Sum of values going wrong in pivot

    Quote Originally Posted by pos3002 View Post
    Thank you XLent this solution worked for me.
    I was looking for an option where I can mark the answer as "correct".
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

+ 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. Pivot Column Headers in wrong order?
    By SeaVixen in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-13-2019, 06:37 AM
  2. Pivot Table Showing Wrong data
    By fuerte in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-18-2018, 12:21 PM
  3. Replies: 6
    Last Post: 08-19-2015, 07:46 PM
  4. Replies: 7
    Last Post: 04-19-2015, 12:06 PM
  5. Pivot table giving wrong results?
    By domgilberto in forum Excel General
    Replies: 1
    Last Post: 10-22-2014, 11:08 AM
  6. Pivot data appearing wrong.
    By brandysommerfel@discussions.microsoft.co in forum Excel General
    Replies: 1
    Last Post: 08-22-2006, 08:30 PM
  7. Pivot table pulling wrong value
    By RestlessAde in forum Excel General
    Replies: 1
    Last Post: 11-25-2005, 02:35 PM

Tags for this Thread

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