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
-
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
-
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!
-
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?
-
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.
-
Yes sir thank you so much. I will try it later as well. Thank you so much for your help.
-
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)))
-
Thank you so much sir! Worked fantastic.
-
Glad it worked!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
