2007: RANK if 2 conditions are met.

[Christiaan]

Hello everyone!

I have a question about the RANK-function.
I need to have a ranking, simple enough with the RANK function. BUT... There need to be 2 criteria in 2 other cells met before a ranking is assigned.

For example:
Cell AC4 is attendance, needs to be "Y".
Cell AH4 is test result, needs to be "Passed".

If these two are true, then it needs to rank the value in AF4 (test result). The range of the cells is $AF$4:$AF$20.

I came up with this:
=IF(AND($AC4="Y";$AH4="Pass");RANK($AF4;$AF$4:$AF$20);"DNQ").

In my perception, this formula would say: Hey, if this person is present and passed the test, we need to give him a ranking. If he was not here, or failed the test, he DNQ (Did Not Qualify). That works partially.
The problem however... It will still look at the range ($AF$:$AF20) and assign a ranking based on that range. The result looks like this:

[TABLE="width: 420"]
<tbody>[TR]
[TD]n[/TD]
[TD]84,90%[/TD]
[TD]32,90%[/TD]
[TD]117,80%[/TD]
[TD]DNQ[/TD]
[TD]Att. error[/TD]
[/TR]
[TR]
[TD]y[/TD]
[TD]84,00%[/TD]
[TD]32,00%[/TD]
[TD]116,00%[/TD]
[TD]DNQ[/TD]
[TD]Fail -> N2[/TD]
[/TR]
[TR]
[TD]y[/TD]
[TD]85,10%[/TD]
[TD]32,50%[/TD]
[TD]117,60%[/TD]
[TD]2
[/TD]
[TD]Pass[/TD]
[/TR]
</tbody>[/TABLE]


The idea here is to have the rank in the 3rd row 1.
Any one know how to achieve this?!

Any help greatly appreciated.

 

[Marcelo Branco]

Maybe...

=IF(AND($AC4="Y",$AH4="Pass"),COUNTIFS($AC$4:$AC$20,"y",$AH$4:$AH$20,"Pass",$AF$4:$AF$20,">"&AF4)+1,"DNQ")

M.

 

[Christiaan]

Hello Marcelo!

This works like a charm. Tested it and it does come up with the right ranking!
Thank you!

Christiaan

 

[Marcelo Branco]

Hello Marcelo!

This works like a charm. Tested it and it does come up with the right ranking!
Thank you!

Christiaan

You are welcome and thanks for the feedback,

M.