Average function returning a "Divide by Zero" Error

Hi!

The following is a snippet of a metrics sheet derived from a project plan. I am trying to take the average of the "Prep Duration" column below, i.e., basically figure out the average duration of a series of tasks. I've formatted the Prep Duration column to return "N/A" and grey out the cell if the Duration column is 0. However, when I try to do AVG([Prep Duration (Majority)]:[Prep Duration (Majority)]), it is giving me a Divide by Zero error. Does anyone know why this is? Is it because I am trying to average a "Duration" type column? Are there any workarounds? Thank you!


Answers

  • L_123
    L_123 ✭✭✭✭✭✭

    you need a helper with something like

    =value(left([Prep Duration (Majority)]@row,find("d",[Prep Duration (Majority)]@row)-1

    Set that to a column formula, and base your average off of it.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!