I am using the rank function in excel 2000 and the result is the reverse of what I need. How can I correct this?
![]() | Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years. Thanks, CNET Support |
I am using the rank function in excel 2000 and the result is the reverse of what I need. How can I correct this?
Discussion is locked
I don't haved the actual work book in fromt of me as I am on Vacation. I just thought I would see what I could find out. Here is the problem in a nut shell. We have 3 work books (one for each shift) with the total amount of money taken in for each month in one column and the number of sales in another column. These are associated to another work book for everybody for the year. These totals are then associated to another work book in the superviser's office where each colum is then ranked and the 2 ranks are added together and ranked again. This is where the problem is as Excel ranks from highest to lowest and I need it to do the opposite as we have the highest total as the smaller number.
Thanks for any help you can give.
Lewisd,
One solution springs to my mind immediately. It's a trick programmers use to make a descending index where the system (like in MS Access) only supports ascending ones. You index on/rank on the negative of the value, or the compliment of the value to a very high one! One column with a very simple formula (=-origcell, or =100000-origcell) somewhere out of sight (hidden or to the far right) suffices.
Say the numbers are 2, 5 and 1. They rank as 2,1 and 3. Subtract from 10 and you get 8, 5 and 9, that rank as 2, 3 and 1.
But you might prefer the other solution, right from Excel's function wizard: the third parameter of the rank-function determines if it's descending or ascending.
If you are programming, learn to think as a programmer and learn to read the manual and the help. It really makes a difference.
Hope this helps.
Kees