Counting dates....
 

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

Counting dates....
 

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

Cheers,
--
AP

"ExcelDummie" a écrit dans le
message de news: ...
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")

Counting dates....
 

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

Cheers,
--
AP

"SteveW" a écrit dans le message de news:
op.tci39vlwevjsnp@enigma03...
Since when, and why does excel accept "<--" as "<=" ?

Steve

On Tue, 11 Jul 2006 15:23:40 +0100, Ardus Petus
wrote:

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

Cheers,
--
AP

"ExcelDummie" a écrit dans le
message de news: ...
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")





--
Steve (3)

Counting dates....
 

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


On Tue, 11 Jul 2006 15:51:02 +0100, Ardus Petus
wrote:

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

Cheers,
--
AP

"SteveW" a écrit dans le message de news:
op.tci39vlwevjsnp@enigma03...
Since when, and why does excel accept "<--" as "<=" ?

Steve

On Tue, 11 Jul 2006 15:23:40 +0100, Ardus Petus

wrote:

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

Cheers,
--
AP

"ExcelDummie" a écrit dans le
message de news: ...
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")





--
Steve (3)





--
Steve (3)

Counting dates....
 

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)

"SteveW" wrote in message
news:op.tci5ioleevjsnp@enigma03...
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


On Tue, 11 Jul 2006 15:51:02 +0100, Ardus Petus
wrote:

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

Cheers,
--
AP

"SteveW" a écrit dans le message de news:
op.tci39vlwevjsnp@enigma03...
Since when, and why does excel accept "<--" as "<=" ?

Steve

On Tue, 11 Jul 2006 15:23:40 +0100, Ardus Petus

wrote:

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

Cheers,
--
AP

"ExcelDummie" a écrit dans le
message de news: ...
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")





--
Steve (3)





--
Steve (3)

Counting dates....
 

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" wrote:

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


On Tue, 11 Jul 2006 15:51:02 +0100, Ardus Petus
wrote:

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

Cheers,
--
AP

"SteveW" a écrit dans le message de news:
op.tci39vlwevjsnp@enigma03...
Since when, and why does excel accept "<--" as "<=" ?

Steve

On Tue, 11 Jul 2006 15:23:40 +0100, Ardus Petus

wrote:

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

Cheers,
--
AP

"ExcelDummie" a écrit dans le
message de news: ...
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")





--
Steve (3)





--
Steve (3)

Counting dates....
 

On Tue, 11 Jul 2006 16:14:19 +0100, Bob Phillips
wrote:

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

--
Steve (3)

Counting dates....
 

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


On Tue, 11 Jul 2006 16:52:01 +0100, Sloth
wrote:

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" wrote:

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


On Tue, 11 Jul 2006 15:51:02 +0100, Ardus Petus

wrote:

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

Cheers,
--
AP

"SteveW" a écrit dans le message de news:
op.tci39vlwevjsnp@enigma03...
Since when, and why does excel accept "<--" as "<=" ?

Steve

On Tue, 11 Jul 2006 15:23:40 +0100, Ardus Petus

wrote:

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

Cheers,
--
AP

"ExcelDummie" a écrit dans
le
message de news:
...
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")





--
Steve (3)





--
Steve (3)




--
Steve (3)

Counting dates....
 

Not quite, try doing

=VALUE(TRUE)

and

=--TRUE

--
HTH

Bob Phillips

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

"Sloth" wrote in message
...
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" wrote:

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


On Tue, 11 Jul 2006 15:51:02 +0100, Ardus Petus

wrote:

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

Cheers,
--
AP

"SteveW" a écrit dans le message de news:
op.tci39vlwevjsnp@enigma03...
Since when, and why does excel accept "<--" as "<=" ?

Steve

On Tue, 11 Jul 2006 15:23:40 +0100, Ardus Petus

wrote:

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

Cheers,
--
AP

"ExcelDummie" a écrit dans
le
message de news:
...
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")





--
Steve (3)





--
Steve (3)