Thanks:  0
Likes:  0

# Thread: Rank Data in Multiple Columns

1. ## Rank Data in Multiple Columns

B C D E F G
Jack \$156.00 Jack \$157.04 Jack \$99.12
Joe \$131.28 Joe \$150.79 Joe \$92.56
Mary \$128.93 Mary \$128.94 Mary \$66.06
Mark \$124.00 Mark \$101.31 Mark \$52.43

I need to rank the data in columns c,e,g and return the answer in column a next to Jack, Joe, Mary and so on. Here is an example of the formula that is not working for me, =rank(C2,C2:C5,F2:F5,I2:I5,0), it returns to tell me I have used to many instances. Thanks for the help.

2. ## Re: Rank Data in Multiple Columns

Hi, your post is a little unclear... what are you trying to return ? The rank of the SUM of each person ? If so I'd suggest creating another column to consolidate and rank that, ie:

Sheet13

 A B C D E F G H 1 1 Jack 156 Jack 157.04 Jack 99.12 412.16 2 2 Joe 131.28 Joe 150.79 Joe 92.56 374.63 3 3 Mary 128.93 Mary 128.94 Mary 66.06 323.93 4 4 Mark 124 Mark 101.31 Mark 52.43 277.74

 Cell Formula A1 =RANK(\$H1,\$H\$1:\$H\$4,0) H1 =SUM(C1:G1)

Excel tables to the web >> Excel Jeanie HTML 4

If that's not what you want please elaborate.

3. ## Re: Rank Data in Multiple Columns

each number represents sales for the month, I would like to rank each month individualy and have the rank show up beside each name in every month.

4. ## Re: Rank Data in Multiple Columns

to rank values in column C, put the following in A1
=RANK(C1,\$C:\$C)

you can then copy this formula down column A. I am still somewhat confused as to how you are trying to rank the 3 months in column a, or if A is just for the first month. If it is just for the first month, then this should work. If not, please explain further, and we can go from there.

5. ## Re: Rank Data in Multiple Columns

I want to know where Jack's first month ranks compared to all 3, and where his 2 month ranks compared to all 3, and so on for each person. Thanks for all the help.

6. ## Re: Rank Data in Multiple Columns

There should be columns inserted between each month.

How did Lasw10 capture the excel screen?

7. ## Re: Rank Data in Multiple Columns

One more thing - if you are trying to rank the current month sales for 1 person (i.e. Jack )against all people for all months, then I would suggest re-arranging data so that all are in the same 3 columns.

Just a thought . . . again, not sure what your goal is. Perhaps you can explain what the output should look like based on your example, and how you got to those results.(eg - Jack should have a rank of 2 because if you . . . Mary should have a rank of 1 because if you . . . )

8. ## Re: Rank Data in Multiple Columns

If you are inserting columns for each month and just ranking the person against the others for the month, then my first formula should work for the first month (in C), and then you will have to adjust values for the other columns where you insert.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•