Rank Data in Multiple Columns

JWFAKOURI

New Member
Joined
Feb 10, 2006
Messages
16
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.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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:

Excel Workbook
ABCDEFGH
11Jack156Jack157.04Jack99.12412.16
22Joe131.28Joe150.79Joe92.56374.63
33Mary128.93Mary128.94Mary66.06323.93
44Mark124Mark101.31Mark52.43277.74
Sheet13


If that's not what you want please elaborate.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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 . . . )
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

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

Which adblocker are you using?

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
Back
Top