Finding Average Duration Time
Hello,
I am trying to get an average duration for a Sheet Summary field but getting "0". My formula is as follows:
=SUMIFS([Total Onsite Time]:[Total Onsite Time], [Store Status]:[Store Status], >"" / COUNTIF([Store Status]:[Store Status], "Store Fully Open"))
What am I missing?
Thank you in advance
Sharon C
Answers

How is the data entered into [Total Onsite Time]?

The Total onsite time is calculated. The store status "Store Fully Open" is from a drop down list.

How is it calculated? Can you copy/paste the exact formula?

Here you go,
=IF([Tech Name]@row > "", IF([Tech Arrival Time]@row > "", IF([Store Open Time]@row > "", INT([Install Duration]@row) + "." + ([Install Duration]@row  INT([Install Duration]@row)) * 60)))

Ok. The problem is with the output. When you use
+ "."
or anything else within quotes, you convert the output to a text string.
The most simple fix would be to use a VALUE function to convert it back to a numerical value:
=IF([Tech Name]@row > "", IF([Tech Arrival Time]@row > "", IF([Store Open Time]@row > "", VALUE(INT([Install Duration]@row) + "." + ([Install Duration]@row  INT([Install Duration]@row)) * 60))))
But out of curiosity... Why are you pulling the number like that? What exactly is in the [Install Duration] column?

The Install Duration column is the calculation hh:mm from a 24 hr time calculation.

What is the formula in [Install Duration]?

Here is the formula for Install Calculation. I also included the calculations for Open Time and Tech Arrival Time.
Install Calculation: =[Store Open TimeCalculation]2  [Tech Arrival TimeCalculation]2
Store Open Time Calculation: =((VALUE(LEFT([Store Open Time]@row, FIND(":", [Store Open Time]@row)  1)) + IF(CONTAINS("p", [Store Open Time]@row), IF(VALUE(LEFT([Store Open Time]@row, FIND(":", [Store Open Time]@row)  1)) <> 12, 12), IF(VALUE(LEFT([Store Open Time]@row, FIND(":", [Store Open Time]@row)  1)) = 12, 12))) + (VALUE(MID([Store Open Time]@row, FIND(":", [Store Open Time]@row) + 1, 2)) / 60)) + (([Install End Date]@row  [Install Date]@row) * 24)
Tech Arrival Time Calculation: =(VALUE(LEFT([Tech Arrival Time]@row, FIND(":", [Tech Arrival Time]@row)  1)) + IF(CONTAINS("p", [Tech Arrival Time]@row), IF(VALUE(LEFT([Tech Arrival Time]@row, FIND(":", [Tech Arrival Time]@row)  1)) <> 12, 12), IF(VALUE(LEFT([Tech Arrival Time]@row, FIND(":", [Tech Arrival Time]@row)  1)) = 12, 12))) + (VALUE(MID([Tech Arrival Time]@row, FIND(":", [Tech Arrival Time]@row) + 1, 2)) / 60)

Try this in your [Total Onsite Time] column:
=IF(AND([Tech Name]@row <> "", [Tech Arrival Time]@row <> "", [Store Open Time]@row <> ""), [Install Calculation]@row)
These column names and formulas are really familiar. Did we work together getting this sheet set up?

Thank you for the fix. It worked like a charm. My Summary Field formula is:
=SUM([Total Onsite Time]:[Total Onsite Time]) / COUNTIF([Store Status]:[Store Status],
Another Smartsheet Hero saves the day👏

We did. I thought your name was familiar. Voted your solution UP. I guess it would be the SAME Smartsheet Hero saves the day.
Thank you so much again.

Hahaha. Well then in that case... I should have built in a column that has the duration calculated in a numerical value. It is probably called [Calc Duration] or [Calculation Duration] or something like that.
That column was left in place specifically for scenarios like this where you want to use a SUMIFS. Reference THAT column in your SUMIFS, and you shouldn't need an additional column to pull the number and put in the decimal with the INT functions and whatnot.

I just found the thread. Try this (changes in bold  note the removal of a closing parenthesis from the end as well)...
=SUMIFS([Install Duration]:[Install Duration], [Store Status]:[Store Status], <> "") / COUNTIF([Store Status]:[Store Status], "Store Fully Open")

And Summary Field formula:
=SUM([Install Duration]:[Install Duration]) / COUNTIF([Store Status]:[Store Status], <> "")

I tried that and got a #INVALID VALUE error. This is what ended up working:
=SUM([Total Onsite Time]:[Total Onsite Time]) / COUNTIF([Store Status]:[Store Status], >""
Thank you again.
Help Article Resources
Categories
Check out the Formula Handbook template!