How to use "highest rank" with ties

[CalvinTy]

(Disclaimer: I did post a topic yesterday with title of "SUMPRODUCT & COUNTIF question?" but I figured that I was not clear and I wanted to provide a simple example of what I really wanted regardless of which formula combinations would work for what I need).

First, the sheet having 6 random names/dates from larger data set:

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:87px;"><col style="width:37px;"><col style="width:85px;"><col style="width:53px;"><col style="width:84px;"><col style="width:85px;"><col style="width:40px;"><col style="width:36px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>
</td><td>
</td><td>
</td><td>
</td><td style="text-align:center; ">Today's date</td><td style="font-weight:bold; text-align:center; ">31-May-2012</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td style="font-weight:bold; ">
</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:center; ">Highest Rank</td><td style="text-align:center; ">Rank</td><td style="text-align:center; ">Total Days</td><td style="text-align:center; ">Name</td><td style="text-align:center; ">Birthdate</td><td style="text-align:center; ">Deathdate</td><td style="text-align:center; ">Years</td><td style="text-align:center; ">Days</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:center; ">1</td><td style="text-align:center; ">1</td><td style="text-align:center; ">40854</td><td style="text-align:center; ">NameC</td><td style="text-align:center; ">24-Jul-1900</td><td>
</td><td style="text-align:center; ">111</td><td style="text-align:center; ">312</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:center; ">4</td><td style="text-align:center; ">2</td><td style="text-align:center; ">40770</td><td style="text-align:center; ">NameD</td><td style="text-align:center; ">26-Sep-1900</td><td style="text-align:center; ">11-May-2012</td><td style="text-align:center; ">111</td><td style="text-align:center; ">228</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="text-align:center; ">3</td><td style="text-align:center; ">3</td><td style="text-align:center; ">40691</td><td style="text-align:center; ">NameB</td><td style="text-align:center; ">24-Jul-1900</td><td style="text-align:center; ">20-Dec-2011</td><td style="text-align:center; ">111</td><td style="text-align:center; ">149</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style="text-align:center; ">3</td><td style="text-align:center; ">T4</td><td style="text-align:center; ">40615</td><td style="text-align:center; ">NameE</td><td style="text-align:center; ">20-Mar-1901</td><td>
</td><td style="text-align:center; ">111</td><td style="text-align:center; ">72</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td style="text-align:center; ">3</td><td style="text-align:center; ">T4</td><td style="text-align:center; ">40615</td><td style="text-align:center; ">NameF</td><td style="text-align:center; ">20-Mar-1901</td><td>
</td><td style="text-align:center; ">111</td><td style="text-align:center; ">72</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td style="text-align:center; ">2</td><td style="text-align:center; ">6</td><td style="text-align:center; ">40236</td><td style="text-align:center; ">NameA</td><td style="text-align:center; ">24-Jul-1900</td><td style="text-align:center; ">21-Sep-2010</td><td style="text-align:center; ">110</td><td style="text-align:center; ">59</td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>A4</td><td>=SUMPRODUCT(($E$4:$E$9<=E4)*1,($F$4:$F$9<=F4)*1)</td></tr><tr><td>B4</td><td>=IF(COUNTIF($C$4:$C$9,C4)>1,CONCATENATE("T",RANK(C4,$C$4:$C$9,0)),RANK(C4,$C$4:$C$9,0))</td></tr><tr><td>C4</td><td>=IF(F4="",$F$1-E4,F4-E4)</td></tr><tr><td>G4</td><td>=IF(F4="",(YEAR($F$1)-YEAR(E4)-IF((MONTH($F$1)*31+DAY($F$1))>=(MONTH(E4)*31+DAY(E4)),0,1)),(YEAR(F4)-YEAR(E4)-IF((MONTH(F4)*31+DAY(F4))>=(MONTH(E4)*31+DAY(E4)),0,1)))</td></tr><tr><td>H4</td><td>=IF(F4="",($F$1-DATE(YEAR(E4)+G4,MONTH(E4),IF(AND(DAY(E4)=29,MONTH(E4)=2,DAY(DATE(YEAR(E4)+G4,MONTH(E4),DAY(E4)))<>29),28,DAY(E4)))),(F4-DATE(YEAR(E4)+G4,MONTH(E4),IF(AND(DAY(E4)=29,MONTH(E4)=2,DAY(DATE(YEAR(E4)+G4,MONTH(E4),DAY(E4)))<>29),28,DAY(E4)))))</td></tr></tbody></table></td></tr></tbody></table>

First, the RANK column works perfectly for me (I do want to CONCATENATE by prefacing any ties with "T" and then result of RANK function).

Before I posted, I did sort the table above from highest to lowest based on Total Days column.

Problem #1: The "highest rank" is where I am having trouble with. Even without the visual CONCATENATE aid, should the answer for NameA in cell A9 be "1" as well as for NameB in cell A6 being "1", too?

In short, before the first death of the "group" in 2010, the 3 people with 24-Jul-1900 birth dates were "ranked first". From what I understand, it's because I set up SUMPRODUCT with an equal sign, but if I remove the equal sign, then the ranking results are wrong (i.e. ranking of "0" shows up for leader).

Problem #2: After resolving problem #1 above, how can I implement CONCATENATE into a SUMPRODUCT formula in where I can get results such as "T1" for NameA, NameB, and NameC?

Improvement #1 (optional): Regarding the Years and Days columns, I basically got the formulas from my organization's old files. They seem logical to me (it took me a while to understand how everything was calculated), but I wonder if there is any more simplistic formula or method to calculate Years and Days, just curious?

Thanks in advance!

* CalvinTy

 

[barry houdini]

Hello Calvin, does this formula give you the correct rank?

=IF(F4="",RANK(C4,C$4:C$9),RANK(E4,E$4:E$9,1))

 

[CalvinTy]

Hello Calvin, does this formula give you the correct rank?

=IF(F4="",RANK(C4,C$4:C$9),RANK(E4,E$4:E$9,1))

I plugged this into cell A4 (down to A9). It seemed to be closer to what I am looking for... but I see that NameD should not be only "4" for highest rank. NameD was (and would still be) 2nd oldest of that group of 6 names?

* CalvinTy

 

[barry houdini]

OK would this work?

=IF(F4="",RANK(C4,C$4:C$9),MIN(RANK(E4,E$4:E$9,1),RANK(C4,C$4:C$9)))

 

[CalvinTy]

OK would this work?

=IF(F4="",RANK(C4,C$4:C$9),MIN(RANK(E4,E$4:E$9,1),RANK(C4,C$4:C$9)))

That worked as expected! Let me see if I understand that -- basically selecting the smallest value, whether it is earliest rank of birthdate OR ranked by total days. This "should" deal with multiple ties, it would appear.

Let me apply this to my larger data set right now to see if I have overlooked possible issues.

Much appreciated!

* CalvinTy

 

[CalvinTy]

Applying that to the larger data set didn't work... let me give the exact formula as modified:

In cell A16 downwards (Column A = Highest Rank column)

=IF(M16="",RANK(G16,G$16:G$1506),MIN(RANK(K16,K$16:K$1506,1),RANK(G16,G$16:G$1506)))

Column M has the death date
Column G is Total Days
Column K has the birth date

It would do exactly the same thing that the Rank column does (Column B) other than the CONCATENATE visual aid.

=IF(COUNTIF($G$16:$G$1506,G16)>1,CONCATENATE("T",RANK(G16,$G$16:$G$1506,0)),RANK(G16,$G$16:$G$1506,0))

What am I doing wrong?

* CalvinTy

 

[CalvinTy]

This snapshot is what I had before the suggested solution...

Applying the suggested solution:

The solution should be very similar to what I originally had, just that the problem was this:

Where the cell is -- both that cell and the cell below it that says "28", it should be "27" for both. In my other file with only the living list, they are correctly recorded as "T27".

I hope the snapshots help everything here. How do I fix this issue?

NOTE: Yes, the dates are 400 years in the future so we can work with dates before March 1, 1900.

Cheers,

* CalvinTy

 

[barry houdini]

It would do exactly the same thing that the Rank column does (Column B) other than the CONCATENATE visual aid.

I'm not sure what that means. Surely the highest rank isn't expected to give the same ranking value as "Rank"?

Is it possible to post a small sample where my formula suggestion doesn't work......and showing the expected results?

...meanwhile....You asked about the years and days. You could try these formulas for G4 and H4 copied down

=DATEDIF(E4,IF(F4="",F$1,F4),"y")

and

=IF(F4="",F$1,F4)-EDATE(E4,G4*12)

Edit: I posted this before I saw your last post: I'll have a look at that and try to understand your requirements.......

 

[CalvinTy]

I'm not sure what that means. Surely the highest rank isn't expected to give the same ranking value as "Rank"?

Is it possible to post a small sample where my formula suggestion doesn't work......and showing the expected results?

I knew I worded that poorly so that's why I gave a snapshot of how your suggestion seems to do very similar to the Rank column, when you correctly point out that highest rank shouldn't be expected to give same ranking value. Hope that helped as you saw the snapshots.

...meanwhile....You asked about the years and days. You could try these formulas for G4 and H4 copied down

=DATEDIF(E4,IF(F4="",F$1,F4),"y")

and

=IF(F4="",F$1,F4)-EDATE(E4,G4*12)

Both formulas worked! Definitely a shorter length formula big time! Guess I have to learn what DATEDIF and EDATE actually do...

I look forward to the issue of having ties showing up as a higher tied number (28 and 28) rather than lower tied number (27 and 27). I'll be stepping out for a couple of hours -- thanks to all your help, Barry, and anyone else who can assist!

NOTE: I cannot remember where online but I had found the SUMPRODUCT formula a few weeks ago and it appeared to work flawlessly until it was pointed out about the "off by one" tie issue. So I don't know if SUMPRODUCT is what I would be looking for, especially that after resolving the tie issue, I still want to use CONCATENATE.

Cheers,

* CalvinTy

 

[barry houdini]

OK, last post of the night for me.....

My immediate thought was that your SUMPRODUCT should be like this

=SUMPRODUCT(($K$16:$K$1506< K958)*1,($M$16:$M$1506 >M958)*1)+1

no = signs with < and > but a +1 at the end, that would be closer to the "standard" SUMPRODUCT you'd use for ranking - perhaps try that on your real data.......but that formula won't work for your small example you started with here, it gives 1 for A5 rather than 2.....