Newbie needs help on excel formula
 

Hi, i have a problem if i need to count the number of occurrences of odd
or even numbers in an array how should i go about writing it. COUNTIF
and COUNT does not seem to work. Haveing some difficulties in writing
the formula.

I.e from B3:H11 i need to count the number of times odd and even
numbers occurs; how should i write the formula or what formula should i
use. Please advice. Thank you. ^^:confused:


--
abc99
------------------------------------------------------------------------
abc99's Profile: http://www.excelforum.com/member.php...o&userid=35992
View this thread: http://www.excelforum.com/showthread...hreadid=557805

Newbie needs help on excel formula
 

Hello,

To count even numbers:
=SUMPRODUCT(--(MOD($B$3:$H$11,2)=0))

To count odd ones;
=SUMPRODUCT(--(MOD($B$3:$H$11,2)=1))

If both results do not add up to 63 then you have fractional numbers in
your area (1.1 or similar)...

HTH,
Bernd

Newbie needs help on excel formula
 

Hi,

for odd numbers:

=SUMPRODUCT((MOD($B$3:$H$11,2)=1)*1)

and for even numbers (including 0):

=SUMPRODUCT((MOD($B$3:$H$11,2)=0)*1)

Regards
Ingolf


abc99 schrieb:

Hi, i have a problem if i need to count the number of occurrences of odd
or even numbers in an array how should i go about writing it. COUNTIF
and COUNT does not seem to work. Haveing some difficulties in writing
the formula.

I.e from B3:H11 i need to count the number of times odd and even
numbers occurs; how should i write the formula or what formula should i
use. Please advice. Thank you. ^^:confused:


--
abc99
------------------------------------------------------------------------
abc99's Profile: http://www.excelforum.com/member.php...o&userid=35992
View this thread: http://www.excelforum.com/showthread...hreadid=557805

Newbie needs help on excel formula
 

On Mon, 3 Jul 2006 06:48:44 -0500, abc99 wrote:

Hi, i have a problem if i need to count the number of occurrences of odd
or even numbers in an array how should i go about writing it. COUNTIF
and COUNT does not seem to work. Haveing some difficulties in writing
the formula.

I.e from B3:H11 i need to count the number of times odd and even
numbers occurs; how should i write the formula or what formula should i
use. Please advice. Thank you. ^^:confused:

Create separate range of cells, with ones for odd and zeros for even, then
use COUNTIF formula.

Or, write a VBA function, name it CountOdd and use it in a formula.

Public Function CountOdd(r as Excel Range) As Long
dim retval as long
For Each c in r.Cells
if c.value mod 2 = 1 then retval = retval+1
Nect c
CountOdd=retval
End Function

Do similar thing for even values...

--
PL

Newbie needs help on excel formula
 

:eek: Wow it works it works!!!! Thank you all so much Thank you all.
/bow /bow wow looks like excel is gonna be fun learning from all of
you. Thank you:) :) :)


--
abc99
------------------------------------------------------------------------
abc99's Profile: http://www.excelforum.com/member.php...o&userid=35992
View this thread: http://www.excelforum.com/showthread...hreadid=557805