Formula to Average Performance Score for Various Service Categories

ullkay95

I am trying to create a formula that averages the performance score of various service categories.

For example, whenever the service category (drop down box) has "civil engineer" selected, I want a running formula that averages all the civil engineer ratings. I have tried using the =averageif() formula, but I continue to get an error.

I would like the formula to be on the bottom row:

Paul Newcome

In that case you would use the same syntax but you would reference the column in the sheet using the appropriate column name.

[Column name]:[Column name]

Paul Newcome

Can you share a screenshot of where you are wanting to put this formula?

ullkay95

https://community.smartsheet.com/discussion/comment/388040#Comment_388040

Hi Paul - I rewrote my original question as I realized that wasn't the formula i was looking for. I also included a screenshot of where I would like the formula to calculate. Thanks!

Paul Newcome

I'm still not sure I follow. You want to enter (for example) "Civil Engineer" in the bottom blue row of the second screenshot and have it display averages, but the source data doesn't look like it has all of the categories broken out. Just an overall Performance Score?

ullkay95

https://community.smartsheet.com/discussion/comment/388044#Comment_388044

The "Performance Score" column averages out the above ratings of our a vendor performed. The "Service Category" will have various vendors such as: Civil Engineer, Architect, Traffic Consultant, etc. For now, we only have a rating for a Civil Engineering firm (this is a form that our project manager will use to rate our consultants).

The bottom blue row averages out the overall scoring for services provided no matter their service category. Underneath that, I want to average the performance score for the service categories:

Service Category Average Performance Score

Civil Engineer 4.8

Architect 3.5

Paul Newcome

An AVERAGEIF should be the way to go then.

=AVERAGEIF({Source Sheet Vendor Column}, @cell = "Civil Engineer", {Source Sheet Schedule Column})

ullkay95

I am getting an invalid reference when following your formula:

=AVERAGEIF({service category}, [Service Category]22, {performance score}

Paul Newcome

#INVALID REF means you did not create the cross sheet reference(s). Is this formula going on the same sheet as the source data or a different sheet?

ullkay95

Same sheet as the source data is preferred.

Paul Newcome

In that case you would use the same syntax but you would reference the column in the sheet using the appropriate column name.

[Column name]:[Column name]

ullkay95

That worked! What finally did the trick was the ":" between the []:[].

Paul Newcome

Happy to help. 👍️