Avg. Amount Based on Several Criteria
Hello,
I am trying to write a formula that tells me the average repair amount based on several criteria in different columns on the sheet.
The columns are listed below:
Location Column
Repair Category Column
Repair Amount Column
Basically, if the location is "Location A" and the repair category is "Category 2" then what is the average repair amount in the Repair Amount column.
I've tried several attempts of countifs and contains formulas with no luck. I've used those successfully in the past for basic counts but I've never had to attempt an average as well.
I hope that makes sense! Thank you in advance!
-Nick
Best Answer
-
You can use a CONTAINS function like so:
=AVG(COLLECT({Repair Amount Range 7}, {Location Range 1}, "AB Calgary", {Repair Category Range 5}, CONTAINS("PDR", @cell)))
Answers
-
Hello Nick,
Try the following:
=AVG(COLLECT([Repair Amount]:[Repair Amount], Location:Location, "Location A", [Repair Category]:[Repair Category], "Category 2"))
-
Hi Shimanta,
Thanks for the quick reply! I tried that formula and and getting a "#divide by zero" error message.
Here's my formula:
=AVG(COLLECT({Repair Amount Range 7}, {Location Range 1}, "AB Calgary", {Repair Category Range 5}, "PDR"))
The columns I'm referencing are all on a different sheet.
Thanks again,
Nick
-
That particular error is indicating that you are not pulling in any matching rows. Just for some trouble shooting... What happens when you enter
=COUNTIFS({Location Range 1}, "AB Calgary", {Repair Category Range 5}, "PDR")
-
Hi Paul,
I think the issue was that I wasn't typing the exact repair type. So it was not finding a match. I typed the exact repair type and it worked!
However, now my issue is that there are two types of "PDR" repair types that I'm trying to average together. That was why I originally used just "PDR" in my formula hoping it would pick up anything with PDR in the type. That didn't seem to work.
So now I'm wondering how I can average both of the PDR repair types together (PDR Major and PDR Minor).
Thanks for your help!
-
You can use a CONTAINS function like so:
=AVG(COLLECT({Repair Amount Range 7}, {Location Range 1}, "AB Calgary", {Repair Category Range 5}, CONTAINS("PDR", @cell)))
-
That worked! Thank you for your help!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 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!