# RANK Formula

#### JBWalker

##### New Member
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?

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### mrshl9898

##### Well-known Member
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
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

• Excel spreadsheet snap.PNG
108 KB · Views: 6

#### mrshl9898

##### Well-known Member
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
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)

Replies
3
Views
120
Replies
6
Views
120
Replies
3
Views
181
Replies
1
Views
203
Replies
8
Views
582

Threads
1,127,481
Messages
5,625,015
Members
416,065
Latest member
meiravmeron

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back