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

[pete6032]

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]

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]

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:

=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).

 

[pete6032]

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:

=Sort(A1:D32,32)

That would create a new table starting at cell A34 sorted by row 32.

Excellent thank you!

 

[dullard]

Excellent thank you!

Doh! I missed your descending order request. I've edited it. But, glad to help.

 

[igor_kavinski]

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]

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]

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.