How to Make an Averaging Formula Exclude Zero Data in a Time Format (00:00:00)

[hmc74]

I work for a transportation contractorAttachment 437642 and I'm building a spreadsheet to track the time it takes for a train to travel from one station to another (runtime) as well as how much time it spends at each station (dwelltime). I built a 'summary' worksheet that averages the runtime and dwelltime over a period of 20 days. I would like to look at accurate runtime and dwelltime averages on the summary sheet before the project is complete. As I understand it, I can only do this by asking the averaging formula to ignore cells that haven't had data entered into them yet (00:00:00). How do I ask the formula to ignore 'zero data' formatted as time?

I have included a sample workbook in the same format as my actual spreadsheet and with the same averaging formulas I am using. Any help would be deeply appreciated. I am also new at this. If there is anything I can do better to make my posts more concise in the future, please let me know. Thanks in advance.

[jewelsharma]

For Excel, entering 00:00:00 is not the same as "haven't had data entered into them yet". 00:00:00 is a definite time in excel. It represents 12:00:00 AM (aka Midnight).

Replace the formula in your RUNTIME with something like this:

Please Login or Register  to view this content.

Similarly, for DWELL TIME:

Please Login or Register  to view this content.

This will ensure that a 0 value is converted to a blank cell, and not to 00:00:00. By default, AVERAGE formula ignores all blank cells, and your calculations should be fine.

[hmc74]

Thank you.