Average function returning a "Divide by Zero" Error

Wenjie

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!

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.