Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Using rank in a range that changes

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    England
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,026
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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

  3. #3
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Sorry Aladin, I did not see your post



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

  4. #4
    New Member
    Join Date
    Apr 2002
    Location
    England
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •