+ Reply to Thread
Results 1 to 2 of 2

Working with Ranges

  1. #1
    Registered User
    Join Date
    04-27-2005
    Posts
    5

    Working with Ranges

    Ok, I have a small project that I need to do, and I can't figure out a function that can help me.
    I have a database of about 15,000 records, and in this data is a Column called Square Meters. Next to this column is another called Alottment. I need to be able to put in the Alottment column the number of allotments based on the range that is in the Square meters column. So for example, A2 = 20, I need to be able to analyze that and put "5" in the Allotments field.

    The following is my range. I could go through and do this by hand, but on 15,000 records, that would be a little mundane and time consuming. Is this something I can create with a function, or do I need to brush up on my VB skills and do it through VBA.

    Range = # of allotments
    12 - 23 = 5
    24 - 35 = 10
    36 - 47 = 15
    48 - 59 = 20
    60 - 71 = 25
    72 - 83 = 30
    84 and up = 35

    Any help/guidance would be greatly appreciated.

  2. #2
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile

    Good evening thebside

    I can't think of a standard Excel function that will do this quickly and cleanly, so I've written one that should do the job. Copy this into an empty code module:

    Function allot(a As Integer)
    Application.Volatile True
    If a < 12 Then allot = 0
    If a >= 12 And a <= 23 Then allot = 5
    If a >= 24 And a <= 35 Then allot = 10
    If a >= 36 And a <= 47 Then allot = 15
    If a >= 48 And a <= 59 Then allot = 20
    If a >= 60 And a <= 71 Then allot = 25
    If a >= 72 And a <= 83 Then allot = 30
    If a > 84 Then allot = 35
    End Function

    As you can see it's pretty straight forward - nothing too sophisticated (you didn't state what would happen with allotments <12 - you may not have any but it's just to catch errors).

    As for the syntax, let's follow your example. A2 is 20 and your allotments column is B. B20 should read:
    =allot(A20)
    and the result will be 5.

    Let me know how you go on - if you have any errors I'll try and help iron them out.

    HTH

    DominicB

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1