Using rank in a range that changes

Tinhare

New Member
Joined
Apr 17, 2002
Messages
44
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.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
 
Upvote 0
Sorry Aladin, I did not see your post
This message was edited by eliW on 2002-04-29 03:23
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

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?

Disable AdBlock

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

Disable AdBlock Plus

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
Back
Top