Today I am going to show you how to sort a group of scores by their average using Excel functions and NOT a pivot table. Pivot tables are great but they can be intimidating. Excel functions can help you get around them sometimes.
Let’s say we have this data:
We have a group of people and their scores at some imaginary game. We want to keep this table in tact but create a new table of people showing their average score and sorted descending.
So we first need to create the unique list of names. To do this, copy the list of names over to the right. Then with your cursor on this list of names, go to Data on the ribbon and over to Remove Duplicates.
Once you click ok, it will remove all duplicates and leave you with only unique names.
Next, we need to get the average for each name. To do this we use the AVERAGEIF function. This will check the names in column A and average them out by that name.
Let’s go to cell G2 and enter in =AVERAGEIF(A2:A19,F2,B2:B19)
Let’s break this down:
The first part A2:A19 is the range it is checking for the name.
The second part, F2 is what we are looking for in that range. In this example, it is Jeff.
The last part is B2:B19. This is the range of numbers that it will average out if it finds the word Jeff.
You have to make sure the rows are the same in both. That means, since the first part checks rows 2 to 19, the last part MUST also use rows 2 to 19. When it finds a match in Column A, it takes all those corresponding values in B and averages them.
Before we copy this down, we need to be sure to anchor those ranges, so they don’t change when you copy them down. You can do that by putting your cursor on each part of the range and pressing F4.
So now when we copy it down, we will have this:
So all that’s left to do is sort them. Just put your cursor on any of the numbers and go up to Data and then Sort Descending.
You will now see that they are all sorted with the largest value on top.