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

Peppey

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!

Lucas Rayala

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

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

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

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

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

Lucas Rayala

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

Thank you so much sir! Worked fantastic.

Lucas Rayala

https://community.smartsheet.com/discussion/comment/416305#Comment_416305

Glad it worked!