Need help with AVG(Collect - multiple criteria same column

Options
meb_0101
meb_0101 ✭✭✭
edited 11/02/22 in Formulas and Functions

I'm trying to use Avg(Collect to get the average number of days from another sheet. My problem is I need to search one column for 2 key words.

Is Avg(Collect similar to Countifs( where you can't do this? The below formulas work (XXX is a holder) separately but I'm at a loss for how to combine them to yield the correct answer. I tried combining them but the average was incorrect.


=AVG(COLLECT({Active#DaysOpen}, {ActiveSegment}, HAS(@cell, "XXX"), {ActiveRequestStatus}, CONTAINS("ready", @cell)))

=AVG(COLLECT({Active#DaysOpen}, {ActiveSegment}, HAS(@cell, "XXX"), {ActiveRequestStatus}, CONTAINS("assign", @cell)))


Thank you!

Tags:

Best Answer

  • meb_0101
    meb_0101 ✭✭✭
    edited 11/02/22 Answer ✓
    Options

    So this ended up working but sheesh... wish there was a simpler way lol

    =(SUM(COLLECT({Active#DaysOpen}, {ActiveSegment}, HAS(@cell, "XXX"), {ActiveRequestStatus}, CONTAINS("assign", @cell))) + SUM(COLLECT({Active#DaysOpen}, {ActiveSegment}, HAS(@cell, "XXX"), {ActiveRequestStatus}, CONTAINS("ready", @cell)))) / (COUNT(COLLECT({Active#DaysOpen}, {ActiveSegment}, HAS(@cell, "XXX"), {ActiveRequestStatus}, CONTAINS("assign", @cell))) + COUNT(COLLECT({Active#DaysOpen}, {ActiveSegment}, HAS(@cell, "XXX"), {ActiveRequestStatus}, CONTAINS("ready", @cell))))

Answers

  • Christian G.
    Christian G. ✭✭✭✭✭✭
    Options

    You might have to calculate the average yourself:

    =(Count(COLLECT({Active#DaysOpen}, {ActiveSegment}, HAS(@cell, "XXX"), {ActiveRequestStatus}, CONTAINS("assign", @cell))+ Count(COLLECT({Active#DaysOpen}, {ActiveSegment}, HAS(@cell, "XXX"), {ActiveRequestStatus}, CONTAINS("ready", @cell))))/ Count({Active#DaysOpen})

  • meb_0101
    meb_0101 ✭✭✭
    Options

    @Christian G. Thank you for the suggestion. I tried that but it did not work either.

  • Christian G.
    Christian G. ✭✭✭✭✭✭
    Options

    Which part does not work ? Can you count with a formula both tje status independently?

  • meb_0101
    meb_0101 ✭✭✭
    edited 11/02/22 Answer ✓
    Options

    So this ended up working but sheesh... wish there was a simpler way lol

    =(SUM(COLLECT({Active#DaysOpen}, {ActiveSegment}, HAS(@cell, "XXX"), {ActiveRequestStatus}, CONTAINS("assign", @cell))) + SUM(COLLECT({Active#DaysOpen}, {ActiveSegment}, HAS(@cell, "XXX"), {ActiveRequestStatus}, CONTAINS("ready", @cell)))) / (COUNT(COLLECT({Active#DaysOpen}, {ActiveSegment}, HAS(@cell, "XXX"), {ActiveRequestStatus}, CONTAINS("assign", @cell))) + COUNT(COLLECT({Active#DaysOpen}, {ActiveSegment}, HAS(@cell, "XXX"), {ActiveRequestStatus}, CONTAINS("ready", @cell))))

  • Christian G.
    Christian G. ✭✭✭✭✭✭
    Options

    @meb_0101 It is complex work. But you only do it once!

    Congratulation on finding the right syntaxt !

  • meb_0101
    meb_0101 ✭✭✭
    Options

    @Christian G. aha had to use the formula for 8 other cells but glad to have the base formula done! Thank you for helping me figure it out! :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!