Averageifs Question
Hello!
I am wanting to do an averageifs statement, but it does not seem that is supported. Is there another way that I can calculate an average based off fitting two different criteria?
Right now, I have this equation, which works for when just hitting the one criteria:
=ROUND(AVERAGEIF({System Request}, [Ticket Type 2]1, {Duration}), 2)
However, I also want to break down the average by month, which will add another average criteria.
I know that I can, if needed, create reports that break down the above criteria so I can just use the averageif based of months, but I would prefer to be able to just calculate this through an equation.
Best Answer
-
You will want to use an AVG(COLLECT(...................))
=AVG(COLLECT(range_to_be_averaged, first_criteria_range, first_criteria, second_criteria_range, second_criteria, third_criteria_range, third_criteria, .............................))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
You will want to use an AVG(COLLECT(...................))
=AVG(COLLECT(range_to_be_averaged, first_criteria_range, first_criteria, second_criteria_range, second_criteria, third_criteria_range, third_criteria, .............................))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you so much! That worked perfectly.
-
Happy to help! 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Paul....Thank you so much...It worked like a charm...
Just out of curiosity...what exactly does Collect do?
-
@rwilson29376 The COLLECT function does exactly that. It collects a range of cells based on the criteria. It is used within another function to tell the main function what to pull from. It allows for A TON of flexibility and adaptability for a wide range of uses since it can be nested inside of almost any other function (other than another COLLECT function).
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul,
I am trying to use AVG(Collect) to average a column based on three months listed in another column. I keep getting incorrect argument set. Can you tell me where I am going wrong?
=AVG(COLLECT([Reception Desk Staffing]:[Reception Desk Staffing], Month:Month, "January", [Reception Desk Staffing]:[Reception Desk Staffing], Month:Month, "February", [Reception Desk Staffing]:[Reception Desk Staffing], Month:Month, "March"))
Thank you!
-
Try this instead...
=AVG(COLLECT([Reception Desk Staffing]:[Reception Desk Staffing], Month:Month, OR(@cell = "January", @cell = "February", @cell = "March")))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you so much Paul! This worked perfectly!!
-
@Megan Harry Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thks Paul! I’ve found essential answers thanks to you!
-
Hi! One question with the AVERAGEIF(S) fonction: I need to calculate an average when two criteria are met, but these criteria are in two separate columns.
I tried this but failed: =AVERAGEIF(range1; criteria 1; range 2; criteria 2; average range)
ANY IDEA?? Pleeeeeeeeease!
-
FOr multiple range/criteria sets you will need to use the AVG/COLLECT.
=AVG(COLLECT(range to average, criteria range 1, criteria 1, criteria range 2, criteria 2))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you so much, @Paul Newcome ! You are so helpful. I was looking for "averageifs" and found your AVG(Collect....) solution.
I needed to average turnaround time for the fiscal year. This is my formula, in case it helps someone else:
=AVG(COLLECT({Total Turnaround Time}, {Date of Issue}, >=DATE(2020, 4, 1), {Date of Issue}, <=(DATE(2021, 3, 31))))
I was missing the comma between the criteria range and criteria. Once I recognized that, it worked perfectly.
-
Hello, I am thinking this is the solution that I need, but I can't seem to get it right. Trying to average numeric survey results in a column titled "Supply Vendor Performance" and limit response to only ones with drop down selected in "Supply Vendor"
Here is what I have so far:
=AVG(COLLECT(Supply Vendor Performance]:[Supply Vendor Performance], [Supply Vendor]:[Supply Vendor], "ACME"))
-
It looks like you're just missing a square parentheses at the beginning of your formula, around the column name [Supply Vendor Performance]... try this:
=AVG(COLLECT([Supply Vendor Performance]:[Supply Vendor Performance], [Supply Vendor]:[Supply Vendor], "ACME"))
Cheers!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!