Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countifs with same reference?
I am attempting to create age ranges using the COUNTIFS formula. However,
the numbers that is gives back to me are wrong. The formula: =COUNTIFS(H:H,"<=40",H:H,"=25") should have a total of 50. Instead it calculates 49. Is it a problem when the references are the same range in the database? The column of ages is also a formula (date of birth - date of entry). Could this be the error? I have tried all sorts of combinations in the number and have gone over my data extensively. It seems to be an error in the COUNTIFS formula. But, I am hoping that I am just using it incorrectly. Or, is there another formula that I can use to calculate age ranges? Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countifs with same reference?
Hi
Nothing wrong with the formula. Are you sure than 50 entries falls into the range ? Regards, Per "CEBeau" skrev i meddelelsen ... I am attempting to create age ranges using the COUNTIFS formula. However, the numbers that is gives back to me are wrong. The formula: =COUNTIFS(H:H,"<=40",H:H,"=25") should have a total of 50. Instead it calculates 49. Is it a problem when the references are the same range in the database? The column of ages is also a formula (date of birth - date of entry). Could this be the error? I have tried all sorts of combinations in the number and have gone over my data extensively. It seems to be an error in the COUNTIFS formula. But, I am hoping that I am just using it incorrectly. Or, is there another formula that I can use to calculate age ranges? Thank you. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countifs with same reference?
Yes. I counted myself and had another person verify.
"Per Jessen" wrote: Hi Nothing wrong with the formula. Are you sure than 50 entries falls into the range ? Regards, Per "CEBeau" skrev i meddelelsen ... I am attempting to create age ranges using the COUNTIFS formula. However, the numbers that is gives back to me are wrong. The formula: =COUNTIFS(H:H,"<=40",H:H,"=25") should have a total of 50. Instead it calculates 49. Is it a problem when the references are the same range in the database? The column of ages is also a formula (date of birth - date of entry). Could this be the error? I have tried all sorts of combinations in the number and have gone over my data extensively. It seems to be an error in the COUNTIFS formula. But, I am hoping that I am just using it incorrectly. Or, is there another formula that I can use to calculate age ranges? Thank you. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countifs with same reference?
If there are any values that are 25 or 40, perhaps you should use INT(25) and
INT(40), just to ensure there is no rounding issues? -- John C "CEBeau" wrote: Yes. I counted myself and had another person verify. "Per Jessen" wrote: Hi Nothing wrong with the formula. Are you sure than 50 entries falls into the range ? Regards, Per "CEBeau" skrev i meddelelsen ... I am attempting to create age ranges using the COUNTIFS formula. However, the numbers that is gives back to me are wrong. The formula: =COUNTIFS(H:H,"<=40",H:H,"=25") should have a total of 50. Instead it calculates 49. Is it a problem when the references are the same range in the database? The column of ages is also a formula (date of birth - date of entry). Could this be the error? I have tried all sorts of combinations in the number and have gone over my data extensively. It seems to be an error in the COUNTIFS formula. But, I am hoping that I am just using it incorrectly. Or, is there another formula that I can use to calculate age ranges? Thank you. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countifs with same reference?
John C <johnc@stateofdenial wrote...
If there are any values that are 25 or 40, perhaps you should use INT(25) and INT(40), just to ensure there is no rounding issues? .... When would INT(25) and 25 or INT(40) and 40 not be the same? Or do you mean wrap the H:H references inside INT? If so, can't be done in COUNTIFS, which requires that its odd-numbered arguments (1st, 3rd, 5th, etc) be range references. If the OP's problem is rounding, then the solution would be =COUNTIFS(H:H,"=24.5",H:H,"<40.5") And, no, there's no roundoff error with .5 when the absolute value of the integer part is less than 10^14. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countifs with same reference?
I was suggesting if his number in his data set showed 25 or 40 (but in
actuality is not quite 25 or 40), then check to see if 25=INT(the cell that shows 25). -- John C "Harlan Grove" wrote: John C <johnc@stateofdenial wrote... If there are any values that are 25 or 40, perhaps you should use INT(25) and INT(40), just to ensure there is no rounding issues? .... When would INT(25) and 25 or INT(40) and 40 not be the same? Or do you mean wrap the H:H references inside INT? If so, can't be done in COUNTIFS, which requires that its odd-numbered arguments (1st, 3rd, 5th, etc) be range references. If the OP's problem is rounding, then the solution would be =COUNTIFS(H:H,"=24.5",H:H,"<40.5") And, no, there's no roundoff error with .5 when the absolute value of the integer part is less than 10^14. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countifs with same reference?
Unless that you are confident that any rounding is downwards, you'd be
better withy ROUND rather than INT. If the cell contains 24.99999999999, INT will return 24, not 25. -- David Biddulph "John C" <johnc@stateofdenial wrote in message ... I was suggesting if his number in his data set showed 25 or 40 (but in actuality is not quite 25 or 40), then check to see if 25=INT(the cell that shows 25). -- John C "Harlan Grove" wrote: John C <johnc@stateofdenial wrote... If there are any values that are 25 or 40, perhaps you should use INT(25) and INT(40), just to ensure there is no rounding issues? .... When would INT(25) and 25 or INT(40) and 40 not be the same? Or do you mean wrap the H:H references inside INT? If so, can't be done in COUNTIFS, which requires that its odd-numbered arguments (1st, 3rd, 5th, etc) be range references. If the OP's problem is rounding, then the solution would be =COUNTIFS(H:H,"=24.5",H:H,"<40.5") And, no, there's no roundoff error with .5 when the absolute value of the integer part is less than 10^14. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countifs with same reference?
John C <johnc@stateofdenial wrote...
I was suggesting if his number in his data set showed 25 or 40 (but in actuality is not quite 25 or 40), then check to see if 25=INT(the cell that shows 25). .... Clarification good, but approach still flawed. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countifs with same reference?
ding ding ding, you now get it!
If he shows a value of 25 in a cell, but in reality it is 24.999999999, then OBVIOUSLY 25 will not be equal to INT(25). This was testing the data to ensure that any values that show 25, and any values that show 40, are truly 25 and 40 respectively. -- John C "David Biddulph" wrote: Unless that you are confident that any rounding is downwards, you'd be better withy ROUND rather than INT. If the cell contains 24.99999999999, INT will return 24, not 25. -- David Biddulph "John C" <johnc@stateofdenial wrote in message ... I was suggesting if his number in his data set showed 25 or 40 (but in actuality is not quite 25 or 40), then check to see if 25=INT(the cell that shows 25). -- John C "Harlan Grove" wrote: John C <johnc@stateofdenial wrote... If there are any values that are 25 or 40, perhaps you should use INT(25) and INT(40), just to ensure there is no rounding issues? .... When would INT(25) and 25 or INT(40) and 40 not be the same? Or do you mean wrap the H:H references inside INT? If so, can't be done in COUNTIFS, which requires that its odd-numbered arguments (1st, 3rd, 5th, etc) be range references. If the OP's problem is rounding, then the solution would be =COUNTIFS(H:H,"=24.5",H:H,"<40.5") And, no, there's no roundoff error with .5 when the absolute value of the integer part is less than 10^14. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countifs with same reference?
I wasn't trying to correct his data set. I was testing to find out if that
was causing an error. Then he could go from there. Approach to find out if the 25 or 40 that might be displayed is ACTUALLY 25 or 40 is just fine and dandy. -- John C "Harlan Grove" wrote: John C <johnc@stateofdenial wrote... I was suggesting if his number in his data set showed 25 or 40 (but in actuality is not quite 25 or 40), then check to see if 25=INT(the cell that shows 25). .... Clarification good, but approach still flawed. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countifs with same reference?
John C <johnc@stateofdenial wrote...
I wasn't trying to correct his data set. I was testing to find out if that was causing an error. Then he could go from there. Approach to find out if the 25 or 40 that might be displayed is ACTUALLY 25 or 40 is just fine and dandy. .... The problem is that your first response was, to be generous, incomplete. Let's recap: 'If there are any values that are 25 or 40, perhaps you should use INT(25) and INT(40), just to ensure there is no rounding issues?' How would you expect someone else to interpret that? What would these values be? The values of cells in the OP's H:H range as used in the OP's original formula? If so, I'll repeat the line of questioning I undertook last week: how would the OP use INT in his/her COUNTIFS formula? If you meant that the OP should use formulas in other cells to check that the numbers displayed in column H were whole numbers/integers, you've now wasted 4 responses not making that clear. You may know what you mean, but you've done a pretty poor job of communicating it. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countifs with same reference?
If you wanted to test whether the value was between 25 and 25.999999...,
then INT() is fine. If you want to test for a value between 24.5 and 25.499999..., then use ROUND(). If you want to test for the value being equal to 25, just test the cell against 25, and don't use INT or ROUND. -- David Biddulph "John C" <johnc@stateofdenial wrote in message ... ding ding ding, you now get it! If he shows a value of 25 in a cell, but in reality it is 24.999999999, then OBVIOUSLY 25 will not be equal to INT(25). This was testing the data to ensure that any values that show 25, and any values that show 40, are truly 25 and 40 respectively. -- John C "David Biddulph" wrote: Unless that you are confident that any rounding is downwards, you'd be better withy ROUND rather than INT. If the cell contains 24.99999999999, INT will return 24, not 25. -- David Biddulph "John C" <johnc@stateofdenial wrote in message ... I was suggesting if his number in his data set showed 25 or 40 (but in actuality is not quite 25 or 40), then check to see if 25=INT(the cell that shows 25). -- John C "Harlan Grove" wrote: John C <johnc@stateofdenial wrote... If there are any values that are 25 or 40, perhaps you should use INT(25) and INT(40), just to ensure there is no rounding issues? .... When would INT(25) and 25 or INT(40) and 40 not be the same? Or do you mean wrap the H:H references inside INT? If so, can't be done in COUNTIFS, which requires that its odd-numbered arguments (1st, 3rd, 5th, etc) be range references. If the OP's problem is rounding, then the solution would be =COUNTIFS(H:H,"=24.5",H:H,"<40.5") And, no, there's no roundoff error with .5 when the absolute value of the integer part is less than 10^14. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countifs with same reference?
Whatever. You interpret what you want. I was responding to the questions and
followup as stated. Perhaps you don't contextualize a response, but I do. I shall not respond to your critiques any further, I have no use for them, or for you. -- John C "Harlan Grove" wrote: John C <johnc@stateofdenial wrote... I wasn't trying to correct his data set. I was testing to find out if that was causing an error. Then he could go from there. Approach to find out if the 25 or 40 that might be displayed is ACTUALLY 25 or 40 is just fine and dandy. .... The problem is that your first response was, to be generous, incomplete. Let's recap: 'If there are any values that are 25 or 40, perhaps you should use INT(25) and INT(40), just to ensure there is no rounding issues?' How would you expect someone else to interpret that? What would these values be? The values of cells in the OP's H:H range as used in the OP's original formula? If so, I'll repeat the line of questioning I undertook last week: how would the OP use INT in his/her COUNTIFS formula? If you meant that the OP should use formulas in other cells to check that the numbers displayed in column H were whole numbers/integers, you've now wasted 4 responses not making that clear. You may know what you mean, but you've done a pretty poor job of communicating it. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countifs with same reference?
Your 3rd response is exactly what I was doing.
-- John C "David Biddulph" wrote: If you wanted to test whether the value was between 25 and 25.999999..., then INT() is fine. If you want to test for a value between 24.5 and 25.499999..., then use ROUND(). If you want to test for the value being equal to 25, just test the cell against 25, and don't use INT or ROUND. -- David Biddulph "John C" <johnc@stateofdenial wrote in message ... ding ding ding, you now get it! If he shows a value of 25 in a cell, but in reality it is 24.999999999, then OBVIOUSLY 25 will not be equal to INT(25). This was testing the data to ensure that any values that show 25, and any values that show 40, are truly 25 and 40 respectively. -- John C "David Biddulph" wrote: Unless that you are confident that any rounding is downwards, you'd be better withy ROUND rather than INT. If the cell contains 24.99999999999, INT will return 24, not 25. -- David Biddulph "John C" <johnc@stateofdenial wrote in message ... I was suggesting if his number in his data set showed 25 or 40 (but in actuality is not quite 25 or 40), then check to see if 25=INT(the cell that shows 25). -- John C "Harlan Grove" wrote: John C <johnc@stateofdenial wrote... If there are any values that are 25 or 40, perhaps you should use INT(25) and INT(40), just to ensure there is no rounding issues? .... When would INT(25) and 25 or INT(40) and 40 not be the same? Or do you mean wrap the H:H references inside INT? If so, can't be done in COUNTIFS, which requires that its odd-numbered arguments (1st, 3rd, 5th, etc) be range references. If the OP's problem is rounding, then the solution would be =COUNTIFS(H:H,"=24.5",H:H,"<40.5") And, no, there's no roundoff error with .5 when the absolute value of the integer part is less than 10^14. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countifs with same reference?
John C <johnc@stateofdenial wrote...
.... I was responding to the questions and followup as stated. Perhaps you don't contextualize a response, but I do. So that's what you call omitting necessary details - contextualizing. Reality seldom intrudes into your context, does it? I shall not respond to your critiques any further, I have no use for them, .... Nor any capacity to recognize your own mistakes, thus an unwillingness to admit you made them, and certainly no chance you'd learn from them. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countifs with same reference?
Just because you misunderstand a response to a question, does not mean the
person who posed the question misunderstood it. I write the response to the person questioning, I do not write a response to appease you. I have seen your flames of several other people on here who post. It is truly pathetic. Do you have nothing better to do with your time? Your 'reality' is that you misunderstood a response. Don't blame me that you are suffering from synaptic misfires. I had said I would not respond before, and yes, you got me to the point that I had too. I guess you got a way with people (again, looking at some of your past flame jobs) that just gets under their skin. If you do not like my posts, then don't read them, and the world will be a better place. -- John C "Harlan Grove" wrote: John C <johnc@stateofdenial wrote... .... I was responding to the questions and followup as stated. Perhaps you don't contextualize a response, but I do. So that's what you call omitting necessary details - contextualizing. Reality seldom intrudes into your context, does it? I shall not respond to your critiques any further, I have no use for them, .... Nor any capacity to recognize your own mistakes, thus an unwillingness to admit you made them, and certainly no chance you'd learn from them. |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countifs with same reference?
John C <johnc@stateofdenial wrote...
Just because you misunderstand a response to a question, does not mean the person who posed the question misunderstood it. . . . Compare 25 to INT(25)? I understand it - it's useless. I imagine the OP has also found it useless by now too. Had you written that if some cell, say C3, showed 25, then it'd be useful to compare INT(C3) or, better, ROUND(C3,0), to 25, then your first response would have been useful. . . . I have seen your flames of several other people on here who post. . . . I only flame people who've proven they're idiots. Welcome to the club. It's easy to leave this club - stop being an idiot. Then again, we all rejoin this club (me too) from time to time. However, some people seem to be trying for life memberships. I had said I would not respond before, and yes, you got me to the point that I had too. . . . .... So I can get you to respond to me. How can I get you to respond to OPs with pertinent details? . . If you do not like my posts, then don't read them, and the world will be a better place. Nah. I'm performing a public service - pointing out poor postings and providing details on the lack of depth or breadth. |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countifs with same reference?
you just aren't worth it.
-- John C "Harlan Grove" wrote: John C <johnc@stateofdenial wrote... Just because you misunderstand a response to a question, does not mean the person who posed the question misunderstood it. . . . Compare 25 to INT(25)? I understand it - it's useless. I imagine the OP has also found it useless by now too. Had you written that if some cell, say C3, showed 25, then it'd be useful to compare INT(C3) or, better, ROUND(C3,0), to 25, then your first response would have been useful. . . . I have seen your flames of several other people on here who post. . . . I only flame people who've proven they're idiots. Welcome to the club. It's easy to leave this club - stop being an idiot. Then again, we all rejoin this club (me too) from time to time. However, some people seem to be trying for life memberships. I had said I would not respond before, and yes, you got me to the point that I had too. . . . .... So I can get you to respond to me. How can I get you to respond to OPs with pertinent details? . . If you do not like my posts, then don't read them, and the world will be a better place. Nah. I'm performing a public service - pointing out poor postings and providing details on the lack of depth or breadth. |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countifs with same reference?
John C <johnc@stateofdenial wrote...
you just aren't worth it. Oh, so you're codependent too? |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countifs with same reference?
Hence our confusion when you suggested INT.
-- David Biddulph "John C" <johnc@stateofdenial wrote in message ... Your 3rd response is exactly what I was doing. -- John C "David Biddulph" wrote: If you wanted to test whether the value was between 25 and 25.999999..., then INT() is fine. If you want to test for a value between 24.5 and 25.499999..., then use ROUND(). If you want to test for the value being equal to 25, just test the cell against 25, and don't use INT or ROUND. -- David Biddulph "John C" <johnc@stateofdenial wrote in message ... ding ding ding, you now get it! If he shows a value of 25 in a cell, but in reality it is 24.999999999, then OBVIOUSLY 25 will not be equal to INT(25). This was testing the data to ensure that any values that show 25, and any values that show 40, are truly 25 and 40 respectively. -- John C "David Biddulph" wrote: Unless that you are confident that any rounding is downwards, you'd be better withy ROUND rather than INT. If the cell contains 24.99999999999, INT will return 24, not 25. -- David Biddulph "John C" <johnc@stateofdenial wrote in message ... I was suggesting if his number in his data set showed 25 or 40 (but in actuality is not quite 25 or 40), then check to see if 25=INT(the cell that shows 25). -- John C "Harlan Grove" wrote: John C <johnc@stateofdenial wrote... If there are any values that are 25 or 40, perhaps you should use INT(25) and INT(40), just to ensure there is no rounding issues? .... When would INT(25) and 25 or INT(40) and 40 not be the same? Or do you mean wrap the H:H references inside INT? If so, can't be done in COUNTIFS, which requires that its odd-numbered arguments (1st, 3rd, 5th, etc) be range references. If the OP's problem is rounding, then the solution would be =COUNTIFS(H:H,"=24.5",H:H,"<40.5") And, no, there's no roundoff error with .5 when the absolute value of the integer part is less than 10^14. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countifs Fx in 07 how in 03? | Excel Discussion (Misc queries) | |||
countifs | Excel Discussion (Misc queries) | |||
Averageifs & Countifs | Excel Worksheet Functions | |||
2 COUNTIFS | Excel Discussion (Misc queries) | |||
Multiple countifs | Excel Worksheet Functions |