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, .............................))
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, .............................))
-
Thank you so much! That worked perfectly.
-
Happy to help! 👍️
-
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).
-
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")))
-
Thank you so much Paul! This worked perfectly!!
-
@Megan Harry Happy to help. 👍️
-
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))
-
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
Help Article Resources
Categories
Check out the Formula Handbook template!
