Using rank in a range that changes

[Tinhare]

I want to be able to rank stores sales growth by region.
I have a sheet that has 4 columns of data
Column A contains numbers from 1 to (number of stores in that region)
Column B contains the store number
Column C contains the rank for the store within the region based on the store growth
Column D contains the store growth
Each region is repeated under the other region down the sheet.
The problem is that if a new store is inserted it will usually be at the end of the region as the store number will be higher. This then throws out the ranking as they do not take into account for the new store. Also a store could close and be deleted and once again if it is at the end of the rank it creates an error. Also the regions are in units of 5 starting at 10. ie 10,15,20,25 etc etc. Regions 10 & 15 are ranked togather as are 20 & 25 etc.

How can I get it to change the ranges based on the change in the number of stores within the regions?

Thanks,
Alan.

[Aladin Akyurek]

On 2002-04-28 19:54, Tinhare wrote:
I want to be able to rank stores sales growth by region.
I have a sheet that has 4 columns of data
Column A contains numbers from 1 to (number of stores in that region)
Column B contains the store number
Column C contains the rank for the store within the region based on the store growth
Column D contains the store growth
Each region is repeated under the other region down the sheet.
The problem is that if a new store is inserted it will usually be at the end of the region as the store number will be higher. This then throws out the ranking as they do not take into account for the new store. Also a store could close and be deleted and once again if it is at the end of the rank it creates an error. Also the regions are in units of 5 starting at 10. ie 10,15,20,25 etc etc. Regions 10 & 15 are ranked togather as are 20 & 25 etc.

How can I get it to change the ranges based on the change in the number of stores within the regions?

Thanks,
Alan.

I'd like to see some sample data. If you inclined to post a sample, please use the following procedure:

Activate an empty cell, type =, select 15 rows of relevant data including the labels/column headings, hit F9, Copy what you see, and post the copied bit in the follow up.

In the meantime, I'd suggest using dynamic name ranges which you feed to the RANK formula instead of a definite range or a name that refers to a definite range.

I'll assume in what follows that your data is in a worksheet called SalesData. And the column A is really of numeric type and the sales data start at row 2.

Activate the option Insert|Name|Define.
Enter NumRecs as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=MATCH(9.99999999999999E+307,SalesData!$A:$A)

Activate Add. (Don't leave yet the Define Name window.)

Enter DataRecs as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=NumRecs-(ROW(SalesData!$A$2)-1)

Activate Add. (Don't leave yet the Define Name window.)

Enter Fstores as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=OFFSET(SalesData!$A$2,0,0,DataRecs,1)

Activate Add. (Don't leave yet the Define Name window.)

Enter StoreNums as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=OFFSET(SalesData!$B$2,0,0,DataRecs,1)

Activate OK.

I leave to you to create a dynamic name range for the growth data in column D.

The purpose of the above exercise is that you will be able to add to or to delete from the data area any number of records you need.

Aladin

[eliW]

Sorry Aladin, I did not see your post



[ This Message was edited by: eliW on 2002-04-29 03:23 ]

[Tinhare]

Thanks Aladin,

I thought I would have to do something like that but you have given me some ideas and I will now scurry off to my little cave and try and figure things out. If I'm still stuck I'll be sure to let you know.

Cheers,
Alan.