RANK Formula

JBWalker

New Member
Joined
Feb 17, 2020
Messages
2
Office Version
2010, 2007
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?
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,115
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

New Member
Joined
Feb 17, 2020
Messages
2
Office Version
2010, 2007
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
 

Attachments

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,115
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

Board Regular
Joined
Sep 25, 2012
Messages
191
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,095,975
Messages
5,447,659
Members
405,460
Latest member
stuartbennett

This Week's Hot Topics

Top