1
I have some data from a database and want to do some analysis with the data. But there are some problems with my resulting pivot table. See the following snippet with some example data (only the important parts are visible):
Now I would like to calculate the average of the PreProcessingDurations by day. There are 5 entries with date 11.01.2018. When I calculate the average with the avg-function from Excel I get what I expect:
My goal is to use the pivot table for that task. See my table:
I don't know what excel is doing there. The result is totaly wrong. My cell format is everywhere
hh:mm:ss,000
I am totaly confused. Is there someone who can help me?
chris000r
Posted 2018-02-08T07:16:04.710
Reputation: 111
Can you please use an ad-free file sharing service like Dropbox or Onedrive to share a sample file that shows the behaviour and then explain what you would expect to see instead? – teylyn – 2018-02-08T08:07:14.113
Well, I nearly got it. If I format the Row H (PreProcessingDuration) with the general format and then calculate the pivot table, it does not round my timevalues. So this makes me to be sure, that excel rounds the value on basis of the format on the original data. I prepared an Excel Sheet as an example:
https://www.dropbox.com/s/gt36sy19eevnu7u/ExampleWithData.xlsx?dl=0
Just a wild guess: Your dates in col B are date+time. I'm not sure how well a pivot table will handle just the date portion. Test the INT function to chop off the times in a helper column and then retest using the helper column instead. – fixer1234 – 2018-02-08T08:28:25.547
@fixer1234 I already had that guess and had a look at that. I can exclude that, this is not the problem. – chris000r – 2018-02-08T08:42:02.777
do you have a problem with sharing a sample file? It would make things a lot easier – teylyn – 2018-02-09T09:31:58.257
@teylyn28 look some comments above. There is a sample file to download: dropbox.com/s/gt36sy19eevnu7u/ExampleWithData.xlsx?dl=1 – chris000r – 2018-02-09T10:01:31.467