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.

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.

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.

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.

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.

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.