Tricky Excel formula problem..?

Question

I have a list of DOBs in one column.
eg
30/11/1939
28/10/1943
11/12/1943
14/06/1945
19/09/1946
... etc

And there is a name column next to it. I need to use a formula to calculate each persons age, then I need to list all ages and the number of them.

If you need me to try and explain it differently just ask.

Answer 1

So you have columns

Name1 DOB1
Name2 DOB2
Name3 DOB2
...
etc.

I'm going to assume that you want to calculate each person's age as of a specific date. Enter that date in cell A1 (or if you put it in another cell just replace A1 with that cell address).

Now add a third column to the right of the DOB column, starting in the same row as DOB1, with the following formula.

=YEARFRAC($A$1,DOB1,1)

where DOB1 is the address of DOB1 - notice that A1 is surrounded by $ which means it is an absolute address. DOB1 is a relative address so there are no $. If DOB1 is in cell E3 then enter E3. If the date in A1 is actually in B4 neter it as $B$4.

The argument 1 at the end is to set the date basis correctly.

Copy this formula down the column for each row in your list. For row j the formula should be

=YEARFRAC($A$1,DOBj,1)

where DOBj is the cell address for the jth DOB in the list.

Hope that helps.

===============

Your last sentence implies you want to have a frequency of ages. You'll need to modify your previous formula to make it useful.

=INT(YEARFRAC($A$1,DOB1,1))

or

=ROUND(YEARFRAC($A$1,DOB1,1),0)

Either you want an age of 17.6 to be truncated to 17 (the INT function) or rounded to 18 (the ROUND function).

Once you do this, create a list of ages

0
1
2
...
100

or whatever.

Use the function COUNTIF()

=COUNTIF(range of DOBs,Agej)

If you need help with the ranges let me know.

Answer 2

The first part is relatively easy, simply put the date that you want to calculate ages on in a separate cell and do *that cell*-the reference to the DoB, then truncate if you want full years.

I'm not sure what you mean by list all ages and the number of them - is it a list like 3 people are age 40, 5 people are age 39, no one is 38 etc.. ? I think you need to play around with Countif or similar, depending on what spreadsheet software you're using

Answer 3

OK

You are using an international date format, and my Excel is setup in US format, so I am not sure if your's will work like this will -- however, the concept is the same

I placed the dates in mm/dd/yyyy format and them placed a cell next to the name with the following formula

TODAY()-B11 (where b11 was the first one in your list).
The other key is to format this cell with a custom format of date yy.

I got 68 for the first one, 62 for the second, 64 for the third, etc.

Now if you have the name next to the DOB it would look like this:

11/30/1939 ..... Joe ...... 68
10/28/1943...... Mary......62

For the second part , I think you will need to do some sort of count.

Hope that helps.

Answer 4

If we assume that column A is the DOB column, and column C you want to calculate the age in years, then here is what you would want in cell C2

=INT(TODAY()-A2)/365

You would want to format that cell to display a single digit number.

If what you want is to follow the last entry with something like:

AGE COUNT
55...............3
56...............4
57...............0
58..............1

I think it is not possible without writing a program in Visual Basic. It's not a huge challenge for someone that knows VB, but it's more than I could explain here.

Answer 5

i'm undecided I one hundred% understand what you're attempting to do, yet i think of you do choose to apply a study formulation. i could use vlookup(the fee you're in seek of for which i think of you pronounced one million, selection of the columns that has the variety you're looking up and the column you elect to tug from ie and b, the variety 2 which skill you as quickly as you come across the variety one million in column a it is going to positioned the fee in the 2nd column, real or fake next in fact skill you the two choose the precise variety to verify to tug the fee from column 2 or the closest variety to one million that column one million has and it will populate column bs fee. In excel it may appear as if this vlookup(one million,a1:b10,2,real)