Help with ranking- i think!

[Muppeteer]

Hi all, was sent here by a friend who said u may be able to help!
I have a spreadsheet which works out a percentage of a target achieved for people. Aka in a list going down Column A2-A13 is the name of the colleague and column B2-13 is the % they have achieved. They are then ranked by the sheet 1-12 in column C. This rank then adds to another load of differently ranked KPIs to give a balance score card rank. This all works fine.

What i want to do it is amend the values of the rank dependent on the %. For example if they achieve OVER 100% they get -1 added to their ranking (remember lower the number the better). If they get 90-100% they get +1, 80-90% +2 70-80% +3

Hope this makes sense! If someone can help i would be grateful!

 

[Hui]

Muppeteer

Firstly, Welcome to the Chandoo.org Forums

In C2 amend the formulas as per below:

=Existing Formula + If(B2>100%,-1,if(B2>90%,+1,If(b2>80%,+2,+3)))
copy down

 

[Muppeteer]

Muppeteer

Firstly, Welcome to the Chandoo.org Forums

In C2 amend the formulas as per below:

=Existing Formula + If(B2>100%,-1,if(B2>90%,+1,If(b2>80%,+2,+3)))
copy down

Thanks Hui, very grateful for quick response, im not in work where i use Excel but wanted to try using Libre Office on my home PC. It doesnt appear to be working?

Based on this screen shot i would have expected Column G to have the data as follows:

Kam 10 (he is actually 79.9%) as his rank in column H was 7 but as he was between 70%-80% he gets +3 - SO HE IS CORRECT!

Ed 3 - he shows as 5 tho? his ranking in G was 4 but as he was over 100% he gets -1

Lauren 16 - her ranking in G is 9 but as she is only 37% she should have +7

John 2 - ranking shows 3 but -1 as over 100%

Holly - looks correct? +1 to column G as she is 98%

etc

Is there something obvious that i have done wrong? of will this work correctly in Excel when im back in work

Very grateful for the help!

Pete

 

[Hui]

Your formula is incorrect

try: =RANK(F4,$F$4:$F$13) + IF(F4>0.9,1, IF(F4>0.8,2, IF(F4>0.7,3, IF(F4>0.6,4, IF(F4>0.5,5, IF(F4>0.4,6, IF(F4>0.4,6,0)))))))