Trying to apply an AVERAGE IF formula

Shelby.Kriesel

I am running the Employee Pulse Check template for a project survey for our installer team.

In a nutshell, form submitters will use a drop down to select which installer they had for the project, and then rate them on 5 questions.

I need to average out the ratings for the 5 questions, per installer.

Columns I would like to use in the AVERAGEIF formula in the "Installer Average Rating" column are those in gray.

I have a pretty substantial drop down list for calling out which installer was used:

I understand I will need a part of the formula dedicated to each installer name, but each time I try I am getting an #UNPARSABLE error and can't figure out where I am going wrong.

=AVERAGEIF(Provision Installer:Provision Installer, "Joe C.", [Converted Install - Quality]:[Converted Install - Quality]) and so on with each installer name and column to be averaged.

Thanks for your help!

SoS | Dan Palenchar

Hello @Shelby Kriesel,

The issue with your formula is that column names that are more than one word need brackets around them (i.e., Provision Installer needs to be written as [Provision Installer], so change it to this:

=AVERAGEIF([Provision Installer]:[Provision Installer], "Joe C.", [Converted Install - Quality]:[Converted Install - Quality]) and so on with each installer name and column to be averaged.

On another note - Is your end goal to get an average rating for each installer? If so, I would create a separate Metrics Sheet that lists the name of each installer and then references the Employee Pulse Check Sheet. I suspect (but am not sure, since I am not a mathematician) that you would want to sum the individual columns and then create an average off that.

Imagine the Metrics Sheet has a column named "Installer" with the names of each installer as they are available in the Employee Pulse Check Sheet.

I would create columns in the Metric Sheet for each question and then add

=SUMIF({Cross Sheet Reference to Column in Employee Pulse Check}, installer@row)

Then, you could make an Average column and average each of these columns and you have your data.