counting age groups
 

I having issues in picking up certain ages from the formula i am using. The
problem the formula is fine until it has to count the value showing within
the formula. i.e if i have to count age 20 it doesn't seem to pick this up or
with the age 18, but within the range there is no issue. The formula i am
using is =SUM(COUNTIF(F27:F48,{"<18","<=20"})*{-1,1}). I have also tried to
move the = sign with no effect. With each specific age range i am having the
same problem. I hope some is able to help as it would most apprciated.

Chedd

--
Message posted via http://www.officekb.com

counting age groups
 

The formula is working fine with integer values (18, 19, 20, etc).
That suggests a data issue. What values are contained in F27:F48?

--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)


"chedd via OfficeKB.com" <u18187@uwe wrote in message
news:78caad219710a@uwe...
I having issues in picking up certain ages from the formula i am using.
The
problem the formula is fine until it has to count the value showing within
the formula. i.e if i have to count age 20 it doesn't seem to pick this up
or
with the age 18, but within the range there is no issue. The formula i am
using is =SUM(COUNTIF(F27:F48,{"<18","<=20"})*{-1,1}). I have also tried
to
move the = sign with no effect. With each specific age range i am having
the
same problem. I hope some is able to help as it would most apprciated.

Chedd

--
Message posted via http://www.officekb.com

counting age groups
 

Hi Chedd,

I have just tried your formula (Excel 2003)and seems to be working fine when
array entered Ctrl+Shift+Enter

Regards,

Gav.

"chedd via OfficeKB.com" wrote:

I having issues in picking up certain ages from the formula i am using. The
problem the formula is fine until it has to count the value showing within
the formula. i.e if i have to count age 20 it doesn't seem to pick this up or
with the age 18, but within the range there is no issue. The formula i am
using is =SUM(COUNTIF(F27:F48,{"<18","<=20"})*{-1,1}). I have also tried to
move the = sign with no effect. With each specific age range i am having the
same problem. I hope some is able to help as it would most apprciated.

Chedd

--
Message posted via http://www.officekb.com

counting age groups
 

Hi the formula to obtain the value in F27:F48 is =INT(A27-E27)/365.25. Do
think this could be causing the conflict. if so what could be the solution.
Thanks for the reply

Ron Coderre wrote:
The formula is working fine with integer values (18, 19, 20, etc).
That suggests a data issue. What values are contained in F27:F48?

--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

I having issues in picking up certain ages from the formula i am using.
The
[quoted text clipped - 9 lines]

Chedd

--
Message posted via http://www.officekb.com

counting age groups
 

That formula might be a problem because it returns decimal fractions of a
year.

Example:
E27: 06/18/1987
A27: 09/18/2007

This formula: =INT(A27-E27)/365.25
returns 20.2518822724162

So...even though that person is actually 20 years old...he will not be
picked up by your formula because he is "technically" older than 20.

Try using this age calculation:
=DATEDIF(E27,A27,"Y")
For instructions on using the UNdocumented DATEDIF function, see Chip
Pearson's website:
http://www.cpearson.com/excel/datedif.htm

Your formula, even if you'd constructed it more properly:
=INT((A27-E27)/365.25)
would still return some erroneous values.

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"chedd via OfficeKB.com" <u18187@uwe wrote in message
news:78cb2d5e0a512@uwe...
Hi the formula to obtain the value in F27:F48 is =INT(A27-E27)/365.25. Do
think this could be causing the conflict. if so what could be the
solution.
Thanks for the reply

Ron Coderre wrote:
The formula is working fine with integer values (18, 19, 20, etc).
That suggests a data issue. What values are contained in F27:F48?

--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

I having issues in picking up certain ages from the formula i am using.
The
[quoted text clipped - 9 lines]

Chedd

--
Message posted via http://www.officekb.com

counting age groups
 

Great as you stated it was a conflict between formulas. Thank you for your
time and help it is most appreciated.

Ron Coderre wrote:
That formula might be a problem because it returns decimal fractions of a
year.

Example:
E27: 06/18/1987
A27: 09/18/2007

This formula: =INT(A27-E27)/365.25
returns 20.2518822724162

So...even though that person is actually 20 years old...he will not be
picked up by your formula because he is "technically" older than 20.

Try using this age calculation:
=DATEDIF(E27,A27,"Y")
For instructions on using the UNdocumented DATEDIF function, see Chip
Pearson's website:
http://www.cpearson.com/excel/datedif.htm

Your formula, even if you'd constructed it more properly:
=INT((A27-E27)/365.25)
would still return some erroneous values.

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

Hi the formula to obtain the value in F27:F48 is =INT(A27-E27)/365.25. Do
think this could be causing the conflict. if so what could be the
[quoted text clipped - 16 lines]

Chedd

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200709/1