Counting dates....

[Guest]

Trying to do a calculation to count the number of people who are aged between
30 and 40 have their dates of birth in a table trying to get the formula to
work because it has to be greater than one date AND smaller than another????

=COUNTIF(B2:B97,"<30/05/1976",>=30/05/1966")

 

[Ardus Petus]

Try:
=SUMPRODUCT((B2:B97<--"30/05/1976")*(B2:B97>--"30/05/1966"))

Cheers,

 

[SteveW]

Since when, and why does excel accept "<--" as "<=" ?

Steve

 

[Ardus Petus]

The -- is used to turn string litteral into numeric value.
=SUMPRODUCT((B2:B97<--"30/05/1976")*(B2:B97>=--"30/05/1966"))

Cheers,

 

[SteveW]

Cheers, thanks for that.

Now I see it works, but I can't find any reference to it in my 2003 Help
file

Has it been one of those hidden features ?

Steve

 

[Bob Phillips]

The double unary is a useful tool to convert non-numeric data to numeric,
where it can of course. So a date form like "2006-07-11" can be transformed
to an Excel date (which is just a number) by preceding with the double
unary. Similarly, True can be transformed to 1, and False to 0, which is the
basis of many SUMPRODUCT formulae seen around. See
http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

putting the double negative before a text string is the same thing as using
the VALUE function. You actually could perform any mathematical function on
a text string that can be a number (or boolean value). For example
="01"+"005"
returns the number 6 (as a number not text). It just so happens that -- is
the easiest way to convert text to a number; as opposed to: the VALUE
function, 0+, 1*, etc.

*copied from the help under the VALUE function...

Remark

You do not generally need to use the VALUE function in a formula because
Microsoft Excel automatically converts text to numbers as necessary. This
function is provided for compatibility with other spreadsheet programs.

 

[SteveW]

The double unary is a useful tool to convert non-numeric data to numeric,
where it can of course. So a date form like "2006-07-11" can be
transformed
to an Excel date (which is just a number) by preceding with the double
unary. Similarly, True can be transformed to 1, and False to 0, which is
the
basis of many SUMPRODUCT formulae seen around. See
http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

!!! Opera doesn't like that site - 50 lines of code appear at the start

but I see the explanation of --

 

[SteveW]

Neat, and + on it's won't actuall do anything.

I could see it's effect from the start once I realised it wasn't (<=!)
But it's so annoying when it's difficult to find it in the help etc etc

Steve

 

[Bob Phillips]

Not quite, try doing

=VALUE(TRUE)

and

=--TRUE

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)