Wow! This is exactly what I wanted to achieve. Thak you.

I thought I understood Excel. But your formula is beyond me. Can you
explain how this formula works?

In article ,
says...
Is this something like what you want to achieve:

Store | Segm | Perms
--------------------
Store1 | A | 1
Store2 | A | 2
Store3 | A | 3
Store4 | A | 4
Store5 | B | 5
Store6 | B | 6
Store7 | B | 7
Store8 | B | 8
Store9 | C | 9
Store10| C | 10
Store11| C | 11
Store12| C | 12

Put this data in cells A2:C13

i.e. 'Store1' will be A2, 'A' in B2, '1' in C3 etc.

Put this formula in cell D2 and 'array enter' it (i.e.
ctrl+shift+enter):

=1+SUM(--(($B$2:$B$13=B2)*$C$2:$C$13C2))

Copy the formula down to D13.

The values in D should be the rank as regards performance within the
store's segment e.g. Store1 is ranked 4 out of 4 because it has the
lowest performance value of the four segment A stores.

The values in column D should stay the same if you sort the rows into
another order.

--

David Crane wrote in message nk.net...
Thank you for this idea. My problem is that I am constrained to a one
file Excel solution, so I cannot use this idea for my current effort.
Any thoughts on how to do this within Excel?

Thanks

In article ,
says...
First off you should not store this data in a new column in your table.
It should be calculated in a query.

I would calculate the new column to be a percent of the top performer in that RetailSegment