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?
Answers
-
=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:
=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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!