Ranking Value

lapta301

Well-known Member
Dear All

I am not sure if this is possible but I know that if it can be done then the gurus here will know how.

I have data including a heading in cells B3:H73 but this will extend. However, the final two columns will be headed "YTD Position" and "This Month".

I would like a ranking value in each one but do not want to actually sort the data.

The "YTD" Position comes from ranking the numerical value in the column headed "YTD" and the "This Month" value comes from ranking the column to the immediate left of the "YTD" column.

If any of the values are identical then the column headed "City" should be used as a tie break to put it in Alpha order.

I hope that I haven't made this sound more confusing than it really is.

As always many thanks to those who have a look.

Lapta301

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

jasonb75

Well-known Member
I'm Sure one of the MVP's will have a far better soultion that I haven't thought of, but this one works.

In the following, I have used Column C as "City", D as "YTD value", E as "Month value"
Please change columns to match this. \$ and correct order of the columns are critical for this to work correctly.

First 2 formula are to filter, so these go in the next columns asd can be hidden after

In I3 enter the following and fill down as needed. =COUNTIF(\$D:\$D,"<="&D3)&C3

Repeat in J3 with =COUNTIF(\$E:\$E,"<="&E3)&E3

Then hide those 2 columns

The next 2 formula should use the columns of the hidden cells, you only need to change these if you didn't use columns I and J for the first part.

In YTD position enter =COUNTIF(\$I:\$I,"<="&I3) and fill down
In This Month enter =COUNTIF(\$J:\$J,"<="&J3) and fill down

Hope this helps

JB

barry houdini

MrExcel MVP
Assuming cities are in B4:B73 and YTD value in F4:F73 then you can get ranking by YTD, tie-broken by City, like this in G4 copied down

=RANK(F4,F\$4:F\$73)+SUMPRODUCT(--(F\$4:F\$73=F4),--(B\$4:B\$73< B4))

Last edited:

jasonb75

Well-known Member
Good one Barry, that was the better solution I hadn't thought of

Just realised a mistake in my solution as well, it was ranking lowest value first, the first 2 formula should be > = and not < =

lapta301

Well-known Member
Barry & Jason

Many thanks for replying.

Jason I haven't tried yours yet.

Barry yours is fine except that I need it to rank in reverse. Basically it is ranking the time taken to achieve something so the lowest value in (actually) column G will be ranked as 1 with the highest number being the worse.

I could have a bash at amending it myself but would appreciate your input.

Many thanks again to you both

lapta301

barry houdini

MrExcel MVP
Just add a 1 as 3rd argument in the RANK function, i.e.

=RANK(G4,G\$4:G\$73,1)+SUMPRODUCT(--(G\$4:G\$73=G4),--(B\$4:B\$73< B4))

lapta301

Well-known Member
Thats it Barry, wonderful

Thank you so much

Lapta301

Replies
5
Views
702
Replies
3
Views
232
Replies
1
Views
188
Replies
1
Views
1K
Replies
3
Views
905

1,190,677
Messages
5,982,215
Members
439,769
Latest member
trungminh2802

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?

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

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