Calculate Average Days When # is 0

✭✭✭

Hello,

I'm trying to create a formula where we track the days go from sent to actioned on.

We have a column where it get's the number of days from sales sent to sales actioned on and that's simple a subtraction.

However, when I'm trying to get the average number of days it takes a lead to be actioned on by month I'm stumped by the formula. I get a "Divide by Zero" error.

0 = a good thing as that means our sales reps are acting immediately on the leads.

This is my current formula:

=(AVG(COLLECT({Ags and Options Leads Range 3}, {Ags and Options Leads Range 1}, [Asset Class]@row, {Ags and Options Leads Range 5}, \$Totals\$1, {Ags and Options Leads Range 4}, "Sales Actioned", {Energy Leads Month}, May\$1))

Does anyone have a formula to work around this?

Tags:

• ✭✭✭✭✭✭

=IFERROR(AVG(COLLECT({Ags and Options Leads Range 3}, {Ags and Options Leads Range 1}, [Asset Class]@row, {Ags and Options Leads Range 5}, \$Totals\$1, {Ags and Options Leads Range 4}, "Sales Actioned", {Energy Leads Month}, May\$1)),0)

If I understand what you are asking correctly, this will return 0 instead of the divide by zero error.

• ✭✭✭

@Carson Penticuff yes. I've done the "if error" piece to return zero instead of divide by zero. But then I realized that I would like the calculation to return 0 if the time to sales is immediate or "days = 0"

For context, I pull from multiple sheets and then add them all together: