# Working with Ranges

1. ## 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. 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

##### Users Browsing this Thread

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

#### 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