Countifs with same reference?

[CEBeau]

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.

 

[Per Jessen]

Hi

Nothing wrong with the formula. Are you sure than 50 entries falls into the
range ?

Regards,
Per

 

[CEBeau]

Yes. I counted myself and had another person verify.

 

[John C]

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?

 

[Harlan Grove]

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.

 

[John C]

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).

 

[David Biddulph]

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.

 

[Harlan Grove]

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.

 

[John C]

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]

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.

 

[Harlan Grove]

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.

 

[David Biddulph]

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.

 

[John C]

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]

Your 3rd response is exactly what I was doing.

 

[Harlan Grove]

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.

 

[John C]

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.

 

[Harlan Grove]

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.

 

[John C]

you just aren't worth it.
--
John C

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 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.

....

So I can get you to respond to me. How can I get you to respond to OPs
with pertinent details?


Nah. I'm performing a public service - pointing out poor postings and
providing details on the lack of depth or breadth.

 

[Harlan Grove]

you just aren't worth it.

Oh, so you're codependent too?

 

[David Biddulph]

Hence our confusion when you suggested INT.