Average between time frames. Formula error using AVG(Collect)

Hi again everyone!

I am hoping someone can assist me. I tried a formula @leonastomenio sent me awhile back, which worked perfectly, but now I have another situation that this formula does not work. I am trying to average the Days it took to complete an action if the cells are not blank, and if the Date Last Action Taken is in the current month and year. The formula below is giving me an invalid operation error.

=IF({Hire Time} <> "", AVG(COLLECT({Hire Time}, {Last Action Date} = MONTH(TODAY()), {Last Action Date} = YEAR(TODAY()), "")))

The Hire Time reference: Column I am trying to average if not blank

The Last Action Date reference: Date column that I only want to average the Hire Time if not blank and in this current month and year

Any help would be greatly appreciated!

Best Answer

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    Answer ✓

    Ha ha. I was working too late at night @Peppey -- there are a few big mistakes in the above -- try this one instead.

    =AVG(COLLECT({Hire Time}, {Hire Time}, <>" ", {Last Action Date}, >=DATE(YEAR(TODAY()), MONTH(TODAY()), 1), {Last Action Date}, <=(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1)))

Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭

    Hi @Peppey , you want the new criteria inside the COLLECT function. Each criteria in a COLLECT function is essentially another IF statement. So basically:

    =AVG(COLLECT ({Hire Time}, {Hire Time}, <>””, ETC

    with etc being the rest of the previous formula. Hope that helps!

  • Peppey
    Peppey ✭✭

    Hi @Lucas Rayala

    Thank you so much for your help. I tried the formula you provided and added the rest of the formula, but I am sure I am screwing it up. I have the below formula but it is giving me an UNPARSEABLE error.

    =AVG(COLLECT ({Hire Time}, {Hire Time}, <>" ", AVG(COLLECT({Hire Time}, {Last Action Date} = MONTH(TODAY()), {Last Action Date} = YEAR(TODAY()), "")))

    Can you assist me in fixing it please?

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    edited 01/29/24

    Hey @Peppey sorry I assumed part of that formula was already working and you were just trying to add the <>””. If you have a working formula where that is the case, please paste the working formula. Try this though:

    =AVG(COLLECT ({Hire Time}, {Hire Time}, <>" ", {Last Action Date}, >=DATE(YEAR(TODAY()), MONTH(TODAY ()),1), {Last Action Date}, <=(IFERROR(DATE(YEAR([Date Column]@row), MONTH([Date Column]@row) + 1, 1), DATE(YEAR([Date Column]@row) + 1, 1, 1)) - 1)))

    I think I fixed the above to include the last Day of the month. I’ll need to try this on my computer tomorrow. I’m just typing on my phone now.

  • Peppey
    Peppey ✭✭

    Yes sir thank you so much. I will try it later as well. Thank you so much for your help.

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    Answer ✓

    Ha ha. I was working too late at night @Peppey -- there are a few big mistakes in the above -- try this one instead.

    =AVG(COLLECT({Hire Time}, {Hire Time}, <>" ", {Last Action Date}, >=DATE(YEAR(TODAY()), MONTH(TODAY()), 1), {Last Action Date}, <=(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1)))

  • Peppey
    Peppey ✭✭

    Thank you so much sir! Worked fantastic.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!