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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]#1[/TD]
[TD]Pass[/TD]
[TD]Fail[/TD]
[TD]44.07[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]#2[/TD]
[TD]Pass[/TD]
[TD]Pass[/TD]
[TD]40.08[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]#3[/TD]
[TD]Pass[/TD]
[TD]Pass[/TD]
[TD]54.07[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]#4[/TD]
[TD]Pass[/TD]
[TD]Pass[/TD]
[TD]44.55[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

This is what I'm trying to do:
[TABLE="width: 500"]
<tbody>[TR]
[TD]#1[/TD]
[TD]Pass[/TD]
[TD]Fail[/TD]
[TD]44.07[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]#2[/TD]
[TD]Pass[/TD]
[TD]Pass[/TD]
[TD]40.08[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]#3[/TD]
[TD]Pass[/TD]
[TD]Pass[/TD]
[TD]54.07[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]#4[/TD]
[TD]Pass[/TD]
[TD]Pass[/TD]
[TD]44.55[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]#1[/TD]
[TD]Pass[/TD]
[TD]Fail[/TD]
[TD]44.07[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]#2[/TD]
[TD]Pass[/TD]
[TD]Pass[/TD]
[TD]40.08[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]#3[/TD]
[TD]Pass[/TD]
[TD]Pass[/TD]
[TD]54.07[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]#4[/TD]
[TD]Pass[/TD]
[TD]Pass[/TD]
[TD]44.55[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

This is what I'm trying to do:
[TABLE="width: 500"]
<tbody>[TR]
[TD]#1[/TD]
[TD]Pass[/TD]
[TD]Fail[/TD]
[TD]44.07[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]#2[/TD]
[TD]Pass[/TD]
[TD]Pass[/TD]
[TD]40.08[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]#3[/TD]
[TD]Pass[/TD]
[TD]Pass[/TD]
[TD]54.07[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]#4[/TD]
[TD]Pass[/TD]
[TD]Pass[/TD]
[TD]44.55[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

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.