• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Rank dates with multiple tiebreakers

rharrington10

New Member
Hello everyone,

I'm stuck. I've tried countless formula combinations but can't seem to get one that works 100%. I've attached a spreadsheet with sample data and highlighted my problem combination. What I'm trying to accomplish is essentially an auto rank/sort of 3 columns of dates - date 1, oldest to newest, date 2, oldest to newest and date 3, oldest to newest. Date 2 is the first tiebreaker and date 3 is the second tiebreaker.

The formula closest to the result I'm seeking, cobbled together from countless threads:

=IF(C2="","",COUNTIF($C$1:$C$135,"<"&C2)+1+SUMPRODUCT(--($C$1:$C$135=C2),--($D$1:$D$135<D2))+SUMPRODUCT(--($C$1:$C$135=C2),--($E$1:$E$135<E2)))

I'm hoping someone can spot what I'm missing and/or doing wrong and steer me in the right direction. Any assistance is greatly appreciated.

Thank you in advance...
 

Attachments

rharrington10

New Member
Hi:

I am not sure about the logic why you are considering all three date columns for ranking. Does this work for you?

Thanks
Thank you, Nebu. It's a seniority list, so the formula needs to incorporate all 3 dates. Date 1 is the employee seniority date. If two or more employees have the same seniority date, date 2 (hire date) (oldest to newest) is used as the first tiebreaker. If 2 or more employees have the same seniority date and hire date, date 3 (birth date) (oldest to newest) is used.
 
Last edited:

NARAYANK991

Excel Ninja
Hi ,

If you can make use of the following fact , then , using a helper column makes everything so simple.

All dates are at present a maximum of 5 digits ; 3 dates put together still are within the limit of 15 significant digits.

See the attached file.

Narayan
 

Attachments

rharrington10

New Member
Hi ,

If you can make use of the following fact , then , using a helper column makes everything so simple.

All dates are at present a maximum of 5 digits ; 3 dates put together still are within the limit of 15 significant digits.

See the attached file.

Narayan
Thank you, Narayan. At the end of the day, your solution will work, but, if possible, I would prefer to do it without a helper column.
 

rharrington10

New Member
Hi:
Try this formula without helper column
Code:
=RANK(C2,C$2:C$110,1)+SUMPRODUCT(--(C2=C$2:C$110),--(D2>D$2:D$110))+SUMPRODUCT(--(C2=C$2:C$110),--(D2=D$2:D$110),--(E2>E$2:E$110))
Reference:http://www.ozgrid.com/forum/showthread.php?t=141020

Thanks
Thank you, Nebu. That seems to do the trick. I vaguely remember seeing that page in my travels. My problem is, while I can often make these formulas work, I don't always understand how they work. I appreciate your prompt response...both of them.
 

Peter Bartholomew

Well-Known Member
I aim to achieve boring and repetitive solutions, so I would go with
= 1+
COUNTIFS( Date1,"<"&Date1 ) +
COUNTIFS( Date1, Date1, Date2, "<"&Date2 )

COUNTIFS( Date1, Date1, Date2, Date2, Date3, "<"&Date3 )

If you still get ties then add yet another term that compares the row numbers.
 
Top