Calculate Average Days When # is 0

clopez365

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?

Carson Penticuff

=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.

clopez365

@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:

=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)) + AVG(COLLECT({Energy Leads Range 6}, {Energy Leads Range 1}, [Asset Class]@row, {Energy Leads Range 4}, "Sales Actioned", {Energy Leads Month}, May$1, {Energy Leads Range 5}, $Totals$1)) + AVG(COLLECT({Equities & Crypto Leads Range 6}, {Equities & Crypto Leads Range 1}, [Asset Class]@row, {Equities & Crypto Leads Range 4}, "Sales Actioned", {Equities & Crypto Leads Month}, May$1, {Equities & Crypto Leads Range 5}, $Totals$1)) + AVG(COLLECT({FX Leads Range 6}, {FX Leads Range 1}, [Asset Class]@row, {FX Leads Range 4}, "Sales Actioned", {FX Leads Month}, May$1, {FX Leads Range 5}, $Totals$1)) + AVG(COLLECT({GSD Leads Range 6}, {GSD Leads Range 1}, [Asset Class]@row, {GSD Leads Range 3}, "Sales Actioned", {GSD Leads Month}, May$1, {GSD Leads Range 5}, $Totals$1)) + AVG(COLLECT({Interest Rates Leads Range 6}, {Interest Rates Leads Range 1}, [Asset Class]@row, {Interest Rates Leads Range 5}, "Sales Actioned", {Interest Rates Leads Month}, May$1, {Interest Rates Leads Range 2}, $Totals$1)) + AVG(COLLECT({Market Data Leads Range 5}, {Market Data Leads Range 1}, [Asset Class]@row, {Market Data Leads Range 3}, "Sales Actioned", {Market Data Leads Month}, May$1, {Market Data Leads Range 4}, $Totals$1)) + AVG(COLLECT({Metals Leads Range 5}), {Metals Leads Range 1}, [Asset Class]@row, {Metals Leads Range 3}, "Sales Actioned", {Metals Leads Month}, May$1, {Metals Leads Range 4}, $Totals$1), 0)