# AVG(COLLECT) issues

Options
edited 01/22/21
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!

• ✭✭✭✭✭✭
Options

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:

BU3:

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

• ✭✭✭✭✭✭
Options

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)

«1

• ✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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:

Should be better.

Hope it helped!

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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:

BU3:

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

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭
edited 01/21/21
Options

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?

• ✭✭✭✭✭✭
Options

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?

Options

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

• ✭✭✭✭✭✭
Options

Thanks @Genevieve P!!

@Deb White Happy to help! 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!