Solved
Excel countif problem involving age formula. urgent help needed.
Posted on 2003-03-31
I am creating a spread sheet to help me with my childminding accounts and I need to count the amount of children that I look after in one day that are over 8 years old. the table is set up as follows:
Date of birth Age
17/02/1986 17.00
24/01/2003 0.02
12/05/1997 5.00
29/09/1999 3.00
20/02/2002 1.00
30/12/1994 8.00
02/11/2000 2.00
I need to count the amount of children that are over 8 years old. if I try to work out something that is less that a value eg. <8 then the correct answer appears. but when it is greater than 8 or >8
the wrong amswer comes up. Although I have a formula that works out age in the age column and I know that this has something to do with it because when I deleat the formula from the blank cells then the correct answer appears. unfortunatuly I do need these extra rows because the amount of children that I look after veries from week to week. the age formula is as follows:
=IF(E161="","",IF(((YEAR(TODAY())-YEAR(E161))*12+MONTH(TODAY())-MONTH(E161))<=12,((YEAR(TODAY())-YEAR(E161))*12+MONTH(TODAY())-MONTH(E161))/100,IF(MONTH(TODAY())>MONTH(E161),YEAR(TODAY())-YEAR(E161),IF(AND(MONTH(TODAY())=MONTH(E161),DAY(TODAY())>=DAY(E161)),YEAR(TODAY())-YEAR(E161),(YEAR(TODAY())-YEAR(E161))-1))))
cell E161 reffures to the date of birth of the person. this is a complicted formula so heres a breakdown of the formula:
7 =IF(E41="","",
if the cell (E41) has nothing in it, then display nothing
7 IF(((YEAR(TODAY())-YEAR(E41))*12+MONTH(TODAY())-MONTH(E41))<=12,((YEAR(TODAY())-YEAR(E41))*12+MONTH(TODAY())-MONTH(E41))/100
If (the year of today - the year in cell (E41) multiplied by 12 + the month of today month in cell (E41) the months old is less that 12 then do the same calculation and divide by 100 to produce a decimal answer to represent months old if younger than 1 year.
7 IF(MONTH(TODAY())>MONTH(E41),YEAR(TODAY())-YEAR(E41)
If the month of today is greater that the month in cell (E41) then take the year of today from the year in cell (E41)
7 IF(AND(MONTH(TODAY())=MONTH(E41),DAY(TODAY())>=DAY(E41)),YEAR(TODAY())-YEAR(E41)
If the month of today = the month in cell (E41) and the day today = the day in cell (E41) then take the year of today from the year in cell (E41)
7 (YEAR(TODAY())-YEAR(E41))-1))))
Otherwise do the year of today the year in cell (E41) 1
can you please help. urgently needed, but I don't have very many points.
Thanks
Scotton