Excell

%3CLINGO-SUB%20id%3D%22lingo-sub-354990%22%20slang%3D%22en-US%22%3EExcell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354990%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20does%20anyone%20know%20how%20to%20get%20the%20average%20of%20multiple%20rows%20in%20order%20from%20largest%20to%20smallest%3F%20For%20example%E2%80%A6Our%20employees%20have%203%20different%26nbsp%3Bcategories%20they%20get%20ranked%20In%26nbsp%3B%26amp%3B%20we%20want%20Excel%20to%20categorize%26nbsp%3Ball%20the%20employees%20from%26nbsp%3Bhighest%20numbers%20to%20smallest%20with%203%20categories'%20at%20a%20time%20not%20just%20one..%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture.PNG%22%20style%3D%22width%3A%20492px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F83784i833BBEF958AF6EC4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Capture.PNG%22%20alt%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-354990%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1556969%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1556969%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F287781%22%20target%3D%22_blank%22%3E%40Mackenzieparker%3C%2FA%3E%26nbsp%3Bpara%20uso%20de%20empresa%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-355730%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-355730%22%20slang%3D%22en-US%22%3EGiven%20that%20there%20are%203%20categories%20from%20which%20employees%20will%20be%20ranked%2C%20please%20clarify%20the%20following%3A%3CBR%20%2F%3E1.%20Is%20the%20highest%20value%20for%20each%20category%20considered%20as%20Rank%201%20for%20that%20category%3F%20and%3CBR%20%2F%3E2.%20Are%20each%20category%20assigned%20the%20same%20weighting%20factor%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-355004%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-355004%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20not%20clear%20what%20do%20you%20mean.%20You%20may%20sort%20by%203%20columns%2C%20one%20level%20within%20another%20(which%20ones%3F).%20You%20may%20generated%20some%20weighted%20rank%20based%20on%203%20cells%20in%20each%20row%20(what%20are%20weights%3F).%20Perhaps%20something%20else.%20What%20is%20average%20for%20rows%20isn't%20clear%20as%20well.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20may%20to%20illustrate%20by%20screenshort%20with%20desired%20after%20sorting%20of%20your%20source%20data%20result.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1557140%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1557140%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F287781%22%20target%3D%22_blank%22%3E%40Mackenzieparker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20workbook%20that%20will%20give%20you%20the%20option%20of%20assigning%20relative%20weights%20to%20the%20different%20columns%2C%20then%20will%20produce%20a%20single%20weighted%20score%20for%20each%20individual.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20%22Sorted%22%20tab%20then%20shows%20the%20names%20(with%20their%20scores)%20sorted%20in%20descending%20order%20by%20the%20weighted%20scores.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20does%20require%20the%20use%20of%20the%20SORT%20function%20which%20is%20only%20available%20in%20the%20newest%20Excel%20versions.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1557154%22%20slang%3D%22en-US%22%3ERe%3A%20Excell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1557154%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F287781%22%20target%3D%22_blank%22%3E%40Mackenzieparker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBy%20the%20way%2C%20if%20it's%20really%20average%20of%20each%20row%2C%20and%20then%20sorted%2C%20that%20you%20want%2C%20all%20you'd%20need%20to%20do%20with%20that%20workbook%20I%20sent%20is%20change%20the%20formula%20under%20the%20Weighted%20heading%20to%20read%20%3DAVERAGE(B2%3AD2)%20and%20copy%20that%20down.%20The%20SORT%20function%20on%20the%20next%20page%20would%20still%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20that%20doesn't%20give%20you%20a%20way%20to%20weigh%20the%20relative%20importance%20of%20the%20three%20columns.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello, does anyone know how to get the average of multiple rows in order from largest to smallest? For example…Our employees have 3 different categories they get ranked In & we want Excel to categorize all the employees from highest numbers to smallest with 3 categories' at a time not just one..

Capture.PNG

5 Replies
Highlighted

Hi,

 

That's not clear what do you mean. You may sort by 3 columns, one level within another (which ones?). You may generated some weighted rank based on 3 cells in each row (what are weights?). Perhaps something else. What is average for rows isn't clear as well.

 

Perhaps you may to illustrate by screenshort with desired after sorting of your source data result.

Highlighted
Given that there are 3 categories from which employees will be ranked, please clarify the following:
1. Is the highest value for each category considered as Rank 1 for that category? and
2. Are each category assigned the same weighting factor?
Highlighted

@Mackenzieparker para uso de empresa

Highlighted

@Mackenzieparker 

 

Here's a workbook that will give you the option of assigning relative weights to the different columns, then will produce a single weighted score for each individual.

 

The "Sorted" tab then shows the names (with their scores) sorted in descending order by the weighted scores.

 

It does require the use of the SORT function which is only available in the newest Excel versions.

Highlighted

@Mackenzieparker 

 

By the way, if it's really average of each row, and then sorted, that you want, all you'd need to do with that workbook I sent is change the formula under the Weighted heading to read =AVERAGE(B2:D2) and copy that down. The SORT function on the next page would still work.

 

However, that doesn't give you a way to weigh the relative importance of the three columns.