counting age groups

[chedd via OfficeKB.com]

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

 

[Ron Coderre]

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)

 

[Gav123]

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]

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

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

 

[Ron Coderre]

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
solution.
Thanks for the reply

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

 

[chedd via OfficeKB.com]

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

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]