I have a ranking for all of our sales reps - based on % of sales for Units (Col Y) with ties broken by Col AA
example :
rep 1 has 100% in Column Y and 15% in column AA
rep 2 has 100% in Column Y and 50% in column AA
rep 2 should be ranked higher
Problem - there are some that have 0's in both columns creating a tie..
Please Login or Register to view this content.
How can I add column B (hire date) so that those hired recently are ranked higher in this tie.
Maybe this:
=IF(Y3<>"",RANK(Y3,Y$3:Y$42)+SUMPRODUCT((Y$3:Y$42=Y3)*(AA$3:AA$42>AA3)*(B$3:B$42>B3)),"")
you might need to play with the operator to get it right.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
I created a sample - still getting the duplicate rankings (specifically the 0's)
In the Sample, Column F is the Ranking that it should be - Column B is the formula we are working on.
With so many varying numbers, perhaps it's easier with a helper column to provide a ranking key, like so:
Wanted to avoid the additional column - but it is working so...
Thamks for the assistance
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks