# Sum of values going wrong in pivot

1. ## 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  Register To Reply

2. ## Re: Sum of values going wrong in pivot

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

3. ## 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.  Register To Reply

4. ## 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.  Register To Reply

5. ## 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:  `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.  Register To Reply

6. ## 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".  Register To Reply

7. ## Re: Sum of values going wrong in pivot Originally Posted by XLent Your Pivot does not know to sum each value on Base 60

...
The solution worked. Thank you.  Register To Reply

8. ## Re: Sum of values going wrong in pivot Originally Posted by pos3002 Thank you XLent this solution worked for me. I was looking for an option where I can mark the answer as "correct".  Register To Reply

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