Forum Discussion

Darin_Zimmerman's avatar
Darin_Zimmerman
Copper Contributor
Nov 02, 2023

Excel if /then functions help

I am making a very simple spreadsheet, I believe except I am applying a value for a rank of a participant. For example if you rank #1 then you are assigned points of 4, if rank #2 then you are assigned points of 3, #3 then assigned points of 2, and if ranked #4 then assigned points of 1. 

 

I am unable to find an example of this or find help of how to do this. Can someone give suggestion please and Thanks.

excel
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Nov 02, 2023

    Darin_Zimmerman 

    If it's just a simple 1-4 system, you can use CHOOSE:

    rank is A2:A5

    =CHOOSE(rank,4,3,2,1)

     

    If there are ranks outside of 1-4 that should not be awarded points, you could use SWITCH:

    =SWITCH(rank,1,4,2,3,3,2,4,1,"")

     

     

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Nov 02, 2023

    Darin_Zimmerman 

    If you have Excel 365 then

    = LET(
    points, 5 - RANK(numbers, numbers),
    IF(points>0, points, "")
  )

    would return points according to rank.  Without 365 removing the negatives is just a bit more ugly.

Resources