Exclude cell from Rank Array

KyleG

Well-known Member
Joined
Jun 12, 2004
Messages
623
i want to (as the title says) exclude cells from a rank array if the cell in the adjacent cell is 0.

75 4
40 4
90 4
86 4
32 4
100 4
100 0
88 4

In this example it would not include the 100 with the 0 in the array.
is this possible?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
To rank lowest to highest:

Sheet2

<table style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="0" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:72px;"><col style="width:72px;"><col style="width:72px;"></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></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:right; border-style:solid; border-width:1px; border-color:#000000; ">300</td><td style="text-align:right; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">85%</td><td style="text-align:right; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">3</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">280</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">86%</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">4</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">300</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">72%</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">420</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">79%</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">2</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">430</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">93%</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">5</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">40</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">-90%</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">25</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">-88%</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr></tbody></table>


Formula entered in C2:

=IF(A2<100,"",SUMPRODUCT(--(A$2:A$8>=100),--(B2>B$2:B$8))+1)

Copied down as needed.

Hello again, everyone. I returned to this project a couple of days ago, and am not sure how to word my question, but I will try. :confused:

I am using your sumproduct function formula, inversely sorted but have run into an issue where certain values needing to be ranked are the same. It creates a duplicate ranking result, instead of assigning them each a subsequent rank.

For example:

<table style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="0" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:72px;"><col style="width:72px;"><col style="width:72px;"></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></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:right; border-style:solid; border-width:1px; border-color:#000000; ">300</td><td style="text-align:right; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">85%</td><td style="text-align:right; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">3</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">280</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">86%</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">4</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">300</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">79%</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">420</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">79%</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">430</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">93%</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">5</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">40</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">-90%</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">25</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">-88%</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr></tbody></table>
As you may notice, the value of 79% is ranked the same twice, skipping right from #1 to #3 in ranking. (One should obviously be ranked 2nd)

What may be entered into the formula to assign every integer rank between?

Thank you again in advance,

Chris
 
Upvote 0
Hello again, everyone. I returned to this project a couple of days ago, and am not sure how to word my question, but I will try. :confused:

I am using your sumproduct function formula, inversely sorted but have run into an issue where certain values needing to be ranked are the same. It creates a duplicate ranking result, instead of assigning them each a subsequent rank.

For example:

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD>

</TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">300</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">85%</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">280</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">86%</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">300</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">79%</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">420</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">79%</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">1

</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">430</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">93%</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">40</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">-90%</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">25</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">-88%</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">_</TD></TR></TBODY></TABLE>
As you may notice, the value of 79% is ranked the same twice, skipping right from #1 to #3 in ranking. (One should obviously be ranked 2nd)

What may be entered into the formula to assign every integer rank between?

Thank you again in advance,

Chris
Try this...

Book1
ABC
230085%2
328086%3
430079%1
542079%1
643093%4
740-90%_
825-88%_
Sheet1

This array formula** entered in C2 and copied down:

=IF(A2<100,"",SUM(IF(A$2:A$8>=100,IF(B2>B$2:B$8,1/COUNTIF(B$2:B$8,B$2:B$8))))+1)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
 
Upvote 0
Thanks for the new function. I'm having fun with it.
Unfortunately, I ran across a different issue when using it. I've plugged in this updated formula, but I want to see if there is a way to assign each rank (1-5), despite there being a tie. Even if the tie breaker is arbitrary, each rank should be owned by one and only player. Can this be done using your suggestion, or would the sumproduct function be more... functional? hehe
 
Upvote 0
Thanks for the new function. I'm having fun with it.
Unfortunately, I ran across a different issue when using it. I've plugged in this updated formula, but I want to see if there is a way to assign each rank (1-5), despite there being a tie. Even if the tie breaker is arbitrary, each rank should be owned by one and only player. Can this be done using your suggestion, or would the sumproduct function be more... functional? hehe
Post some data and tell us what results you expect.
 
Upvote 0
I apologize for not doing so sooner. I've included additional labeling, and I will break down what I am trying to accomplish. Please forgive me if I insult anyone's sports intelligence.
I hope to have this formula rank a football player's quarterback sacks allowed (for offensive lineman) - a statistic where lower is better (like golf). So, if two players have allowed 0 sacks, the one with more blocking attempts (att) should get the higher ranking.

If at all possible, I would like to see rankings similar to the below:

Sheet1

<table style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="0" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:45px;"><col style="width:45px;"><col style="width:45px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A
att
</td><td>B
sacks
</td><td>C
rank
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:right; border-style:solid; border-width:1px; border-color:#000000; ">269
</td><td style="text-align:right; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:right; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">3
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">215</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">3</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">4
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">276</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">2
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">349</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">178
</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">5</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">5
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">24
</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0
</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">8
</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0
</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr></tbody></table> (rows 7-8 below the minimum requirement threshold, hence no ranking despite allowing no qb sacks)

But, I am more than willing to forgo the tie breaking stipulation in the formula. I am just trying to avoid the rank integers from being skipped (i.e. 1-5 all assigned to someone).

What might I be forgetting?
 
Upvote 0
Thank you all for your help so far. Now I have determined a way to presort the data in column A (att). I didn't think that would be an issue, nor necessarily having to be in the same formula/function as the one we are working with here. Phew!

Where must the current array be modified to automatically assign a different number to each player, without regard for who is ranked higher or lower in the event of a tie? So far, I have been testing with the "+1" and "-1" in every logical place, but it doesn't produce the results I hope to see in column C (rank). I am probably way off.

What else should I try?
 
Upvote 0
Thank you all for your help so far. Now I have determined a way to presort the data in column A (att). I didn't think that would be an issue, nor necessarily having to be in the same formula/function as the one we are working with here. Phew!

Where must the current array be modified to automatically assign a different number to each player, without regard for who is ranked higher or lower in the event of a tie? So far, I have been testing with the "+1" and "-1" in every logical place, but it doesn't produce the results I hope to see in column C (rank). I am probably way off.

What else should I try?
I've been kind of busy the last few days so I haven't been able to work on this any.

Can you post some samples so I can see how you have the data sorted along with the results you expect? That could make this a lot easier.
 
Upvote 0
So here is a sample (really, all of it) of the data I wish to rank in order of lowest sacks first. the function will rank the data in column B (sacks) in column C (rank), and ideally will not duplicate or skip a ranking. The suggestions so far, rank them successfully, but do not recognize a tie. Previously cells B4 and B5 were both ranked #1 and C2 skipped to #3.

This sample data shows the result as giving the tie to the player with the highest value of column A (att). This is because they will obviously have performed better if they have lower value in the sacks column. That part of the equation I am not hung up on. Ranking a tie separately is my main concern, if that is possible.
And note that rows 7-8 do not meet are minimum criteria of 100 (att), so they are not ranked by our function.

Sheet1

<table style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="0" cellpadding="0" cellspacing="0"><colgroup><col style="font-weight:bold; width:30px; "><col style="width:45px;"><col style="width:45px;"><col style="width:45px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A
att
</td><td>B
sacks
</td><td>C
rank
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:right; border-style:solid; border-width:1px; border-color:#000000; ">269
</td><td style="text-align:right; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:right; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">3
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">215</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">3</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">4
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">276</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">2
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">349</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">178
</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">5</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">5
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">24
</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0
</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">8
</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0
</td><td style="color:#ffffff; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr></tbody></table>
Is this a large enough sample? I can expand, but the other sets of data are below the threshold of 100 (att)empts, too.

I am still fiddling with this now. Maybe I will have an epiphany while at the dog park, though!
 
Upvote 0
So here is a sample (really, all of it) of the data I wish to rank in order of lowest sacks first. the function will rank the data in column B (sacks) in column C (rank), and ideally will not duplicate or skip a ranking. The suggestions so far, rank them successfully, but do not recognize a tie. Previously cells B4 and B5 were both ranked #1 and C2 skipped to #3.

This sample data shows the result as giving the tie to the player with the highest value of column A (att). This is because they will obviously have performed better if they have lower value in the sacks column. That part of the equation I am not hung up on. Ranking a tie separately is my main concern, if that is possible.
And note that rows 7-8 do not meet are minimum criteria of 100 (att), so they are not ranked by our function.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD>

</TD><TD>A
att

</TD><TD>B
sacks

</TD><TD>C
rank

</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">269

</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">3

</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">215</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">4

</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">276</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">0</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">2

</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">349</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">0</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">1

</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">178

</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">5

</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">24

</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">0

</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">8

</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">0

</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">_</TD></TR></TBODY></TABLE>
Is this a large enough sample? I can expand, but the other sets of data are below the threshold of 100 (att)empts, too.

I am still fiddling with this now. Maybe I will have an epiphany while at the dog park, though!
In your previos post you said you had the data sorted in a certain way. Does the exhibit in this post show how the data is sorted? It doesn't look sorted, it looks like it's random to me.

If you could sort it like this:

Book1
AB
23490
32760
42691
52153
61785
7240
880
Sheet1

Then it would be super easy to do what you want:

Book1
ABC
234901
327602
426913
521534
617855
7240_
880_
Sheet1

This formula entered in C2 and copied down:

=IF(A2<100,"",COUNT(B$2:B2))
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top