CODEWITHSUNDEEP
excelexcel-formulaspreadsheet
PhillyStafford
PhillyStafford

Reputation: 697

Exclude cell value from rank range based on value in another cell

I am putting together a golf society scoring & handicap sheet. I am having trouble trying to exclude a NON-members score from the ranking of the members scores.

A non member cannot win, even if they have the highest score. I want to ignore/exclude the NON-members scores from the result positions. i.e. If a NON-member has the highest score, they will not be ranked as 1st position (or any position for that matter)

So here's the deal:

A NON-member is marked with "n". I want to see if the "Members" column cell has an "n" in it or not. If so, I want to exclude their "Score" form being included in the overall "Position" column.

Here's what I have so far:

=IF(AND(C2="",D2<>""),RANK(D2,$D$2:$D$39,0),IF(AND(C2="",D2=""),"",IF(AND(C2<>"",D2=""),"",IF(AND(C2<>"",D2<>""),""))))

Here is the result that I am getting:

enter image description here

But here is the result that I want:

enter image description here

The difference between the 2 tables is the values in the "Position" column. I only circled one difference but if you look closely you will notice that the values in the "Position" column are all different in both tables. This is due to the NON-member scores being excluded from "Position" ranking in the second table. The second table is the result I would like to achieve.

Upvotes: 2

Views: 15646

Answers (2)

Doug Glancy
Doug Glancy

Reputation: 27478

Here's a formula that I modified from this answer after searching for "Excel rank with conditions":

=IF(B2<>"n",COUNTIFS(B$2:B$100,"<>n",A$2:A$100,">"&A2)+1,"")

Note that this works in Excel 2007 or higher.

Upvotes: 2

Derrik
Derrik

Reputation: 1103

I would suggest using an extra helper column (that can be hidden) to filter out the non-member scores.

For example, to the left of your 'Position' column, use the following in a new Column E that is inserted and copy it down to the bottom of your list:

=IF(C2="n","",D2)

From there, you can reference this 'helper' column that will return blanks for the non-member scores and can be used to rank only your member scores such as the following to compute your 'Position' column:

=IF(E2<>"",RANK(E2,$E$2:$E$39,0),"")

Hope this helps.

Cheers!

Upvotes: 1

Related Questions