Calculate average time

Helen Rim
Helen Rim ✭✭
edited 07/21/21 in Formulas and Functions

Hello, I am trying to calculate average duration time for a date range. For example, on Monday I spent 23 min doing a project, on Tuesday I spent 35 min, on Wednesday I spent 15 min, etc. what is my Weekly Average?

When I enter the data as 0:23, 0:35, 0:15 and do an AVG formula, I get an error message. Any suggestions on how to do this formula? Thank you in advance!

Answers

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭

    AVG function requires numbers to calculate the average value. Values in your duration column are not numbers. Is there any reason you cannot specify them as 23, 35, 15? (You can always have the column header as "Time spent (in mins)"

  • First, thank you for your response. The only thing with your suggestion is that I cannot use seconds. The average time spent we are trying to calculate is down to the seconds level - so trying to find the average time spent if we had 00:23:10, 00:35:45, 00:15:30. Sorry, I should have been more clear in my first post!

  • If you haven't found an answer yet, maybe this can help.

    I am only using minutes and hours so I reformatted my numbers to hr.min and used the formula below to find the average. It's not perfect, but it gets me pretty close to the information I need.

    =IFERROR(AVG(CHILDREN()), "")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!