#### Ranking/Sorting more than 3 row's

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?
 0
Chris6982 (632)
2/2/2005 8:39:04 PM
excel.misc 78881 articles. 5 followers.

1 Replies
427 Views

Similar Articles

[PageSpeed] 41

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

>-----Original Message-----
>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?
>.
>
 0
jasonjmorin (551)
2/2/2005 9:17:56 PM

Similar Artilces:

Excell #3

Vlookup in vba
Hi I have the following snippet of code: Sheets("mdata").Select Range("E1").Select ActiveCell.FormulaR1C1 = "RVU" Range("E2").Select ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-1],rvu!R[-1]C[-4]:R[7238]C[-3],2)),0,VLOOKUP(RC[-1],rvu!R[-1]C[-4]:R[7238]C[-3],2))" Range("E2").Select Selection.AutoFill Destination:=Range("E2:E" & LastRow), Type:=xlFillDefault This fills about 5,000 rows with look up data from the rvu ws, and puts in 0 if #N/A is obtained. HOWEVER, if I was ...

Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next
Hi I am new here and have been snooping around some of the threads, yet not quite found the simple formula I expect to solve my problem. The closest matches seemed to advanced for my need, so I reckon and hope this will be an easy one for the more experienced members. What I'm looking for is a formula that will: Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a column with similar conditions in the next column. Say A2:A21 contains 9 "0"s and the remaining 11 are "1"s, of which only 5 (the "1"s) appear on the same ...

IF formula question #3
Ok. All I want to do is to say if Col A = "Yes" or Col B = "Yes" I want a Yes printed in Col C. I can't seem to figure this one out. Thanks for your assistance. In C1 =if(or(A1="Yes",B1="Yes"),"Yes","") "Mad Dog" wrote: > Ok. All I want to do is to say if Col A = "Yes" or Col B = "Yes" I want a > Yes printed in Col C. I can't seem to figure this one out. > > Thanks for your assistance. > > > Hi Try something like: =IF(OR(A2="Yes",B2="Yes&quo...

How to create chart with 1 column and 3 lines
IF This type of chart not in Standarad or Custom Types. THANK YOU Hi, Start by creating a standard column chart. Now select 1 of the column series that you want as a line. Right click and chose Chart Types. Select Line chart. That should have now changed the data series to a line. Select another column and this time press F4, which will repeat the last action. Repeat for the final series. Or maybe its easier to create a line chart of all 4 and the change 1 series to column using the same technique as above. Cheers Andy agenda9533 wrote: > IF > This type of chart not in Standara...

We're not able to install CRM, keep getting a SRS can't be found error. This is a dev environment. One box, 2 Virtual Servers Virtual Server 1 - CRM Virtual Server 2 - SQL and SRS The SQL has 2 instances, we're using the default instance. We can go to the \\reportserver, however, it prompts for credentials. I suspect when when CRM is installing SQL is prompting it for permissions. Anybody have similar issue with SRS? Thanks. SRS is very picky :( and if using SQL 2005 even more diligent about wanting exactly the right permissions.. One way to solve a more complex install...

how to get the row and col count of a TMGrid using sendmessage?
I have a 3rd party application which uses TMGrid, I want to get the row and col count of this TMGrid using SendMessage. The Parent Control is a TTabSheet. Anyone know how to do it? I tried the LVM_ message, but failed. Hi, try to find out what messages this control can handle, e.g. with Spy++. Just give it a try,... Regards Kerem -- ----------------------- Beste Gr�sse / Best regards / Votre bien devoue Kerem G�mr�kc� Microsoft Live Space: http://kerem-g.spaces.live.com/ Latest Open-Source Projects: http://entwicklung.junetz.de ----------------------- "This reply is provided as ...

need assistance with sorting a list of numbers
I've tried to achieve this problem with formulas and lookups and if statements and conditinal formatting however was not able to achieve desired results, so asking for help since I am not good with XL marcros..if someone can start me up I can navigate myself.. There is a list.. say of numbers and there is a possiblity that numbers will be repeats like: 12345 fail 12345 fail 12345 pass 11111 fail 11111 pass 22222 pass 33333 fail 33333 fail 44444 pass I want to read say A1 (numbers) disregard the repeats and put info A2 beside it 12345 fail fail pass 11111 fail pass 22222 pass 333...

Outlook 2000 won't start #3
My Outlook has stopped working. It was working fine, and then all of a sudden, it will not come up. I have uninstalled and reinstalled and it still will not work. When I click on it, I get the the box that says Microsoft Outlook on the screen, but that is where it gets stuck and it never actually loads. This was installed with Office, and all of the other programs that were installed with Office work just fine. What can I do to get this to work? On Wed, 20 Apr 2005 15:00:01 -1000, Ole Lang Signs"" <Ole Lang <Signs@discussions.microsoft.com>> wrote: > My O...

Prevent row from being deleted
Hi all I have a form with a subform on it. The subform is displayed in Datasheet view. If a user right clicks on a row it allows for the user to insert a new record or deleted the current record. Is there a way to prevent the user from deleting the record? Thanks This is a multi-part message in MIME format. ------=_NextPart_000_013B_01C87499.8575B040 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Hi Murray,=20 Sure - set the sub-form's property 'allow deletions' (in design mode, = property sheet) to false.=20 Gordon &quo...

change page setup from printing 1 business card to 2 rows of 5
the program wont let me make this simple change. all i want to do is set up the page so that it prints the card in two rows of 5 but there is no way to change it. Oh but there is, give this a try. Here's I do it with Publisher 2000. File Page Setup Special Size Business Card from the drop down menu OK Create card. Assuming you are using standard business card stock of 2 wide and 5 high; File Print Page options button Print multiple copies per sheet Custom options button Margins = .5" Gap = 0" Side Margins =...

Auto Number #3
In my spreadsheet I have a cell called file number. Is their anyway that a can get this cell to automatically put in the next number everytime I enter new data, so that I do not have to type in the number it is excel 2000 If your numbers are in column A, maybe you could just put a bunch of formulas in that column that depend on another column having data. =if(b10="","",a9+1) (in A10, and drag down.) horseman wrote: > > In my spreadsheet I have a cell called file number. Is their anyway that a > can get this cell to automatically put in the next number everyt...

VLookup question #3
I've tried searching for an answer to my question, but with no luck. I'm working on a sheet to compare to lists of numbers, using this formula: =VLOOKUP(C6,A4:B392,1,TRUE) I've tried to use a constant in the formula, but it doesn't work. Assuming that {} defines a constant, why can't I do this? =VLOOKUP(C6,{A4:B392},1,TRUE) so the array I need to search is constant? Peter, You need absolute cell references (\$A\$4:\$B\$392), not relative (A4:B392). The F4 key will switch this, if the reference is selected while you're in Enter or Edit mode. =VLOOKUP(C6,\$A\$4:\$B\$39...

Sort on Tab Change
Is there a way to trigger sorting of a column, when I change tabs? I have a dataset that I use for input and a different tab which is a leaderboard. I want to sort the leaderboard by high score every time I change to that tab. I currently have a macro button I click to sort by high score, then I have to click the leadboard tab. I'd obviously like to click the leaderboard tab and have the data sorted by high score. Just attempting to combine 2 steps. step 1 - Sort input form by highscore step 2 - open the leaderboard tab Thanks, Jeff right-click the ...

Formatting problem #3
Hi, In thspreadsheet, I'm able to format only one cell but not other cell besides this cell. Could anyone tell me how I could fix this problem. Thanks. Rajan -- Message posted from http://www.ExcelForum.com Hi Rajani More imformation required. Can you select other cells?, the worksheet may be protected. Bob C. >-----Original Message----- >Hi, >In thspreadsheet, I'm able to format only one cell but not other cells >besides this cell. Could anyone tell me how I could fix this problem. >Thanks. >Rajani > > >--- >Message posted from http://www.ExcelFor...

Sheet 1 complete list of names all pupils and fees per month Sheet 2/sheet per instructor - list of pupils per instructor with names and fees linked from sheet 1 Problem. If we add new names to sheet 1 then sort it alphabetically the rows change so sheets 2s values get messed up. One way is to use VLOOKUP in Sheet2 for the Fees Assume in Sheet1, names are in col A, Fees in col B data from row2 down In Sheet2, names are listed in A2 down, Fees to be extracted in B2 down Put in B2: =IF(A2="","",VLOOKUP(A2,Sheet1!A:B,2,0)) Copy B2 down as far as required -- Rgds Max xl ...

Hi, I can logon onto the CRM app from a browser after supplying the password to a SERVERNAME\username request. The problem is with Outlook, because this request cannot be satisfied the CRM COM is not running. Does anyone know how I can eliminate the need to supply a password when I connect to the CRM app, I tried to turn on anonymous access but I could not log on at all then just said the logon had failed and did I want to try again. Thanks. We ran into something similar. The CRM server was running on an IP address other than the default IP for the NIC (in other words a second IP was bou...

Macro for Graph #3
For the following table, I have written the macro to obtain the graph But the problem is the no. of column and rows can change... So I don want to hard code particularly the first part of macro... Please help. Keyword Date Total Of Value XYZ TUV RST ABC 27/10/2003 7 7 11 100 ABC 27/11/2003 7 3 5 15 ABC 27/12/2003 7 10 200 25 Sub Graph() Charts.Add ActiveChart.ApplyCustomType ChartType:=xlBuiltIn TypeName:="Logarithmic" ActiveChart.SetSourceDat Source:=Sheets("Keyword_Analysis").Range("B2:F4"), PlotBy:=xlColumns ActiveChart.SeriesCollection(1).Delete ActiveC...

When clikcing on the Reply to All button I receive the same e-mail. Others I work with can do the same thing but instead do not receive the e-mail. What do I need to check or where do I need to go to stop this from happening? To clearify the issue: When replying to any email I use the reply to all button in outlook 2000. After clicking the button the reply message appears with the To: row filled with the appropriate people. The problem is that my name shows up in the field as well. I am trying to eliminate my email address from becoming one of the reply addresses. I know that ...

Row Selection for deleting
Hi All I have picked up some great code and assistance from this group. Thanks to all the contributors. I have code finding a particular criteria I want to reamove from a spreadsheet but I also want to delet the next 3 rows after the instance. Can someone add the correct code to the row with Apples in it followed by the next 3. Application.ScreenUpdating = False Do Set rng = Columns(7).Find("Apples") If rng Is Nothing Then Exit Do rng.EntireRow.delete Loop Thanks once again. rng.EntireRow.delete becomes rng.resize(4,1).EntireRow.delete Murph wrote: > > Hi All &g...

Row Re-numbering
Does anyone know if you are able to re-number the rows in Excel 2000? I am refering to the numbers generated by excel on the extreme left of the spreadsheet. Some how the numbering got out of sequence. Does anyone know how the rows get out of sequence? When I attempt to delete rows to test if the numbering changes, the rows renumber automatically. Based on my test, I'm not sure how the rows numbers got out of sequence in the first place. Thanks. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from...

Condtional Formatting
My conditional formatting applies to cells (see row numbers): =\$B\$41 =\$B\$42 When I insert 10 rows between row 41 and 42, the condition now applies to cells as follows: =\$B\$41:\$B\$51 1. Is there a way that the condtions will not be copied to rows 42 to 51? Or, the condition will apply only to each cell, i.e." =\$B\$41 =\$B\$42 =\$B\$43 etc. to =\$B\$51 Thank you. Use the absolute column reference of \$B and the relative row reference of 41 to get the seconf optiont. =\$B41 To not have the CF copied to the inserted rows, turn off the option to "exte...

Email Sorting
I have a column with email addresses. I'd like them grouped and sorted by email address. test@domain.com smith@help.com test2@domain.com alpha@help.com yahoo@yahoo.com would become.. test@domain.com test2@domain.com alpha@help.com smith@help.com yahoo@yahoo.com Thanks! One way: Copy addresses to an unused column. Choose Data/Text to Columns. Select Delimited, Click Next, check Other and enter @, click Next. Select the first column and choose the "Do not import column radio button". Click Finish. Sort on the column of domains. In article <1171379816.938911.73700@k78...

Outlook XP Contact Sort
Transferred .pst file to new computer. Contacts sorted properly (last, first) in "Contacts" view, however when trying to send e-mail using contact list, the list shown is sorted first, last. I know that the Tools>Options>Contact Options" routine is only supposed to "fix" new entries, but how do I correct existing entries? TIA. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.688 / Virus Database: 449 - Release Date: 5/18/2004 Willy <dehinten@yahoo.com> wrote: > Transferred .pst ...

Display Knowledge Base object in Microsoft CRM 3.0 Outlook Client
I am trying to display the KB object in CRM according to the following article: http://www.workopia.com/howto/DisplayKnowledgeBaseOutlookClient.htm I found another note to the above link that states there are two entries that needed to be modified and I made them both. But have still had no luck. Does anyone know how to make this work? TIA Larry ...