AVG(COLLECT) issues

System
System Employee
edited 01/22/21 in Formulas and Functions
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!

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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

Answers

  • Deb White
    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
    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
    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
    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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
    Deb White ✭✭✭✭

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

  • Paul Newcome
    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
    Deb White ✭✭✭✭

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

  • Deb White
    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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
    Deb White ✭✭✭✭
    edited 01/21/21

    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
    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?

  • 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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!