Ranking Value

[lapta301]

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

 

[jasonb75]

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]

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))

 

[jasonb75]

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]

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]

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]

Thats it Barry, wonderful

Thank you so much

Lapta301