average metric using multiple criteria in one range
I have a sheet with risk scores for various projects. A project may have multiple risks in the sheet, with a project number that relates to another sheet.
I want to create a metric that will show the average risk score for "project 1", "project 3", "project 4", "project 7" to use a simplistic example.
I have been experimenting with some suggestions I have seen for average formulas but nothing for this specific example of multiple criteria from one range.
Could someone help with this?
Answers
-
Are you wanting to average those four projects separately or get a combined average?
-
ideally i would like to be able to do both, but in the first place I am looking for a combined average
-
Try something like this...
=AVG(COLLECT({Score Column}, {Project Name Column}, OR(@cell = "Project 1", @cell = "Project 2", @cell = "Project 3", @cell = "Project 4")))
For just one project...
=AVG(COLLECT({Score Column}, {Project Name Column}, @cell = "Project 1"))
-
many thanks, this looks like some of what i have seen, but seems like it would solve my separating out the different projects. i wil let you know how it goes
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives