Issue with skipping rank values (RANK + IF + OR)

[isadork]

Hello all, first time poster here. I've run into an issue and can't figure out how to get around it.

I have 3 columns that come into play here, Phase 1 (Pass/Fail), Phase 2 (Pass/Fail) and score. I want to rank only the ones that have passed both Phases, giving the failed ones a rank of 0.

I'm using the formula =IF(OR(J21="Fail",F21="Fail"),0,RANK(M21,$M$21:$M$24,0)) to only rank the ones that have passed both. But the issue with this is that if one of them has failed, the rank skips that number.

I'm sure it's a matter of using the wrong formula (or combination of formulas), I'm just not sure what else to try.

This is the result I'm getting:

#1	Pass	Fail	44.07	0
#2	Pass	Pass	40.08	4
#3	Pass	Pass	54.07	1
#4	Pass	Pass	44.55	2

<tbody>
</tbody>

This is what I'm trying to do:

#1	Pass	Fail	44.07	0
#2	Pass	Pass	40.08	3
#3	Pass	Pass	54.07	1
#4	Pass	Pass	44.55	2

<tbody>
</tbody>

 

[jarjarbingie]

Hello all, first time poster here. I've run into an issue and can't figure out how to get around it.

I have 3 columns that come into play here, Phase 1 (Pass/Fail), Phase 2 (Pass/Fail) and score. I want to rank only the ones that have passed both Phases, giving the failed ones a rank of 0.

I'm using the formula =IF(OR(J21="Fail",F21="Fail"),0,RANK(M21,$M$21:$M$24,0)) to only rank the ones that have passed both. But the issue with this is that if one of them has failed, the rank skips that number.

I'm sure it's a matter of using the wrong formula (or combination of formulas), I'm just not sure what else to try.

This is the result I'm getting:

#1	Pass	Fail	44.07	0
#2	Pass	Pass	40.08	4
#3	Pass	Pass	54.07	1
#4	Pass	Pass	44.55	2

<tbody>
</tbody>

This is what I'm trying to do:

#1	Pass	Fail	44.07	0
#2	Pass	Pass	40.08	3
#3	Pass	Pass	54.07	1
#4	Pass	Pass	44.55	2

<tbody>
</tbody>

try this:

=IF(OR($F21="Fail",$J21="Fail"),0,SUM(IF(($F$21:$F$24="Pass")*($J$21:$J$24="Pass")*($M$21:$M$24>$M21),1,0))+1) press CTRL+SHIFT+ENTER

 

[Marcelo Branco]

Maybe...

=IF(OR(F21="Fail",J21="Fail"),0,COUNTIFS(M$21:M$24,">"&M21,F$21:F$24,"Pass",J$21:J$24,"Pass")+1)

M.

 

[isadork]

Thanks to both of you, both formulas worked!

If possible, would you explain what the formulas mean? It would really help me in future.

Also what the CTRL+SHIFT+ENTER does? I've not seen this before.