Ranking/Sorting more than 3 row's

C

chris

I have a spreadsheet with 742 rows (of company names).
I have 5 columns of criteria for the companies.
Each column is a different criteria about each company, and has a numerical
rank for each company. 1-~75 (some columns/criteria have no rank for a
company)

I want to create an overall rank of the companies based on how they perform
across the 5 rows. I want the companies with the most 1's at the top and then
those with 1 & 2's and then those with 2 & 2's etc.

Is there a way to sort or rank this spreadsheet of companies?
 
J

Jason Morin

I would do a sum of the inverses of each of the numerical
rankings for a company. If a company is missing a ranking
in a column, that ranking = 0. For example, with company
names in col. A, and the 5 numerical rankings in columns
B through F, put this in G1 and press ctrl/shift/enter:

=SUM((IF(B1:F1<>"",1,0))/(IF(B1:F1<>"",B1:F1,1)))

Now copy the formula down to row 742. The higher the
number in col. G, the better the overall company ranking.
A perfect score is 5 (ie the company received a 1 in each
of the 5 categories).

HTH
Jason
Atlanta, GA
 
Top