RANK Formula

[JBWalker]

I have been successful utilising RANK formula to annote highest to lowest from a set field. Set field being numbers with no other influences.

example =RANK(A1,C1:C10)

However I now have need to modify data C1:C10, instead of having set numbers in these fields i now require each cell to be the result of an equation.

C1 = D1+E1
C2 = D2+E2
etc

Now that the information in C1:C10 is derived from other formulas my original =RANK formula doesn't work.


If this makes sense, does anybody have any suggestions?

 

[mrshl9898]

Can you share your values?

Should be working from what I understand. Though not sure why you'd compare A1 to values in C

 

[JBWalker]

I have attached snap of the tab in which i am currently working. The aim of this tab is to gain a quick snapshot view (untilising =RANK) of student progress.
Total score is Practical -> Teamwork, the value of which has been transposed AJ column (which would usually be hidden)
Current situation dictates I need to seperate front runners Student 3 & 12 and I would utilise "Behaviour" to achieve this.

I could include "Behaviour" in the total score to solve my issue, but for other reasons this is not possible.
My solution was to input the following =sum(AJ4+J9) into cell AK4, continuing down to AK23 and then have cell M10 read =RANK(J10,AK4:AK23)
However when i do this I get #N/A (a value is not available to the formula or funtion)

I'm known for over complicating things, so i'm sure there is an easier way but I'm not sure what it is.

Appreciate any help or suggestions given

 

[mrshl9898]

Looks like your challenge is RANK only works if the value you are looking for exists in the cells you are looking at.

Struggling with a workaround without sorting the totals and behavior first, defeating the purpose.

 

[petertenthije]

You could still include the behaviour.

Adding the behaviour to the score directly would affect the ranking as a whole. I understand from your explanation that this is undesirable.

Using student 1 as an example,what you could use as formula in AJ4 is this: =SUM(J4:J8)+(J9/1000)

Keep in mind though that when two students have the same total score AND the same behavioral score, then they would still be tied. You could modify the formula further still like below, which would add a alphabetical ranking on top of your “total score + behavior“ ranking: =SUM(J4:J8)+(J9/1000)+(ROW()/100000)