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

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)

"SCOOBYDOO" wrote:

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

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?

"Duke Carey" wrote:

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)

"SCOOBYDOO" wrote:

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

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.

"SCOOBYDOO" wrote:

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?

"Duke Carey" wrote:

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)

"SCOOBYDOO" wrote:

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

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%!

"Duke Carey" wrote:

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.

"SCOOBYDOO" wrote:

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?

"Duke Carey" wrote:

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)

"SCOOBYDOO" wrote:

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

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 first
----------------------------------------------------------------------------------
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 Sub
----------------------------------------------------------------------------------


"SCOOBYDOO" wrote:

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%!

"Duke Carey" wrote:

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.

"SCOOBYDOO" wrote:

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?

"Duke Carey" wrote:

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)

"SCOOBYDOO" wrote:

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

On Thu, 9 Jun 2005 05:02:05 -0700, SCOOBYDOO
wrote:

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


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




--ron