Excel: Finding averages based on a specified criterion

One of the many things I do in my capacity as a union representative is to assemble my own statistics based on raw numbers I get from my employer. One of the stats I calculate is the average pay in each section. As you might remember, last week I assembled a list of forty seven unique codes.

This time, I’m going to use that list of unique codes to find average amounts for each code. To do this, I used the AVERAGEIF-function. The format is as follows:

  • Range to lookup
  • Criterion for evaluation
  • Range to calculate average

In the example below, my range to lookup is in column A, the criterion for evaluation (in this case the individual unique value found in the overall list) is in column C, and the range from which the average is to be calculated is in column B. For the criterion for evaluation, I specify a single cell. This gives me a formula that looks like this: =AVERAGEIF(A:A,C2,B:B).

I copy and paste this to the individual cells for all forty seven entries, giving me a list that looks like this:







By posting a comment, you consent to our collecting the information you enter. See privacy policy for more information.

This site uses Akismet to reduce spam. Learn how your comment data is processed.