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
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".
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.
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.
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.
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...
Thank you XLent this solution worked for me.
I was looking for an option where I can mark the answer as "correct".
The solution worked. Thank you.Originally Posted by XLent
Last edited by AliGW; 10-22-2019 at 07:58 AM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.Thank you XLent this solution worked for me.
I was looking for an option where I can mark the answer as "correct".
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks