Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO CALCULATE AGES IN AN ENTIRE COLUMN??
Hullo all - in Excel 2007, I have one column with dob's and another with
test-dates - how to fill a 3rd column with people's ages as at their test dates? "YEARFRAC" does it for ONE cell at a time, but WONT accept e.g. =Yearfrac(e2:e741, s2:s741) to do the calculation for the ENTIRE column. I tried subtracting one column from another by highlighting all of the new column, entering "=s2:s741 - e2:e741" and then pressing ctrl-shift-enter, BUT (a) it shows whole days (and I cant see a formula to convert whole days to years for the ENTIRE column) and (b) it creates an "Array" and then I cant seem to delete any bad values from the data (where there is a dob missing). As a first-time user of Excel, I must say it's VERY obscure and counter-intuitive etc - I started using Word2007 immediatley with no problem, all seems self-explanatory! ANy help appreciated! Thankyou! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO CALCULATE AGES IN AN ENTIRE COLUMN??
You would usually use a formula specific to the first row, i.e. in row 2 use
the formula =YEARFRAC(E2,S2) then copy this down the column. To do that you place the cursor on the bottom right corner of the cell until you see a black +, this is the "fill handle". Left-click, hold down and drag as far as needed.........OR....if you have continuous data in an adjacent column, e.g. if you're imputting formula in T2, you can just double-click fill handle and formula will populate as far down as you have data. Note: if you just want the age in whole years try DATEDIF =DATEDIF(E2,S2,"y") "Meadow" wrote: Hullo all - in Excel 2007, I have one column with dob's and another with test-dates - how to fill a 3rd column with people's ages as at their test dates? "YEARFRAC" does it for ONE cell at a time, but WONT accept e.g. =Yearfrac(e2:e741, s2:s741) to do the calculation for the ENTIRE column. I tried subtracting one column from another by highlighting all of the new column, entering "=s2:s741 - e2:e741" and then pressing ctrl-shift-enter, BUT (a) it shows whole days (and I cant see a formula to convert whole days to years for the ENTIRE column) and (b) it creates an "Array" and then I cant seem to delete any bad values from the data (where there is a dob missing). As a first-time user of Excel, I must say it's VERY obscure and counter-intuitive etc - I started using Word2007 immediatley with no problem, all seems self-explanatory! ANy help appreciated! Thankyou! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO CALCULATE AGES IN AN ENTIRE COLUMN??
it worked! thanks heaps!
"daddylonglegs" wrote: You would usually use a formula specific to the first row, i.e. in row 2 use the formula =YEARFRAC(E2,S2) then copy this down the column. To do that you place the cursor on the bottom right corner of the cell until you see a black +, this is the "fill handle". Left-click, hold down and drag as far as needed.........OR....if you have continuous data in an adjacent column, e.g. if you're imputting formula in T2, you can just double-click fill handle and formula will populate as far down as you have data. Note: if you just want the age in whole years try DATEDIF =DATEDIF(E2,S2,"y") "Meadow" wrote: Hullo all - in Excel 2007, I have one column with dob's and another with test-dates - how to fill a 3rd column with people's ages as at their test dates? "YEARFRAC" does it for ONE cell at a time, but WONT accept e.g. =Yearfrac(e2:e741, s2:s741) to do the calculation for the ENTIRE column. I tried subtracting one column from another by highlighting all of the new column, entering "=s2:s741 - e2:e741" and then pressing ctrl-shift-enter, BUT (a) it shows whole days (and I cant see a formula to convert whole days to years for the ENTIRE column) and (b) it creates an "Array" and then I cant seem to delete any bad values from the data (where there is a dob missing). As a first-time user of Excel, I must say it's VERY obscure and counter-intuitive etc - I started using Word2007 immediatley with no problem, all seems self-explanatory! ANy help appreciated! Thankyou! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate the difference between ages, show as yy mm | Excel Worksheet Functions | |||
HOW DO I CALCULATE ACCURATE HUMAN AGES ON A SPREADSHEET ? | Excel Worksheet Functions | |||
How do I calculate ages in excel using date of birth & another dat | Excel Discussion (Misc queries) | |||
How do I automatically calculate ages in Xcel? | Excel Discussion (Misc queries) | |||
How do I calculate ages of employees in Excel? | Excel Worksheet Functions |