Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting a single value across multiple cell groups | Excel Discussion (Misc queries) | |||
Counting groups of exact numbers in a huge list (column) | Excel Discussion (Misc queries) | |||
how do i view all groups under excel in google groups | Excel Discussion (Misc queries) | |||
Counting groups of exact case numbers w/letters in them. | Excel Discussion (Misc queries) | |||
Counting groups exact case numbers | Excel Discussion (Misc queries) |