Solved

Excel - Score or Rank

Posted on 2016-09-04
3
136 Views
Last Modified: 2016-09-05
Hi Experts

I have a data set which has got Two Columns, named - Product and Value % Change, respectively.

I am looking for some DIFFERENT METHODS which could be used for Ranking this data from 1 to 100, in such a manner that the Highest Positive Value gets the Highest Score i.e. 100 and the Lowest Value " it would be negative value in this data set" should get the Lowest Score of 0.

Can you please provide me some ideas in this regards. What could be various methods that could be used for such ranking purpose.

Any ideas are most welcome. If you could provide any links where such methods are described, then that would be a great help too.

I am using the following software versions -
Microsoft SQL Server Management Studio version-  12.0.2000.8,
Microsoft Office 2016 x64
and Windows 7 x64

I have attached the Excel File having the data.
Excel---Rank-or-Score.xlsx

Thanks
0
Comment
Question by:happy 1001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 250 total points
ID: 41784167
What about the RANK function...

    =RANK(D2, D$2:D$231,1)

...where D2 is the value and D$2:D$231 contains the range of values to rank. The "1" indicates that the highest number on the range is ranked the highest. This assigns a number of 1 to n, where n is the number of points being ranked. If you want a rank between 1 and 100, use this formula...

    =INT(RANK(D2, D$2:D$231,1)/COUNT(D$2:D$231)*100)

...but note that you will end up with different numbers with the same rank when you are ranking >100 values.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 41784253
Using a SQL Server query there are several opportunities:

rank() over()
dense_rank() over()
ntile() over()

But I think you want NTILE(100) OVER()

select
      Product, Value_Change,ntiled_100 
from (
     select
            Product, Value_Change
          , ntile(100) over(order by Value_Change) as ntiled_100
     from YourTable
     ) d
order by ntiled_100 DESC, Product, Value_Change

Open in new window

Example Result
Messages
Product Value_Change ntiled_100 
------- ------------ ---------- 
A191    19           100        
A37     6            100        
A119    4            99         
A80     6            99         
A48     4            98         
A83     4            98         
A158    4            97         
A196    4            97         
A155    3            96         
A174    3            96         
A179    3            95         
A6      3            95         
A20     3            94         
A29     3            94         
A55     3            93         
A71     3            93         
A51     2            92         
A52     2            92         
A41     2            91         
A59     2            91         
A24     2            90         
A73     2            90         
A126    2            89         
A97     2            89         
A113    2            88         
A114    2            88         
A178    2            87         
A184    2            87         
A169    2            86         
A203    2            86         
A197    1            85         
A201    1            85         
A187    1            84         
A195    1            84         
A163    1            83         
A210    1            83         
A150    1            82         
A162    1            82         
A156    1            81         
A170    1            81         
A172    1            80         
A227    1            80         
A120    1            79         
A133    1            79         
A118    1            78         
A124    1            78         
A106    1            77         
A109    1            77         
A105    1            76         
A79     1            76         
A1      1            75         
A15     1            75         
A27     1            74         
A69     1            74         
A39     1            73         
A58     1            73         
A45     1            72         
A50     0            72         
A49     0            71         
A54     0            71         
A43     0            70         
A44     0            70         
A38     0            69         
A42     0            69         
A64     0            68         
A65     0            68         
A62     0            67         
A63     0            67         
A60     0            66         
A61     0            66         
A56     0            65         
A57     0            65         
A23     0            64         
A72     0            64         
A21     0            63         
A22     0            63         
A25     0            62         
A30     0            62         
A33     0            61         
A36     0            61         
A10     0            60         
A7      0            60         
A13     0            59         
A9      0            59         
A12     0            58         
A19     0            58         
A18     0            57         
A89     0            57         
A46     0            56         
A91     0            56         
A81     0            55         
A82     0            55         
A74     0            54         
A75     0            54         
A104    0            53         
A78     0            53         
A102    0            52         
A103    0            52         
A108    0            51         
A96     0            51         
A107    0            50         
A93     0            50         
A115    0            49         
A125    0            49         
A123    0            48         
A98     0            48         
A121    0            47         
A122    0            47         
A111    0            46         
A112    0            46         
A110    0            45         
A132    0            45         
A130    0            44         
A131    0            44         
A128    0            43         
A129    0            43         
A127    0            42         
A138    0            42         
A136    0            41         
A137    0            41         
A143    0            40         
A144    0            40         
A140    0            39         
A142    0            39         
A225    0            38         
A226    0            38         
A229    0            37         
A230    0            37         
A176    0            36         
A177    0            36         
A173    0            35         
A175    0            35         
A149    0            34         
A171    0            34         
A148    0            33         
A154    0            33         
A152    0            32         
A153    0            32         
A157    0            31         
A160    0            31         
A165    0            30         
A166    0            30         
A221    0            30         
A202    0            29         
A219    0            29         
A220    0            29         
A208    0            28         
A209    0            28         
A217    0            28         
A205    0            27         
A206    0            27         
A207    0            27         
A204    0            26         
A214    0            26         
A215    0            26         
A185    0            25         
A186    0            25         
A213    0            25         
A188    0            24         
A199    0            24         
A200    0            24         
A190    -1           23         
A192    0            23         
A198    0            23         
A164    -1           22         
A180    -1           22         
A189    -1           22         
A151    -1           21         
A159    -1           21         
A161    -1           21         
A167    -1           20         
A168    -1           20         
A183    -1           20         
A182    -1           19         
A222    -1           19         
A228    -1           19         
A145    -1           18         
A92     -1           18         
A95     -1           18         
A100    -1           17         
A101    -1           17         
A94     -1           17         
A76     -1           16         
A77     -1           16         
A99     -1           16         
A17     -1           15         
A84     -1           15         
A87     -1           15         
A11     -1           14         
A14     -1           14         
A16     -1           14         
A2      -1           13         
A3      -1           13         
A8      -1           13         
A34     -1           12         
A35     -1           12         
A5      -1           12         
A26     -1           11         
A31     -1           11         
A32     -1           11         
A67     -1           10         
A68     -1           10         
A70     -1           10         
A40     -1           9          
A53     -1           9          
A66     -1           9          
A28     -2           8          
A47     -2           8          
A90     -2           8          
A117    -2           7          
A86     -2           7          
A88     -2           7          
A116    -2           6          
A134    -2           6          
A135    -2           6          
A139    -2           5          
A223    -2           5          
A224    -2           5          
A146    -2           4          
A147    -2           4          
A181    -2           4          
A193    -2           3          
A194    -2           3          
A212    -2           3          
A211    -2           2          
A216    -2           2          
A218    -3           2          
A141    -3           1          
A4      -5           1          
A85     -5           1   

Open in new window

0
 

Author Comment

by:happy 1001
ID: 41784554
@ Wayne Taylor, thank you so much. Both those formulas are working fine. The second one is more useful in my case, because I need to limit the ranks from 0 to 100. But as you mentioned, this method has the weakness of assigning the same rank to different values, as in the case of products A37 and A80, both getting a rank of 99, although they have different values of 6.13 and 5.54 respectively.

@ Paul Maxwell, thanks a lot for making me aware of the options that are present in SQL Server. Highly Appreciated.
Although I am a bit confused, regarding the tool that I should use, Excel or SQL Server for this work ! Because the raw data is available within excel, and if I want to make use of SQL Server for ranking, then I will first have to find out some easy method to move this data from excel to sql server, without too much manual effort. And I am not yet able to figure out an easy way to move such data from many different excel files to sql server.

Thank you so much for the help provided.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question