AVG(COLLECT) issues
Paul Newcome - seems to me you are the main to ask for AVG(COLLECT) issues. I am attempting to find the average time it is taking to get from one stage to another in a sales process BUT I have 3 business units and within 1 of those business units I have to search for 2 locations. I am very new to formulas and not even sure if I can do this within 1 formula but was attempting to use this:
=AVG(COLLECT([Business Unit]:[Business Unit], OR(@cell = "BU1", Location:Location, OR(@cell = "R3 (South Bay)", @cell = "R4 (Long Beach)"), [Weeks NDA to LOI];[Weeks NDA to LOI])), [Business Unit]:[Business Unit], OR(@cell = "BU2", @cell = "BU3", [Weeks NDA to LOI])
Error coming back is unparsable and I really have no idea what I'm doing!!
I did wonder if I need to do 2 separate Sheet summary fields - the AVERAGEIF formula and then another summary field to do the AVG(COLLECT) to get the total time.
Your expertise would be appreciated. Thank you!
Best Answers
-
Ah. Ok. That clears it up for me. My apologies for the initial misunderstanding.
In this case we will want an AVG/COLLECT each of the 3 BUs and then we average those together.
BU1 w/Locations:
=AVG(COLLECT([Weeks NDA to LOI]:[Weeks NDA to LOI], [Business Unit]:[Business Unit], "BU1", Location:Location, OR(@cell = "R3 (South Bay)", @cell = "R4 (Long Beach)")))
BU2:
=AVG(COLLECT([Weeks NDA to LOI]:[Weeks NDA to LOI], [Business Unit]:[Business Unit], "BU2"))
BU3:
=AVG(COLLECT([Weeks NDA to LOI]:[Weeks NDA to LOI], [Business Unit]:[Business Unit], "BU2"))
Average them all together:
=AVG(BU1_formula, BU2_formula, BU3_formula)
=AVG(AVG(COLLECT([Weeks NDA to LOI]:[Weeks NDA to LOI], [Business Unit]:[Business Unit], "BU1", Location:Location, OR(@cell = "R3 (South Bay)", @cell = "R4 (Long Beach)"))), AVG(COLLECT([Weeks NDA to LOI]:[Weeks NDA to LOI], [Business Unit]:[Business Unit], "BU2")), AVG(COLLECT([Weeks NDA to LOI]:[Weeks NDA to LOI], [Business Unit]:[Business Unit], "BU2")))
-
I would suggest wrapping each of the individual BU formulas in an IFERROR to output a zero.
IFERROR(BU1_formula, 0)
IFERROR(BU2_formula, 0)
IFERROR(BU3_formula, 0)
Then you can wrap the entire thing in an IFERROR just in case all three are outputting zero.
=IFERROR(AVG(IFERROR(BU1_formula, 0), IFERROR(BU2_formula, 0), IFERROR(BU3_formula, 0), 0)
Answers
-
@Paul Newcome - seems to me you are the main to ask for AVG(COLLECT) issues. I am attempting to find the average time it is taking to get from one stage to another in a sales process BUT I have 3 business units and within 1 of those business units I have to search for 2 locations. I am very new to formulas and not even sure if I can do this within 1 formula but was attempting to use this:
=AVG(COLLECT([Business Unit]:[Business Unit], OR(@cell = "BU1", Location:Location, OR(@cell = "R3 (South Bay)", @cell = "R4 (Long Beach)"), [Weeks NDA to LOI];[Weeks NDA to LOI])), [Business Unit]:[Business Unit], OR(@cell = "BU2", @cell = "BU3", [Weeks NDA to LOI])
Error coming back is unparsable and I really have no idea what I'm doing!!
I did wonder if I need to do 2 separate Sheet summary fields - the AVERAGEIF formula and then another summary field to do the AVG(COLLECT) to get the total time.
Your expertise would be appreciated. Thank you!
-
Hi @Deb White
There's many problems within your OR statements and the Collect argument too. It seems you have it backward.
A screenshot or two may help understanding whet you want to do here.
Still, your formula should look more like this:
=AVG(COLLECT([Weeks NDA to LOI];[Weeks NDA to LOI], [Business Unit]:[Business Unit], OR(@cell= "BU1", @cell="BU2", @cell="BU3), [Location]:[Location], IF(INDEX(COLLECT([Business Unit]:[Business Unit], [Business Unit]:[Business Unit], "BU1"),1)="BU1", OR(@cell="R3 (South Bay)", @cell="R4 (Long Beach)"), "")))
Should be better.
Hope it helped!
-
@Deb White I am not sure I follow exactly what you are tyrying to accomplish, but here is my best guess at what your formula should look like...
=AVG(COLLECT([Weeks NDA to LOI]:[Weeks NDA to LOI], [Business Unit]:[Business Unit], OR(@cell = "BU1", @cell = "BU2", @cell = "BU3"), Location:Location, OR(@cell = "R3 (South Bay)", @cell = "R4 (Long Beach)")))
-
@PaulNewcome - so your updated formula gets me closer but it's only pulling in "jobs" from BU1 and not BU2 or BU3. I found this out by doing a count at the beggining of the formula to see where the error might lay.
Again, to try and explain it in better terms, I have 3 Business Units and within 1 of those business units (BU1) I have 2 locations that need to be included in my Average. So:
BU1 - + 2 locations
BU2 - stand alone
BU3 stand alone
Hope that helps as you navigate my mess. Thanks,
-
Ah. Ok. That clears it up for me. My apologies for the initial misunderstanding.
In this case we will want an AVG/COLLECT each of the 3 BUs and then we average those together.
BU1 w/Locations:
=AVG(COLLECT([Weeks NDA to LOI]:[Weeks NDA to LOI], [Business Unit]:[Business Unit], "BU1", Location:Location, OR(@cell = "R3 (South Bay)", @cell = "R4 (Long Beach)")))
BU2:
=AVG(COLLECT([Weeks NDA to LOI]:[Weeks NDA to LOI], [Business Unit]:[Business Unit], "BU2"))
BU3:
=AVG(COLLECT([Weeks NDA to LOI]:[Weeks NDA to LOI], [Business Unit]:[Business Unit], "BU2"))
Average them all together:
=AVG(BU1_formula, BU2_formula, BU3_formula)
=AVG(AVG(COLLECT([Weeks NDA to LOI]:[Weeks NDA to LOI], [Business Unit]:[Business Unit], "BU1", Location:Location, OR(@cell = "R3 (South Bay)", @cell = "R4 (Long Beach)"))), AVG(COLLECT([Weeks NDA to LOI]:[Weeks NDA to LOI], [Business Unit]:[Business Unit], "BU2")), AVG(COLLECT([Weeks NDA to LOI]:[Weeks NDA to LOI], [Business Unit]:[Business Unit], "BU2")))
-
Paul - so when I used the formula above I get the message #divide by zero
-
Try separating out the three individual formulas into separate cells and see if one of them either throws an error or outputs a zero.
-
Yes, my 3rd single business unit throws the error - what does this mean?
-
That means there are no rows containing a number that match the criteria.
-
Okay, so would I need to include an IFERROR on the column formula to put something in that row where there is no data to continue to look and computate this sheet summary formula?
-
I would suggest wrapping each of the individual BU formulas in an IFERROR to output a zero.
IFERROR(BU1_formula, 0)
IFERROR(BU2_formula, 0)
IFERROR(BU3_formula, 0)
Then you can wrap the entire thing in an IFERROR just in case all three are outputting zero.
=IFERROR(AVG(IFERROR(BU1_formula, 0), IFERROR(BU2_formula, 0), IFERROR(BU3_formula, 0), 0)
-
Thank you - this has helped me understand what the formula is doing - which helps me learn!
How do I mark this answered to close it?
-
There should be a button somewhere that allows you to "accept" the answer. @Genevieve P I haven't started any new "questions" since we switched over to this new Community format. Are you able to provide a little guidance for @Deb White pretty please?
-
Hi @Deb White
Happy to help! Normally, as the user who posted the initial question, you would see a comment underneath each post saying "Did this answer the question? Yes · No"
However since this thread was split from the original conversation (creating a new post), you may not have seen those prompts. I've accepted two of Paul's answers for you, so they appear at the top of the thread. Let me know if I've chosen the wrong ones and I can adjust this!
Cheers,
Genevieve
-
Help Article Resources
Categories
Check out the Formula Handbook template!
