Saturday, February 06, 2010

Count Even or Odd numbers in Arrays

There are several ways to count even or odd numbers in Excel. The simplects one is probably if you are familiar with Array Formulas.

Let's say you have an array A1:A100 with integers.
Enter the folloring formulas into the result cells, say C1 and D1 :
(the curely braces are automatically entered by Excel afret you type into the formula bar the formula and press Shift+Ctrl+Enter. Do not enter them manually.)

Odds {=SUM((ODD(A1:A100)=A1:A100)*1)}


Evens {=SUM((EVEN(A1:A100)=A1:A100)*1)}

Explanation:
The Odd and Even function is compared to the original value and return True or False.
Then it is multiplied by 1 to transtale the True / False into 1 / 0 respectively.
The SUM function then sums the ones to get the expected result.

If you don't use array formulas just create the formulas in column B1:B100 and count the results using the COUNT function.

1 comment:

  1. Thanks a lot, i was looking for this simple solution long time ago.

    thanks!

    ReplyDelete

Enter your comment here