Rank Formula

[sassriverrat]

Good Morning-

Quick question for someone-

I've got
numbers A1:A10
a "Y" or "N" in B1:B10
a "Y" or "N" in C1:C10

I'm trying to put a rank formula together that
IF B cell is a "Y", it goes to the top of the rank, irregardless of the number in A cell. Then if C cell is a "Y", it goes to the top of the rank AFTER the B cell "Y"- and finally, if both B and C cells are "N", then the final numbers are ranked by the numbers in "A"

Ranks are put in column D.

Purpose- the ranking is by the number in A (Level 1) but if B is a "Y", then it automatically goes to the top of the list (Level 2) and if C is a "Y", it automatically goes to the top of the list (Level 3).

 

[Eric W]

Try:

	A	B	C	D	E
N	N
N	N
N	Y
N	N
N	N
N	N
N	N
Y	N
N	N
N	N

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]14[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D1[/TH]
[TD="align: left"]{=IF(B1="y",1,IF(C1="y",COUNTIF($B$1:$B$10,"Y")+1,SUM(IF((B$1:$B$10="N")*($C$1:$C$10="N")*($A$1:$A$10>A1),1))+COUNTIF($B$1:$C$10,"Y")+1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly,
Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[sassriverrat]

Copied and pasted (without {}) and entered with Shift+CTRL+Enter and it popped in as if I hadn't put an equals into it. Tried a second time and every cell equalled 30 (I dragged it down from cells G5 through G58).....ideaS?

 

[Eric W]

Not sure. If you have the cell formatted as Text, a formula would be entered as text, and not calculate. As far as the second time, I assume you changed all the range references? If you put the formula in G5, does that mean your A column is D, and B is E, and C is F? All from row 5 to 58? The formula does assume that your original B and C columns are contiguous. Is that true in your current ranges? You also said that B/C had N or Y in them. Are some cells empty, in A, B, or C?

 

[DanteAmor]

Try this normal formula, it is longer but requires less memory to work with large amounts of data.

<b>Sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /><col style="width:66px;" /><col style="width:80px;" /></colgroup><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></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">5</td><td style="background-color:#92d050; ">N</td><td style="background-color:#92d050; ">N</td><td style="text-align:right; ">10</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">3</td><td >N</td><td style="background-color:#ffff00; ">Y</td><td style="text-align:right; ">2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">8</td><td >N</td><td style="background-color:#ffff00; ">Y</td><td style="text-align:right; ">1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">1</td><td style="background-color:#ffc000; ">Y</td><td >N</td><td style="text-align:right; ">4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">14</td><td style="background-color:#92d050; ">N</td><td style="background-color:#92d050; ">N</td><td style="text-align:right; ">5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">7</td><td style="background-color:#92d050; ">N</td><td style="background-color:#92d050; ">N</td><td style="text-align:right; ">8</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">7</td><td style="background-color:#92d050; ">N</td><td style="background-color:#92d050; ">N</td><td style="text-align:right; ">8</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">8</td><td style="background-color:#ffc000; ">Y</td><td >N</td><td style="text-align:right; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">10</td><td style="background-color:#92d050; ">N</td><td style="background-color:#92d050; ">N</td><td style="text-align:right; ">6</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">9</td><td style="background-color:#92d050; ">N</td><td style="background-color:#92d050; ">N</td><td style="text-align:right; ">7</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D1</td><td >=IF(C1="Y",COUNTIFS($C$1:$C$10,C1,$A$1:$A$10,">"&A1)+1,IF(B1="Y",COUNTIFS($B$1:$B$10,B1,$A$1:$A$10,">"&A1)+1+COUNTIF($C$1:$C$10,"Y"),COUNTIFS($B$1:$B$10,B1,$C$1:$C$10,C1,$A$1:$A$10,">"&A1)+1+COUNTIF($B$1:$B$10,"Y")+COUNTIF($C$1:$C$10,"Y")))</td></tr></table></td></tr></table> <br /><br />

 

[DanteAmor]

Try the next one, I cut it a little

=SI(C1="Y",CONTAR.SI.CONJUNTO($C$1:$C$10,C1,$A$1:$A$10,">"&A1)+1,SI(B1="Y",CONTAR.SI.CONJUNTO($B$1:$B$10,B1,$A$1:$A$10,">"&A1)+1+CONTAR.SI($C$1:$C$10,"Y"),CONTAR.SI.CONJUNTO($B$1:$B$10,B1,$C$1:$C$10,C1,$A$1:$A$10,">"&A1)+1+CONTAR.SI($B$1:$C$10,"Y")))

 

[DanteAmor]

I'm sorry, I forgot to translate it:



=IF(C1="Y",COUNTIFS($C$1:$C$10,C1,$A$1:$A$10,">"&A1)+1,IF(B1="Y",COUNTIFS($B$1:$B$10,B1,$A$1:$A$10,">"&A1)+1+COUNTIF($C$1:$C$10,"Y"),COUNTIFS($B$1:$B$10,B1,$C$1:$C$10,C1,$A$1:$A$10,">"&A1)+1+COUNTIF($B$1:$C$10,"Y")))

 

[Eric W]

Dante is correct, this doesn't necessarily require an array formula. I converted my formula to:

=IF(B1="y",1,IF(C1="y",COUNTIF($B$1:$B$10,"Y")+1,COUNTIFS(B$1:$B$10,"N",$C$1:$C$10,"N",$A$1:$A$10,">"&A1)+COUNTIF($B$1:$C$10,"Y")+1))

which is actually slightly shorter than my original version. Try that and let us know. If you're still having issues, answer the questions from post 4, and maybe we can figure it out.

 

[DanteAmor]

@Eric W

Then we must analyze the problem of the tie-breaker, but before going ahead, let's wait if the user requires it.

 

[sassriverrat]

Ok this has been a very interesting exercise- thank you both for your continued inputting and I apologize for the slowness of my responses.

Dante- your formula mostly worked but produced a series of cells that said #value at seemingly random intervals. Two of these were noted in the 9 and the 10 rows for starters.

Eric W- your formula seems to have worked perfectly with all but one exception and maybe that was my fault in my explanation (per how I view your formula). The actual ranges used are as follows:

G4:G60- a total number of days.
H4:H60- A "Y" for Yes or a "N" for No
I4:I60- A "Y" for Yes or a "N" for No

This formula is going into the D column (D4:D60).

The way I'd like this to work- everyone is ranked based upon the number of days that they have. If Dante has 1000 in column G and Eric has 900, Dante is #1 and Eric #2 . However, a "Y" in the H column automatically moves you to the front of the line. If two people have a "Y" in the H column, the one with the higher day-count in G becomes #1 and the lower becomes #2 , but either way the "Y" moves them to the front of the line. Now a "Y" in the I column moves them to the front of the line, above the ranking of G but still behind that of H. Please see picture below for clarification on how I would like the formula to work.