Formula to use fraction to set levels

[asdfiuro]

Hi I want to make a point-based level system

I'll have the data in a table displayed like this:

10 points
30 points
60 points
and so on...

but in the scoreboard will be displayed like this:

level 1: 10/10 points
level 2: 20/20 points (10 points from level 1 + 20 points of level 2)
level 3: 30/30 points (10 points from level 1 + 20 points of level 2 + 30 points of level 3)
and so on...

so in the scoreboard instead of showing:

Player A: 25 points
Player B: 36 points
Player C: 47 points

it will show something like:

Player A: (level 1) 15/20
Player B: (level 2) 6/30
Player C: (level 2) 17/30


So I need a formula to automatically convert integer numbers to fractions according to the respective level.

I was trying this formula

=IF(D9<10,TEXT(D9,"Level 1"&D9&"/10"),IF(AND(D9>10,D9<30),TEXT(D9,"Level 2"&D9&"/20"),IF(AND(D9>30,D9<60),TEXT(D9,"Level 3"&D9&"/30"),"")"")"")

and all I get back is FORMULA ERROR, I know it's a wrong formula but I don't know how to fix it, or if there's a better way around for this.

Sorry in advance, I'm a total noob with this, and thanks for your help

 

[asdfiuro]

Ahm... I found the solution, but I don't know how to delete this question, I feel stupid rn...

 

[asdfiuro]

well I made this formula to work:

=IF(D9<10,TEXT(D9,D9&"/"&10),"")

but I can't add the "level 1" text on it.

(P.D.: how do I edit/delete my own posts? that would make things easier for me here)

 

[fhqwgads]

maybe this?

=IF(D9<=10,TEXT("(Level 1) "&D9& "/10",0),IF(AND(D9>10,D9<=30),TEXT("(Level 2) "&D9-10&"/20",0),IF(AND(D9>30,D9<=60),TEXT("(Level 3) "&D9-30&"/30",0),"")))

you dont need the last 2 pairs of ""

also you need to apply some sort of arithmetic to your level 2 and 3 bits otherwise it would display 60 as level 3 60/30

also also you need to account for values of 10 and 30 and 60 using less than or equal to <= otherwise it will display blank for those values

 

[asdfiuro]

Thanks you so much!! it totally worked!!

 

[fhqwgads]

keep in mind this wont work for values over 60 in D9. i'm not sure how your point scoring works, but if there's a formula in there that adds points from somewhere else and player A is sitting on 55 points which would display as level 3 25/30 who then gets 10 points added, it would bring the value in D9 to 65 which will show up as blank

this formula should show all values over 60 as level 3 30/30

=IF(D9<=10,TEXT("(Level 1) "&D9& "/10",0),IF(AND(D9>10,D9<=30),TEXT("(Level 2) "&D9-10&"/20",0),IF(AND(D9>30,D9<=60),TEXT("(Level 3) "&D9-30&"/30",0),IF(D9>60, TEXT("(Level 3) 30/30",0),""))))

 

[asdfiuro]

I did some changes (removed the "level" tag to use ir on another cel) and worked some more levels, so this is the formula:

=IF(D9<=10,D9&"/"&"10"+N("Level 1"),IF(AND(D9>10,D9<=30)+N("Level 2"),D9-10&"/"&"20",IF(AND(D9>30,D9<=60)+N("Level 3"),D9-30&"/"&"30",IF(AND(D9>60,D9<=100)+N("Level 4"),D9-60&"/"&"40",IF(AND(D9>100,D9<=150)+N("Level 5"),D9-100&"/"&"50",IF(AND(D9>150,D9<=210)+N("Level 6"),D9-150&"/"&"60",IF(AND(D9>210,D9<=280)+N("Level 7"),D9-210&"/"&"70",IF(AND(D9>280,D9<=360)+N("Level 8"),D9-280&"/"&"80",IF(AND(D9>360,D9<=450)+N("Level 9"),D9-360&"/"&"90",IF(AND(D9>450,D9<=550)+N("Level 10"),D9-450&"/"&"100",IF(D9>=550,"MASTER","ERROR")))))))))))

and it works!! ;;w;;

Thanks you SO much for your help!