Hello,
Is there anybody can help me to count the odd or even in an excel area?
There is a data area which contains natural number.I need to count how many
odd or even in this area.

Hi Bill

Say your range occupies A1:B10 and you may have blank cells, then you
can use:

=SUMPRODUCT(--(A1:B10<""),--(MOD(A1:B10,2)=1))

to count odd, and:

=SUMPRODUCT(--(A1:B10<""),--(MOD(A1:B10,2)=0))

to count even

Hope this helps!

Richard


Bill. liu wrote:

Hello,
Is there anybody can help me to count the odd or even in an excel area?
There is a data area which contains natural number.I need to count how many
odd or even in this area.

A different reading to Richard's

=COUNTIF(A1:C10,"<0")

and

=COUNTIF(A1:C10,"0")

--
HTH

Bob Phillips

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

"Bill.liu" wrote in message
...
Hello,
Is there anybody can help me to count the odd or even in an excel area?
There is a data area which contains natural number.I need to count how
many
odd or even in this area.

someone could provide a simpler solution but for the moment you can use the
'iseven' function (eg: =iseven(cell ref) This will reply true for an even
number and false for an odd number. You then just need to count the
responses. Two sumifs will do this for you. (note: you will need to have
the analysis toolpak add-in installed for the iseven function to work
(toolsadd-ins and then tick all add-ins except euros, you can tick euros if
you ever intend to work in european currencies))

"Bill.liu" wrote:

Hello,
Is there anybody can help me to count the odd or even in an excel area?
There is a data area which contains natural number.I need to count how many
odd or even in this area.

=iseven(cell ref) or =isodd(cell ref) works fine. This is neat!

Has anyone tried to key in =iseven or =isodd (i.e. without the cell ref) in a cell? I didn't get an error but ......

I'll let you find out. No idea what that means.

I use V 2003.

Epinn

"Oldersox" wrote in message ...
someone could provide a simpler solution but for the moment you can use the
'iseven' function (eg: =iseven(cell ref) This will reply true for an even
number and false for an odd number. You then just need to count the
responses. Two sumifs will do this for you. (note: you will need to have
the analysis toolpak add-in installed for the iseven function to work
(toolsadd-ins and then tick all add-ins except euros, you can tick euros if
you ever intend to work in european currencies))

"Bill.liu" wrote:

Hello,
Is there anybody can help me to count the odd or even in an excel area?
There is a data area which contains natural number.I need to count how many
odd or even in this area.

I get #N/A, don't you? Oddly, in XP I get #VALUE.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Epinn" wrote in message
...
=iseven(cell ref) or =isodd(cell ref) works fine. This is neat!

Has anyone tried to key in =iseven or =isodd (i.e. without the cell ref) in
a cell? I didn't get an error but ......

I'll let you find out. No idea what that means.

I use V 2003.

Epinn

"Oldersox" wrote in message
...
someone could provide a simpler solution but for the moment you can use the
'iseven' function (eg: =iseven(cell ref) This will reply true for an even
number and false for an odd number. You then just need to count the
responses. Two sumifs will do this for you. (note: you will need to have
the analysis toolpak add-in installed for the iseven function to work
(toolsadd-ins and then tick all add-ins except euros, you can tick euros if
you ever intend to work in european currencies))

"Bill.liu" wrote:

Hello,
Is there anybody can help me to count the odd or even in an excel area?
There is a data area which contains natural number.I need to count how
many
odd or even in this area.

You got #N/A because you probably keyed in ( ) after =iseven.
I only keyed in =iseven without ( ) and I got a long number 752746504. Is this a phone no. or what? ;)
For =isodd, I got -708247545. A negative number?

Do you get the same when you drop the ( )?

Epinn

"Bob Phillips" wrote in message ...
I get #N/A, don't you? Oddly, in XP I get #VALUE.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Epinn" wrote in message
...
=iseven(cell ref) or =isodd(cell ref) works fine. This is neat!

Has anyone tried to key in =iseven or =isodd (i.e. without the cell ref) in
a cell? I didn't get an error but ......

I'll let you find out. No idea what that means.

I use V 2003.

Epinn

"Oldersox" wrote in message
...
someone could provide a simpler solution but for the moment you can use the
'iseven' function (eg: =iseven(cell ref) This will reply true for an even
number and false for an odd number. You then just need to count the
responses. Two sumifs will do this for you. (note: you will need to have
the analysis toolpak add-in installed for the iseven function to work
(toolsadd-ins and then tick all add-ins except euros, you can tick euros if
you ever intend to work in european currencies))

"Bill.liu" wrote:

Hello,
Is there anybody can help me to count the odd or even in an excel area?
There is a data area which contains natural number.I need to count how
many
odd or even in this area.

Correct me if I am wrong ......

If it is a function from the ATP, it gives a number, otherwise it will display #NAME?

=workday =eomonth also give a long number.
=sumif =count give #NAME?

Okay, it was an accident and I didn't mean to distract anyone.

Epinn

"Epinn" wrote in message ...
You got #N/A because you probably keyed in ( ) after =iseven.
I only keyed in =iseven without ( ) and I got a long number 752746504. Is this a phone no. or what? ;)
For =isodd, I got -708247545. A negative number?

Do you get the same when you drop the ( )?

Epinn

"Bob Phillips" wrote in message ...
I get #N/A, don't you? Oddly, in XP I get #VALUE.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Epinn" wrote in message
...
=iseven(cell ref) or =isodd(cell ref) works fine. This is neat!

Has anyone tried to key in =iseven or =isodd (i.e. without the cell ref) in
a cell? I didn't get an error but ......

I'll let you find out. No idea what that means.

I use V 2003.

Epinn

"Oldersox" wrote in message
...
someone could provide a simpler solution but for the moment you can use the
'iseven' function (eg: =iseven(cell ref) This will reply true for an even
number and false for an odd number. You then just need to count the
responses. Two sumifs will do this for you. (note: you will need to have
the analysis toolpak add-in installed for the iseven function to work
(toolsadd-ins and then tick all add-ins except euros, you can tick euros if
you ever intend to work in european currencies))

"Bill.liu" wrote:

Hello,
Is there anybody can help me to count the odd or even in an excel area?
There is a data area which contains natural number.I need to count how
many
odd or even in this area.

Hi Epinn
Has anyone tried to key in =iseven or =isodd (i.e. without the cell
ref) in a cell?
Yes strange numbers appear when you do this.

But it changes with each version of Excel. Results below for each
version

XL97 #NAME #NAME
XL2000 2000486408 -1909850105
XL2002 #NAME #NAME
XL2003 -708706296 752156679
XL2007 #NAME #NAME

I hadn't seen your later posting about Workday when starting this, so I
didn't try out all the others but in XL2003
=Workday gives a number and in XL2007 it gives #NAME

--
Regards

Roger Govier


"Epinn" wrote in message
...
=iseven(cell ref) or =isodd(cell ref) works fine. This is neat!

Has anyone tried to key in =iseven or =isodd (i.e. without the cell ref)
in a cell? I didn't get an error but ......

I'll let you find out. No idea what that means.

I use V 2003.

Epinn

"Oldersox" wrote in message
...
someone could provide a simpler solution but for the moment you can use
the
'iseven' function (eg: =iseven(cell ref) This will reply true for an
even
number and false for an odd number. You then just need to count the
responses. Two sumifs will do this for you. (note: you will need to
have
the analysis toolpak add-in installed for the iseven function to work
(toolsadd-ins and then tick all add-ins except euros, you can tick
euros if
you ever intend to work in european currencies))

"Bill.liu" wrote:

Hello,
Is there anybody can help me to count the odd or even in an excel
area?
There is a data area which contains natural number.I need to count how
many
odd or even in this area.

So XL14 will generate numbers again?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Roger Govier" wrote in message
...
Hi Epinn
Has anyone tried to key in =iseven or =isodd (i.e. without the cell ref)
in a cell?
Yes strange numbers appear when you do this.

But it changes with each version of Excel. Results below for each version

XL97 #NAME #NAME
XL2000 2000486408 -1909850105
XL2002 #NAME #NAME
XL2003 -708706296 752156679
XL2007 #NAME #NAME

I hadn't seen your later posting about Workday when starting this, so I
didn't try out all the others but in XL2003
=Workday gives a number and in XL2007 it gives #NAME

--
Regards

Roger Govier


"Epinn" wrote in message
...
=iseven(cell ref) or =isodd(cell ref) works fine. This is neat!

Has anyone tried to key in =iseven or =isodd (i.e. without the cell ref)
in a cell? I didn't get an error but ......

I'll let you find out. No idea what that means.

I use V 2003.

Epinn

"Oldersox" wrote in message
...
someone could provide a simpler solution but for the moment you can use
the
'iseven' function (eg: =iseven(cell ref) This will reply true for an even
number and false for an odd number. You then just need to count the
responses. Two sumifs will do this for you. (note: you will need to have
the analysis toolpak add-in installed for the iseven function to work
(toolsadd-ins and then tick all add-ins except euros, you can tick euros
if
you ever intend to work in european currencies))

"Bill.liu" wrote:

Hello,
Is there anybody can help me to count the odd or even in an excel area?
There is a data area which contains natural number.I need to count how
many
odd or even in this area.

The number must be generated somewhere from some machine info, Roger gets a
different number than you, I get different again (889913352 and -870580217).

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Epinn" wrote in message
...
You got #N/A because you probably keyed in ( ) after =iseven.
I only keyed in =iseven without ( ) and I got a long number 752746504. Is
this a phone no. or what? ;)
For =isodd, I got -708247545. A negative number?

Do you get the same when you drop the ( )?

Epinn

"Bob Phillips" wrote in message
...
I get #N/A, don't you? Oddly, in XP I get #VALUE.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Epinn" wrote in message
...
=iseven(cell ref) or =isodd(cell ref) works fine. This is neat!

Has anyone tried to key in =iseven or =isodd (i.e. without the cell ref) in
a cell? I didn't get an error but ......

I'll let you find out. No idea what that means.

I use V 2003.

Epinn

"Oldersox" wrote in message
...
someone could provide a simpler solution but for the moment you can use the
'iseven' function (eg: =iseven(cell ref) This will reply true for an even
number and false for an odd number. You then just need to count the
responses. Two sumifs will do this for you. (note: you will need to have
the analysis toolpak add-in installed for the iseven function to work
(toolsadd-ins and then tick all add-ins except euros, you can tick euros if
you ever intend to work in european currencies))

"Bill.liu" wrote:

Hello,
Is there anybody can help me to count the odd or even in an excel area?
There is a data area which contains natural number.I need to count how
many
odd or even in this area.

Only in the interests of consistency<bg

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
So XL14 will generate numbers again?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Roger Govier" wrote in message
...
Hi Epinn
Has anyone tried to key in =iseven or =isodd (i.e. without the cell
ref) in a cell?
Yes strange numbers appear when you do this.

But it changes with each version of Excel. Results below for each
version

XL97 #NAME #NAME
XL2000 2000486408 -1909850105
XL2002 #NAME #NAME
XL2003 -708706296 752156679
XL2007 #NAME #NAME

I hadn't seen your later posting about Workday when starting this, so
I didn't try out all the others but in XL2003
=Workday gives a number and in XL2007 it gives #NAME

--
Regards

Roger Govier


"Epinn" wrote in message
...
=iseven(cell ref) or =isodd(cell ref) works fine. This is neat!

Has anyone tried to key in =iseven or =isodd (i.e. without the cell
ref) in a cell? I didn't get an error but ......

I'll let you find out. No idea what that means.

I use V 2003.

Epinn

"Oldersox" wrote in message
...
someone could provide a simpler solution but for the moment you can
use the
'iseven' function (eg: =iseven(cell ref) This will reply true for an
even
number and false for an odd number. You then just need to count the
responses. Two sumifs will do this for you. (note: you will need to
have
the analysis toolpak add-in installed for the iseven function to work
(toolsadd-ins and then tick all add-ins except euros, you can tick
euros if
you ever intend to work in european currencies))

"Bill.liu" wrote:

Hello,
Is there anybody can help me to count the odd or even in an excel
area?
There is a data area which contains natural number.I need to count
how many
odd or even in this area.

Thank you both for taking part in my experiment. You two are wonderful. For some reason, I seem to have an affinity with "anomalies" in Excel. ;) What will I find next?

Guess I can throw away my assumption that numbers are for ATP functions only. They are more version dependent and the "consistency" is amazing. Excel will always remain a mystery to me.

Roger, remember our experiment on "COUNTIF says Null = Blank but Blank < Null?" A few days ago, MATCH told me blank = 0 but blank not equal to blank. I didn't post my findings but I am still hanging on to my worksheet. In case you are interested, I can email it to you.

Epinn

"Roger Govier" wrote in message ...
Only in the interests of consistency<bg

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
So XL14 will generate numbers again?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Roger Govier" wrote in message
...
Hi Epinn
Has anyone tried to key in =iseven or =isodd (i.e. without the cell
ref) in a cell?
Yes strange numbers appear when you do this.

But it changes with each version of Excel. Results below for each
version

XL97 #NAME #NAME
XL2000 2000486408 -1909850105
XL2002 #NAME #NAME
XL2003 -708706296 752156679
XL2007 #NAME #NAME

I hadn't seen your later posting about Workday when starting this, so
I didn't try out all the others but in XL2003
=Workday gives a number and in XL2007 it gives #NAME

--
Regards

Roger Govier


"Epinn" wrote in message
...
=iseven(cell ref) or =isodd(cell ref) works fine. This is neat!

Has anyone tried to key in =iseven or =isodd (i.e. without the cell
ref) in a cell? I didn't get an error but ......

I'll let you find out. No idea what that means.

I use V 2003.

Epinn

"Oldersox" wrote in message
...
someone could provide a simpler solution but for the moment you can
use the
'iseven' function (eg: =iseven(cell ref) This will reply true for an
even
number and false for an odd number. You then just need to count the
responses. Two sumifs will do this for you. (note: you will need to
have
the analysis toolpak add-in installed for the iseven function to work
(toolsadd-ins and then tick all add-ins except euros, you can tick
euros if
you ever intend to work in european currencies))

"Bill.liu" wrote:

Hello,
Is there anybody can help me to count the odd or even in an excel
area?
There is a data area which contains natural number.I need to count
how many
odd or even in this area.

Bob Phillips wrote...
The number must be generated somewhere from some machine info, Roger gets a
different number than you, I get different again (889913352 and -870580217).
....

That's because it's a runtime generated reference to the UDF. It works
like a reference in C++ or Perl, or a pointer in C.

On my system at the moment, =ISEVEN returns 1398997000 and =ISODD
returns -1435238393. If I define the names x and y referring
respectively to 1398997000 and -1435238393, then I define z as
=IF($A$1,x,y). With A1 blank, the formula =z(2) returns FALSE (same as
ISODD), then I enter 1 in A1, and the formula =z(2) returns TRUE (same
as ISEVEN). So for UDFs (but not built-in functions, unfortunately),
Excel provides function aliases. Getting really fancy, create the
following table (named FTBL) with 1st col text, 2nd col formulas.

ISEVEN =ISEVEN
ISODD =ISODD

Then name a cell FCN, and redefine z as =LOOKUP(FCN,FTBL). Enter the
formula =z(2) in some other cell and enter various things in FCN. The
advantage of using such an FTBL is that it uses the current values of
the UDF reference values, so avoiding hardcoding.

Roger Govier wrote...
....
But it changes with each version of Excel. Results below for each
version

XL97 #NAME #NAME
XL2000 2000486408 -1909850105
XL2002 #NAME #NAME
XL2003 -708706296 752156679
XL2007 #NAME #NAME
....

You must not have the ATP loaded in XL97 and XL2002. XL2002 on my
machine at this moment gives -324861944 for =ISEVEN and 364118023 for
=ISODD.

In 2007 the former ATP functions have become built-in functions, so
=ISEVEN and =ISODD *should* return the same result (#NAME?) as =SUM or
=NA.

Harlan

I was very surprised when first reading your post, as I tend to always
have ATP loaded.
However, believing that you would be correct, I went back and checked
and to my surprise found that ATP wasn't loaded on XL97 and XL2002
(these are the 2 I least often go back to).
..
On loading the respective ATP's, all versions do behave the same, other
than XL2007 - as you describe.

Thank you for your detailed explanation in response to Epinn's post.
--
Regards

Roger Govier


"Harlan Grove" wrote in message
ups.com...
Roger Govier wrote...
...
But it changes with each version of Excel. Results below for each
version

XL97 #NAME #NAME
XL2000 2000486408 -1909850105
XL2002 #NAME #NAME
XL2003 -708706296 752156679
XL2007 #NAME #NAME
...

You must not have the ATP loaded in XL97 and XL2002. XL2002 on my
machine at this moment gives -324861944 for =ISEVEN and 364118023 for
=ISODD.

In 2007 the former ATP functions have become built-in functions, so
=ISEVEN and =ISODD *should* return the same result (#NAME?) as =SUM or
=NA.

Hi Richard,

Thanks very much for you help. It's great!
But I am not very clear about how it works,it would be appreciate if you can
make some explanation about the formula.

Thanks again and Best regards!

Bill
"RichardSchollar" wrote:

Hi Bill

Say your range occupies A1:B10 and you may have blank cells, then you
can use:

=SUMPRODUCT(--(A1:B10<""),--(MOD(A1:B10,2)=1))

to count odd, and:

=SUMPRODUCT(--(A1:B10<""),--(MOD(A1:B10,2)=0))

to count even

Hope this helps!

Richard


Bill. liu wrote:

Hello,
Is there anybody can help me to count the odd or even in an excel area?
There is a data area which contains natural number.I need to count how many
odd or even in this area.

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)

"Bill.liu" wrote in message
...
Hi Richard,

Thanks very much for you help. It's great!
But I am not very clear about how it works,it would be appreciate if you
can
make some explanation about the formula.

Thanks again and Best regards!

Bill
"RichardSchollar" wrote:

Hi Bill

Say your range occupies A1:B10 and you may have blank cells, then you
can use:

=SUMPRODUCT(--(A1:B10<""),--(MOD(A1:B10,2)=1))

to count odd, and:

=SUMPRODUCT(--(A1:B10<""),--(MOD(A1:B10,2)=0))

to count even

Hope this helps!

Richard


Bill. liu wrote:

Hello,
Is there anybody can help me to count the odd or even in an excel
area?
There is a data area which contains natural number.I need to count how
many
odd or even in this area.

Guess I can throw away my assumption that numbers are for ATP functions only....... more version dependent ...........<<

Thanks to Harlan's and Roger's clarification, please ignore the above statement. If I have not misunderstood, my original assumption (see below) is correct.

If it is a function from the ATP, it gives a number, otherwise it will display #NAME? <<

functions from the ATP = UDF's
number = runtime generated reference to the UDF i.e. function alias

Epinn

"Epinn" wrote in message ...
Thank you both for taking part in my experiment. You two are wonderful. For some reason, I seem to have an affinity with "anomalies" in Excel. ;) What will I find next?

Guess I can throw away my assumption that numbers are for ATP functions only. They are more version dependent and the "consistency" is amazing. Excel will always remain a mystery to me.

Roger, remember our experiment on "COUNTIF says Null = Blank but Blank < Null?" A few days ago, MATCH told me blank = 0 but blank not equal to blank. I didn't post my findings but I am still hanging on to my worksheet. In case you are interested, I can email it to you.

Epinn

"Roger Govier" wrote in message ...
Only in the interests of consistency<bg

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
So XL14 will generate numbers again?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Roger Govier" wrote in message
...
Hi Epinn
Has anyone tried to key in =iseven or =isodd (i.e. without the cell
ref) in a cell?
Yes strange numbers appear when you do this.

But it changes with each version of Excel. Results below for each
version

XL97 #NAME #NAME
XL2000 2000486408 -1909850105
XL2002 #NAME #NAME
XL2003 -708706296 752156679
XL2007 #NAME #NAME

I hadn't seen your later posting about Workday when starting this, so
I didn't try out all the others but in XL2003
=Workday gives a number and in XL2007 it gives #NAME

--
Regards

Roger Govier


"Epinn" wrote in message
...
=iseven(cell ref) or =isodd(cell ref) works fine. This is neat!

Has anyone tried to key in =iseven or =isodd (i.e. without the cell
ref) in a cell? I didn't get an error but ......

I'll let you find out. No idea what that means.

I use V 2003.

Epinn

"Oldersox" wrote in message
...
someone could provide a simpler solution but for the moment you can
use the
'iseven' function (eg: =iseven(cell ref) This will reply true for an
even
number and false for an odd number. You then just need to count the
responses. Two sumifs will do this for you. (note: you will need to
have
the analysis toolpak add-in installed for the iseven function to work
(toolsadd-ins and then tick all add-ins except euros, you can tick
euros if
you ever intend to work in european currencies))

"Bill.liu" wrote:

Hello,
Is there anybody can help me to count the odd or even in an excel
area?
There is a data area which contains natural number.I need to count
how many
odd or even in this area.