This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hi all!
I'm trying to calculate the average for a range of cells for operations reporting - The range of cells are formatted as a time value and I'm trying to find the average TRT, (turnaround time), of that range of cells. If I autosum, I get #DIV/O! errors, the same if I pivot the information. If I use formulas I'm either returned a #DIV/O! or #VALUE! errors. I presume it's the empty cell that's causing the issue but cannot find a solution to this...
| TRT - Hour | TRT - AVG |
|---|---|
| #VALUE! | |
| 01:02 | #VALUE! |
| 00:08 | #VALUE! |
| 00:04 | #VALUE! |
| 00:05 | #VALUE! |
| 00:06 | #VALUE! |
| 00:05 | #VALUE! |
| 01:13 | #VALUE! |
| 00:19 | #VALUE! |
| 00:05 | #VALUE! |
| 00:30 | #VALUE! |
| 00:04 | #VALUE! |
| 00:12 | #VALUE! |
| 01:26 | #VALUE! |
| 00:18 | #VALUE! |
| 00:48 | #VALUE! |
| 00:11 | #VALUE! |
Formulas used in second column =
=AVERAGE(VALUE(TEXT("00"&[TRT - Hour],"hh:mm:ss")))
=AVERAGEIF([TRT - Hour],">0")
=AVERAGE([TRT - Hour])
etc.
Any help would be greatly appreciated at this point!
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Hi,
Your Time values are left aligned prompting me to believe that they are text entries. Use the ISNUMBER() function to check for whether they are numbers or not.
If you are using tables, then you need the table name:
=AVERAGE(Table1[TRT - Hour])
Why are you using a column of formulas if you want an average?