View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
onedaywhen onedaywhen is offline
external usenet poster
 
Posts: 459
Default Rankings within a database

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

You can see this by creating an new blank access database

Add the following table

tblRetailStore
------------------
RetailStoreID (autonum) <--- PrimaryKey
NumericInfo (number)
RetailSegment (text)


Add some values to that table then create a new query

use the following formula to calculate the RankByRetailSegment

RankByRetailSegment: [tblRetailStore].[NumericInfo]/DMax("NumericInfo","tblRetailStore","RetailSegment ='" & [tblRetailStore]![RetailSegment] & "'")


Or just cut and paste this SQL Statement into the SQL design view of the query

SELECT tblRetailStore.RetailStoreID, tblRetailStore.NumericInfo, tblRetailStore.RetailSegment, [tblRetailStore].[NumericInfo]/DMax("NumericInfo","tblRetailStore","RetailSegment ='" & [tblRetailStore]![RetailSegment] & "'") AS RankByRetailSegment
FROM tblRetailStore
ORDER BY tblRetailStore.RetailSegment, [tblRetailStore].[NumericInfo]/DMax("NumericInfo","tblRetailStore","RetailSegment ='" & [tblRetailStore]![RetailSegment] & "'") DESC;

HTH
-Merk


David 11/25/03 11:07AM

I have a database that includes both numeric and categorization
information about a set of retail stores. I want to create one
additional column in the database that represents the rank of each store
within its segment (i.e. the ranking for a numeric performance field
within the store's segment, as defined by one of the categorization
fields). I want the ranking to be in any of three possible forms -
either a numeric rank, or a percentile, or simple categorization (e.g.,
Top 20%, Bottom 80%).

I have not been able to figure out any way to do this other than by
manual, brute force - creating a page for each segment, calculating
rankings there, and then bringing results back to the main database by
formula. I have tried the percentile function - which appears to work on
an entire database, even if the database is filtered (not the way sums
and averages work). I have also hunted for database functions, but have
not found anything to do what I need.