Statistics in excel with time values (mode, stdev etc)

I almost never work with time serials in Excel. Here's what I can help with.

Originally Posted by kostas

Hi all,

I have some questions that I would like cleared up rather than problems to solve.

I am working with time values (minutes,seconds) in excel. Large quantities of them.

I was interested to see which time value occurs more often therefore I used the MODE function but it keeps turning up 00:00:00.

I have values that range from 00:00:00 to 00:20:00 (with 00:00:15 steps between them) but MODE keeps returning 0's. I DO know that 0 is not the most common value but I can't figure out what is wrong.

One of the reasons I never use time formats is because there can be a serious disconnect between what is seen in the cell and what the underlying value is. Time values (and date values) are stored as a number that represents number of days since 1900. Depending on the exact number format, any whole number serial value (0,1,2,...) can show up in the cell as 0:00:00. I think the first thing I might do when debugging this kind of problem would be to delete the time formats (edit->clear->formats) and see what the underlying serial values are.

Also, working with standard deviation I chose the STDEV function. So, in my case I have something like a 00:02:44 average value of all my records and the STDEV function returns a 00:03:12. If I understand right this means that 68% of all values fall within a 3min,12sec limit around my average value (i.e from 00:02:44 - 00:03:12 to 00:02:44 + 00:03:12). Just need to verify this.

This is a good rule of thumb for mound shaped distributions. The closer your data are to a mound shaped distribution, the closer this will be to reality. I expect your data must be skewed somewhat, because 1 standard deviation extends to below 0, but you originally stated that your data don't extend below 0.

Finally, I tried to work with the Confidence Interval but I get a huge number as a result and I suspect it's just because I'm using time values as data.

Anyone had this issue before? any ideas on how to work around it?


Regards,

Kostas


p.s some of my time values are blank or 00:00:00, could that affect my calculations in returning weird results?

I expect the statistical functions to ignore blanks, text values, and other non-numerics. But a 0 is a number and will be included in your calculations.

Perhaps what you are really after is the average, stdev, etc. of the non-zero values?

Thank you very much for your comments. You helped me clear out and understand quite a few issues.

I have solved most of my problems by turning times to seconds (just multiply the time value with 86400) so I am playing with numbers now. Whenever needed (in the end where I have to present time values again) I just convert them back to time values.

As for the blanks and the 00:00:00 I just filtered/sorted them out and removed them; seemed the quickest way to do it.

Thank you a lot for replying.

Regards,

kostas