points and rankings!

[lukman4068]

Hi guys,

Just wondered if anyone knew how to get around this!

I suspect there's going to be some complicated IF Formula's here :/ but not quite sure how to make this work.

The scenario is two fold:

1) I need to rank a group of people - however in this way -
More than 1 person can score 100%. So, say if there are 10 people, and 2 score 100% - they would both be ranked as 1. The 3rd person who scored 96% should be ranked as 3, and if the next 3 all score 90% they should be ranked as 4, and if the 7th person scored 86% they should be ranked as 7th.

Is there any formula to use to have this done automatically?

The second part is this:

People are ranked by their score in this particular area.

They have points allocated to them for that particular area which is worked out by multiplying their rank by a number dependent on their score:

So, if someone scores:

87-100% - They would have their rank multiplied by 1 (or their rank = their score)

83-86% - they would have their rank multiplied by 2 - so if they ranked as 18th position (because 18 people scored above them) - they would have 36 points in total

79-82% - they would have their rank multiplied by 3

Anything 78% or below would have their rank multiplied by 4

Hopefully that makes some sort of sense! I'm currently entering the formula in individually into each cell - changing the number we multiply by manually. Is there any way of doing this independently?

Appreciate any help on this!

if you need anymore clarification, of course i'll be able to provide it!

 

[c_m_s_jr]

Assuming you have the individual name or ID in Column A and Score in Column B

Then in C2 =RANK(B2,$B$2:$B$100) -- Adjust range as necessary
Then in D2 =IF(B2<0.78,4,IF(B2<0.82,3,IF(B2<0.86,2,IF(B2<100,1))))*C2