Hello,
I have an issue figuring out a Ranking formula which compares three columns of a different sheet, based on the values in Column A, Column B and Column C. Based on these three values in Col A, B and C, I need to create in a different sheet, a column with the ranking (including the above mentioned columns too).
The Ranking should be based on Column A & then Column B and then Column C.
Your help will be much appreciated.
Thanks in advance.
mt
Last edited by m_t; 03-08-2016 at 10:51 AM. Reason: Attachement
Depends on what the range of values in A,B and C are
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Just numbers from 1 to 20
Are you ranking high to low or low to high? Also, what about ties? Rows 3-7 have the same numbers for all 3 trials.
I used a helper column E (could be done with Arrays but why bother). Column E can be hid
In E2 copied down
=B2+C2/100+D2/10000
Then a simple rank in F2
=RANK(E2,$E$2:$E$32)
Or try this in E2:
=SUMPRODUCT(--($B$2:$B$32*10^6+$C$2:$C$32*10^3+$D$2:$D$32>B2*10^6+C2*10^3+D2))+1
Copy down.
Dear all,
Thank you very much for your help and efforts!
I'm a basic user, so I can't really understand each of the suggested formulas...
What i really need to confirm, is that each column should be RANKED (not sum up everything) with the following order: 1st COLUMN C then as 2nd COLUMN A, then as 3rd COLUMN B from high to low and then to have the final ranking.
I hope it's clear now.
and once more many thanks!
In order to give precedence of B over C over D, there needs to be some manipulation of the data. Excel does not have a built in function that does that. Both my formula and Phucam's formula does that. I am attaching a spreadsheet that shows both formulas (in Col F and G)
Pick the formula which makes the most sense to you. If you need it explained further, let us know.
No need for further explanations!
Many many thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks