• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problem with the Rank Function

logearav

New Member
Hi all,

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


TOTALGRADERANK
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?
 

Attachments

Peter Bartholomew

Well-Known Member
I set out to explore aspects of Excel 365 insider. The formula for ranking is the same as @Fluff13's
Code:
= IF(grade="PASS",
     1+COUNTIFS(
       total, ">"&total,
       grade, "PASS"),
    "NO RANK")
but, avoiding relative references, the single row formulas look radically different
Code:
= 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.
 
Top