Age band formula

S

SCOOBYDOO

Hi, in col A1 i have DOB, in A2 I have age attained, in A3 I need to know
which age band they fall into <20, 20-30, 30-35, 35-45, 45-55, 55-60 & 60+. I
have tried an if statement but it is not giving me the correct result
(probably due to an error in the formula)!! Any help would be appreciated,
thanks in advance ; )
 
D

Duke Carey

Create this table in two columns. For this example it starts in cell A1

0 <20
19 21-30
30 31-35
35 36-45
45 46-55
55 56-60
60 >60

Then use this formula

=VLOOKUP(age,$A$1:$B$7,2)
 
S

SCOOBYDOO

Hi, I'm sure that this works but my spreadsheet is sooooo large, that the
VLookup makes excel fall over - can you suggest another way?
 
D

Duke Carey

What do you mean, specifically, when you same it makes Excel 'fall over?'

I've got to believe that a VLOOKUP is the most efficient way of doing this
and that any other route will impose greater demands on Excel.
 
S

SCOOBYDOO

it is a large spreadsheet of over 64000 rows and 40 odd columns and it takes
a long while and then when I look at the task manager is states that excel is
not responding and the cpu usage is 100%!
 
D

Duke Carey

I hope one or more of the MVPs will weigh in with an alternate suggestion.

Barring help from one of the them, another option would be to put the calc
into a macro. The macro would first turn off calculations, then it would
cycle its way through all the rows of data, putting your age bands in place
as constants, then it would turn calculation back on.


This requires you to select all the cells with the Age Attained values firs
----------------------------------------------------------------------------------
Sub AgeBands()

Dim rng As Range
Dim intAge As Integer
Dim strBand As String

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For Each rng In Selection
intAge = rng.Value

Select Case intAge
Case Is < 20
strBand = "<20"
Case Is <= 30
strBand = "21-30"
Case Is <= 35
strBand = "31-35"
Case Is <= 45
strBand = "36-45"
Case Is <= 55
strBand = "46-55"
Case Is <= 60
strBand = "55-60"
Case Is > 60
strBand = ">60"
Case Else
strBand = "Unknown"
End Select
rng.Offset(0, 1).Value = strBand
Next
Application.Calculation = xlCalculationAutomatic


End Su
 
B

bj

I think the macro will be what you need, but you could try something lik
=choose(min(60,floor(age,5))/5,"<20","<20","<20","<20","20-30","20-30",30-35","35-45","45-55","55-60","60 +")

I assume what you want is greater than or equal to the lower number and less
than the upper number

Any time you have a spread sheet this size you should concider macros.
Every equation causes dependencies. Too many dependancies and there will be
full recalc (ie every cell recalculated) every time you change any cell Any
calculation which only is done once should be pasted as value over itself.
 
S

SCOOBYDOO

I have tried this but it comes up with an error - my formula was
=IF(Q:Q>60,"60+",IF(Q:Q<20,"<20",IF(Q:Q>20<=25,"20-25",IF(Q:Q>25<=35,"25-35",IF(Q:Q>35<=45,"35-45",IF(Q:Q>45<=55,"45-55",IF(Q:Q>55<=60,"55-60")))))))
but this also did not work - is it because it is incorrect?
 
B

bj

rather than
IF(Q:Q>20<=25,"
the format is incorrect.
assuming that Q refers to a single Cell at a Time.
It is not clear to me what would happen if someone was 20
to use the if Statements I would use.

=if(Age>60,"60+",if(Age>55,"55-60",if(Age>45,"45-50",if(Age>35,"35-45",if(Age>25,"25-35",if(age>=20,"20-25","<20"))))))

You can only have 7 nested if statements, this just makes it,
 
B

bj

I did have an error in it (I didn't double up all of the 10 year periods.)
Were you getting the wrong answer or an error message?

=choose(min(60,floor(age,5))/5,"<20","<20","<20","<20","20-30","20-30",30-35","35-45","35-45","45-55","45-55","55-60","60 +")
 
R

Ron Rosenfeld

Hi, in col A1 i have DOB, in A2 I have age attained, in A3 I need to know
which age band they fall into <20, 20-30, 30-35, 35-45, 45-55, 55-60 & 60+. I
have tried an if statement but it is not giving me the correct result
(probably due to an error in the formula)!! Any help would be appreciated,
thanks in advance ; )
Click to expand...


=HLOOKUP(A2,{0,20,30,35,45,55,60;"<20","20-29","30-34","35-44","45-54","55-59","60+"},2)




--ron
 
S

SCOOBYDOO

This works great - thanks ; ) I can see that I am going to have to learn how
to use macros, gulp!
 
Top