AVG(COLLECT) issues

System

This discussion was created from comments split from: Averageifs Question.

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!

Paul Newcome

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 Newcome

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)

Deb White

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

David Joyeuse

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!

Paul Newcome

@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)")))

Deb White

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

Paul Newcome

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")))

Deb White

Paul - so when I used the formula above I get the message #divide by zero

Paul Newcome

Try separating out the three individual formulas into separate cells and see if one of them either throws an error or outputs a zero.

Deb White

Yes, my 3rd single business unit throws the error - what does this mean?

Paul Newcome

That means there are no rows containing a number that match the criteria.

Deb White

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?

Paul Newcome

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)

Deb White

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?

Paul Newcome

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?

Genevieve P.

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

Paul Newcome

Thanks @Genevieve P!!

@Deb White Happy to help! 👍️