Help needed - AVG(Collect).. - #divine by zero error

Ryan Park

Hi,  This is Ryan and help needed.

I'd like to get an average value either project status with ''in progress'' or ''complete'' whichever data available but with below formula, it shows #divine by zero' error.   When I try use only 1 condition either "in progress'" or "complete'', then it returns value.     

=AVG(COLLECT({OSAT project-Dashboard for chart_Progress}, {OSAT project-Dashboard for chart_category}, ="PD", {OSAT project-Dashboard for chart_P.status}, ="in progress", {OSAT project-Dashboard for chart_P.status}, ="complete"))

Paul Newcome

That is because having it separated like that implies an AND. You would need to incorporate an OR function like so...

=AVG(COLLECT({OSAT project-Dashboard for chart_Progress}, {OSAT project-Dashboard for chart_category}, ="PD", {OSAT project-Dashboard for chart_P.status}, OR(@cell ="in progress", @cell ="complete")))

Paul Newcome

That is because having it separated like that implies an AND. You would need to incorporate an OR function like so...

=AVG(COLLECT({OSAT project-Dashboard for chart_Progress}, {OSAT project-Dashboard for chart_category}, ="PD", {OSAT project-Dashboard for chart_P.status}, OR(@cell ="in progress", @cell ="complete")))

Ryan Park

It works perfect.  Thanks much Paul.  Have a good day.

Paul Newcome

Happy to help. 👍️

o_c

I am trying to get the average days that a contract took to execute after a specific date, however only for items with the text status of Support, what is the best way to do this in smartsheet?

=AVERAGEIF([Status]:[Status], "Support", [Contract Signed]:[Contract Signed], >DATE(2020, 7, 1), [Contract to GL Days]:[Contract to GL Days])

Paul Newcome

@o_c You are going to want an AVG/COLLECT instead.

=AVG(COLLECT([Contract to GL Days]:[Contract to GL Days], [Status]:[Status], "Support", [Contract Signed]:[Contract Signed], >DATE(2020, 7, 1))

AVERAGEIF only allows for a single range/criteria set. Using the COLLECT function allows for more to be built in.