# Excel help - auto sort the average of cells in descending order?

#### pete6032

##### Diamond Member
I'm distributing a survey at work. We have about 30 projects we've completed and I'm asking people to score them from 1 (bad project) to 10 (good project). What I have is the name of the project in cells A2:A31, and then in B1, C1, D1, I have my coworkers names (Bob, Jane, Mary). I want them to put a 1-10 score in each cell. After that I would like Excel to take the average of each person's score across the project, and create a list in descending order to show that the top scoring project was x, with an average score of 9.5, followed by project y, with an average of 9.1, etc etc.

I know I can achieve this sorting manually but is there a way to make Excel do the sorting automatically after every new data point entry?

#### akenbennu

##### Senior member
You should be able to use sumif() and countif() functions to split all the scores by user and get an average, then conditional formatting to show the highest score per person.

#### dullard

##### Elite Member
Use the =Sort() function.

SORT(array, [sort_index], [sort_order], [by_col])

If I read your post correctly, it sounds like you'd put an average in row 32.
• Cell B32 would average all of Bob's scores from B2 through B31.
• Cell C32 would average all of Jane's scores from C2 through C31.
• etc.
Then in cell A34 type:
Code:
``=Sort(A1:D32,32,-1)``

That would create a new table starting at cell A34 of your data (A1 to D32), sorted by row (32), using descending order (-1).

Last edited:

#### pete6032

##### Diamond Member
Use the =Sort() function.

SORT(array, [sort_index], [sort_order], [by_col])

If I read your post correctly, it sounds like you'd put an average in row 32.
• Cell B32 would average all of Bob's scores from B2 through B31.
• Cell C32 would average all of Jane's scores from C2 through C31.
• etc.
Then in cell A34 type:
Code:
``=Sort(A1:D32,32)``

That would create a new table starting at cell A34 sorted by row 32.
Excellent thank you!

dullard

#### dullard

##### Elite Member
Excellent thank you!
Doh! I missed your descending order request. I've edited it. But, glad to help.

igor_kavinski

#### igor_kavinski

##### Lifer
That would create a new table starting at cell A34 of your data (A1 to D32), sorted by row (32), using descending order (-1).
It's weird they used -1 for descending. 0 and 1 weren't enough for specifying ascending and descending or am I missing something here?

#### dullard

##### Elite Member
It's weird they used -1 for descending. 0 and 1 weren't enough for specifying ascending and descending or am I missing something here?
0 and 1 would be enough (and would be helpful for using TRUE / FALSE there).

But to me using a negative number for descending and a positive number for ascending makes perfect sense for most people.

igor_kavinski

#### igor_kavinski

##### Lifer
But to me using a negative number for descending and a positive number for ascending makes perfect sense for most people.
Ah. I get it. They went for opposites. 1 and -1. Cool.