# 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.  Register To Reply

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).

=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  Register To Reply