Problem with the Rank Function

[logearav]

Hi all,

I am attempting to make a sheet that involves marks, grades and ranks. I have attached my excel sheet too.

TOTAL	GRADE	RANK
199​	FAIL​	NO RANK​
562​	PASS​	​ 1​
223​	PASS​	​ 3​
281​	PASS​	​ 2​
220​	FAIL​	NO RANK​
169​	FAIL​	NO RANK​
210​	PASS​	​ 5​

For the total 210 the rank should come as 4 instead of 5. How to rectify this?

 

[Fluff13]

How about
=IF(I2="Fail","No rank",COUNTIFS($I$2:$I$8,"Pass",$H$2:$H$8,">"&H2)+1)

 

[Peter Bartholomew]

I set out to explore aspects of Excel 365 insider. The formula for ranking is the same as @Fluff13's

= IF(grade="PASS",
     1+COUNTIFS(
       total, ">"&total,
       grade, "PASS"),
    "NO RANK")

but, avoiding relative references, the single row formulas look radically different

= LET(
    SUMλ, LAMBDA(arr, SUM(arr)),
    BYROW(marks, SUMλ)
  )

It defines a named Lambda function to be the SUM of an array and then BYROW feeds the table of marks to the function row by row. Just as Power Query offers solution strategies that share little with traditional spreadsheet practice, it now appears that dynamic arrays can offer an equally radical departure! The knowledge required to create such solutions is greater but the plus is that the solutions are fully dynamic. As student names are added, the analysis of the results extends to accommodate them without further user intervention.