Software

Ask something
Discussion Locked

Excel - Rank Formula for Multiple Fields

0 helpful
5 replies

by jrowl41280aol

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

OK, here's the issue.

I need to use a ranking formula in Excel that will do 1 Rank for 3 seperate fields. I'm not an Excel expert, so the formula I'm using works on individual fields, but when I use it for all fields I have 2 phantom ranks.

Here's the basics

Right now it looks like this

A B

22.00% 8
41.00% 2
52.00% 1

37.00% 5
12.00% 9
18.00% 10

29.00% 6
31.00% 7
10.00% 11

As you can see it skips 2 fields.

Now what you don't seein the spaces between the 2 groups there is 1 blank line, and 1 total for that grouping.

Now this is the short version because the rankings covers 32 percentages.

Here's the exact formula I'm using:

=RANK(G4,G4:G13:G16:G26:G29:G39,0)

What am I doing wrong?
What do I need to change?
What formula will work for what I need it to do and get rid of those 2 phantom ranks for me?

Thanks,
Jeff

5 replies

by thamer

What you need is to fix the groups you want to rank their entrie by adding the $ sign ...
Your formula should be like this ...
=RANK(G4,$G$4:$G$13:$G$16:$G$26:$G$29:$G$39)

Now when you copy the formula to the next cells, the rank function will always look in those 3 groups.

I applied this formula to your data ... and here are the results...

G H
22.00% 6
41.00% 2
52.00% 1

37.00% 3
12.00% 8
18.00% 7

29.00% 5
31.00% 4
10.00% 9

I hope this helps.

by thamer

As I understand it, the last formula you are using is doing excatly what you want it to do. You have 32 entries divided into three cell groups and the function is ranking each entry from 1 to 32... execluding the Total % and the blank entries.

If this not what you want, then will you please re-type what you excatly want to do with the data.

by jrowl41280aol

I tried what you said and it didn't work, it should have because I researched some websites and that was the answer they had as well. So here's the longer version of the data:

Column Column
G H
Row 4 12.30% 24
Row 5 11.35% 27
Row 6 10.09% 31
Row 7 14.17% 19
Row 8 16.22% 12
Row 9 12.86% 22
Row 10 15.79% 15
Row 11 16.59% 10
Row 12 16.18% 13
Row 13 6.61% 33
Row 14 13.35% <--This is a total % of the abovegroup and should not be included in the range
Row 15 <>
Row 16 11.94% 25
Row 17 21.09% 5
Row 18 14.88% 17
Row 19 10.94% 28
Row 20 13.79% 20
Row 21 10.61% 29
Row 22 17.44% 7
Row 23 25.89% 1
Row 24 16.26% 11
Row 25 10.56% 30
Row 26 23.60% 3
Row 27 14.81% <--This is a total % of the above group and should not be included in the range
Row 28 <>
Row 29 11.86% 26
Row 30 4.78% 34Row 31 16.02% 14
Row 32 17.31% 8
Row 33 18.57% 6
Row 34 15.24% 16
Row 35 24.73% 2
Row 36 16.88% 9
Row 37 21.82% 4
Row 38 12.82% 23
Row 39 8.27% 32 =RANK(G39,G4:G13:G16:G26:G29:G39,0)
Row 40 15.03% <--This is a total % of the above group and should not be included in the range

The formula I'm using in the H field is:
=RANK(G4,G4:G13:G16:G26:G29:G39,0)
and I am sequentially raising the first G number as I go down the line....

Hopefully someone has an idea to help with this.

This discussion is closed to new replies.