average metric using multiple criteria in one range

Rob Wright

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?

Paul Newcome

Are you wanting to average those four projects separately or get a combined average?

Rob Wright

ideally i would like to be able to do both, but in the first place I am looking for a combined average

Paul Newcome

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"))

Rob Wright

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