Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Age band formula
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 ; ) |
#2
|
|||
|
|||
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 ; ) |
#3
|
|||
|
|||
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 ; ) |
#4
|
|||
|
|||
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 ; ) |
#5
|
|||
|
|||
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 ; ) |
#6
|
|||
|
|||
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 ; ) |
#7
|
|||
|
|||
I think the macro will be what you need, but you could try something like
=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. "Duke Carey" wrote: 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 ; ) |
#8
|
|||
|
|||
I have tried this but it comes up with an error - my formula was
=IF(Q:Q60,"60+",IF(Q:Q<20,"<20",IF(Q:Q20<=25,"20-25",IF(Q:Q25<=35,"25-35",IF(Q:Q35<=45,"35-45",IF(Q:Q45<=55,"45-55",IF(Q:Q55<=60,"55-60"))))))) but this also did not work - is it because it is incorrect? "bj" wrote: I think the macro will be what you need, but you could try something like =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. "Duke Carey" wrote: 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 ; ) |
#9
|
|||
|
|||
rather than
IF(Q:Q20<=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(Age60,"60+",if(Age55,"55-60",if(Age45,"45-50",if(Age35,"35-45",if(Age25,"25-35",if(age=20,"20-25","<20")))))) You can only have 7 nested if statements, this just makes it, "SCOOBYDOO" wrote: I have tried this but it comes up with an error - my formula was =IF(Q:Q60,"60+",IF(Q:Q<20,"<20",IF(Q:Q20<=25,"20-25",IF(Q:Q25<=35,"25-35",IF(Q:Q35<=45,"35-45",IF(Q:Q45<=55,"45-55",IF(Q:Q55<=60,"55-60"))))))) but this also did not work - is it because it is incorrect? "bj" wrote: I think the macro will be what you need, but you could try something like =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. "Duke Carey" wrote: 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 ; ) |
#10
|
|||
|
|||
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 +") "SCOOBYDOO" wrote: I have tried this but it comes up with an error - my formula was =IF(Q:Q60,"60+",IF(Q:Q<20,"<20",IF(Q:Q20<=25,"20-25",IF(Q:Q25<=35,"25-35",IF(Q:Q35<=45,"35-45",IF(Q:Q45<=55,"45-55",IF(Q:Q55<=60,"55-60"))))))) but this also did not work - is it because it is incorrect? "bj" wrote: I think the macro will be what you need, but you could try something like =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. "Duke Carey" wrote: 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 ; ) |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
This works great - thanks ; ) I can see that I am going to have to learn how
to use macros, gulp! "bj" wrote: rather than IF(Q:Q20<=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(Age60,"60+",if(Age55,"55-60",if(Age45,"45-50",if(Age35,"35-45",if(Age25,"25-35",if(age=20,"20-25","<20")))))) You can only have 7 nested if statements, this just makes it, "SCOOBYDOO" wrote: I have tried this but it comes up with an error - my formula was =IF(Q:Q60,"60+",IF(Q:Q<20,"<20",IF(Q:Q20<=25,"20-25",IF(Q:Q25<=35,"25-35",IF(Q:Q35<=45,"35-45",IF(Q:Q45<=55,"45-55",IF(Q:Q55<=60,"55-60"))))))) but this also did not work - is it because it is incorrect? "bj" wrote: I think the macro will be what you need, but you could try something like =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. "Duke Carey" wrote: 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 ; ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|