?
Solved

Excel countif problem involving age formula. urgent help needed.

Posted on 2003-03-31
7
Medium Priority
?
566 Views
Last Modified: 2008-03-04
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

0
Comment
Question by:Scotton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 16

Expert Comment

by:sebastienm
ID: 8242072
>>> if I try to work out something that is less that a value eg. <8 then the correct answer appears
What do you use to count?
say age is in F2:F100
=COUNTIF(F2:F100,">8")

The above assumes your age formula works fine. Does your age formula returns some wrong values?

Regards,
Sebastien
0
 

Author Comment

by:Scotton
ID: 8244425
Date of birth     Age   Hours Stayed
17/02/1986     17.00         3.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 fogot to mention this but I have an hours stayed column and only when in this column the value is greater that 0 i want to count the amount of children. so in the abouve example I want it to count the amount of children that have stayed more than 0 hours and that are greater than 8 years old

Scotton
0
 
LVL 16

Expert Comment

by:sebastienm
ID: 8246627
ok.. so in that case, you can use Array Formula.
The important thing about arraay fromula is that, after editing the formula, to validate it you have to press CTRL+SHIFT+ENTER instead of the regular ENTER.
Now, say Age is in column F2:F10 and Hours in column G2:G10 :
   = SUM( (F2:F10>8) * (G2:G10>0) * 1)
     --> age greater than 8, hours greater than 0

Just don't forget to press CTRL+SHIFT+ENTER instead of the regular ENTER.

Sebastien
0
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 

Author Comment

by:Scotton
ID: 8248558
Thank tou sebastienm this formula works to an exstent but unfortunatly it also counts the blank cells as well. I use a formula to work out hours stayed when given the hours arrive and departed. this is the formula:

=IF($W42="","",IF(G42="",0,(H42-G42+(H42<G42))*24))

Again I have used the black cell trick where if a particular cell is blank then leave the cell blank. this may be the source of the problem. but how do I get rid of this problem. $w42 is an irrelevent cell as far as this problem is concerned. you could just use letters to represent a value. g42 is the hours arrive and h42 is the hours departed. As far as I can see it appears the these formulas that I have used count a blank cell as a value greater that 0. when in fact it should be nothing. If the hours stayed formula is deleated and thn the cell is left blank, then it works. Although if it just says 0 it works. but I want the blank cells there so that it looks more presentable because I am also designing this system for a friend of mine who is crap when it comes to computers.

You must be pissed off with this by now but if you find that this problem is impossible to solve then please reply.

Scotton
0
 
LVL 16

Accepted Solution

by:
sebastienm earned 580 total points
ID: 8248638
>>> You must be pissed off with this by now
No, not a problem :-)

You can use the same idea to remove the blanks:
  = SUM( (F2:F10>8) * (G2:G10>0) * (G2:G10<>"") * 1)
and press CTRL+SHIFT+ENTER to validate the entry.

Here, i just added the (G2:G10<>"") part.

Regards,
Sebastien
0
 

Author Comment

by:Scotton
ID: 8252567
Thanks Sebastienm, your help was geratly appreciated. you did an excellent job.

Form Scotton
0
 
LVL 16

Expert Comment

by:sebastienm
ID: 8253787
Thank you very much, Scotton.

Glad i could help,
Sebastien
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let’s list some of the technologies that enable smooth teleworking. 
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Using Adobe Premiere Pro, the viewer will learn how to set up a sequence with proper settings, importing pictures, rendering, and exporting the finished product.
The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question