Even Number Formula?

  • Thread starter Thread starter dkenebre
  • Start date Start date
D

dkenebre

How do I express the following as an excel formula:

A1, B1 and C1 will always have one number in it's cell. Therefore,
If A1, B1 and C1 each have a one number that is either 0,2,4,6 or 8,
then D1 =3, example 2,4,6
Also,
If A1, B1 and C1 have only 2 of the cells with one number that is
either 0,2,4,6 or 8, then D1 =2, example 2,4,7
Also,
If A1, B1 and C1 have only 1 of the cells with one number that is
either 0,2,4,6 or 8, then D1 =1, example 2,5,7
Also,
If A1, B1 and C1 have none of the cells with one number that is
either 0,2,4,6 or 8, then D1 =4, example 1,5,7
 
How do I express the following as an excel formula:

A1, B1 and C1 will always have one number in it's cell. Therefore,
If A1, B1 and C1 each have a one number that is either 0,2,4,6 or 8,
then D1 =3, example 2,4,6
Also,
If A1, B1 and C1 have only 2 of the cells with one number that is
either 0,2,4,6 or 8, then D1 =2, example 2,4,7
Also,
If A1, B1 and C1 have only 1 of the cells with one number that is
either 0,2,4,6 or 8, then D1 =1, example 2,5,7
Also,
If A1, B1 and C1 have none of the cells with one number that is
either 0,2,4,6 or 8, then D1 =4, example 1,5,7
Click to expand...


Array-enter:

=(SUM(MOD(A1:C1,2))=3)*4+SUM(3,-MOD(A1:C1,2))

To array-enter a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.


--ron
 
Not sure if I am reading the question correctly.You might try:
=ISEVEN(A1)+ISEVEN(B1)+ISEVEN(C1)
To avoid counting blanks as zeros:
=ISEVEN(A6)+ISEVEN(B6)+ISEVEN(C6)-(ISBLANK(A6)+ISBLANK(B6)+ISBLANK(C6))

Bernard
 
Thanks Ron and Bernard for your help.
Ron your formula did not work for me, but
Bernard your formula did. Although, I did want
zero-evens to be expressed as 4 instead of the obvious
0, but that's okay. What would be cool is if I could assigned
a color to each result with this formula.
For example
A 0 result = black background
1= Red
2= Blue
3= Green
 
Ron your formula did not work for me,
Click to expand...

What happened?

Did you *array-enter* the formula?

Bernard's formula is dependent on the Analysis ToolPak being installed but will
work fine if it is.


--ron
 
What do you mean by array enter. Does not mean the enter the correct
cells within the formula, then I have none that, my arrays would x373,
y373 and z373?

Also, do you know how I can assigned a specific background color to
results?
 
"Array Enter": when you have completed typing the formula hold down the
three keys Shift+Ctrl+Enter (I generally hold Shift & Ctrl down and tap the
Enter key)
Bernard
 
What do you mean by array enter. Does not mean the enter the correct
cells within the formula, then I have none that, my arrays would x373,
y373 and z373?

Also, do you know how I can assigned a specific background color to
results?
Click to expand...

After typing in or pasting in the formula, to array-enter a formula, hold down
<ctrl><shift> while hitting <enter>. XL will place braces {...} around the
formula.

To assign a specific color, you can use conditional formatting. See
Format/Conditional Formatting. You are limited to three conditions. That plus
the basic formatting gives you four possibilities. If you need more, then you
must use a VBA solution.



--ron
 
How do I express the following as an excel formula:

A1, B1 and C1 will always have one number in it's cell. Therefore,
If A1, B1 and C1 each have a one number that is either 0,2,4,6 or 8,
then D1 =3, example 2,4,6
Also,
If A1, B1 and C1 have only 2 of the cells with one number that is
either 0,2,4,6 or 8, then D1 =2, example 2,4,7
Also,
If A1, B1 and C1 have only 1 of the cells with one number that is
either 0,2,4,6 or 8, then D1 =1, example 2,5,7
Also,
If A1, B1 and C1 have none of the cells with one number that is
either 0,2,4,6 or 8, then D1 =4, example 1,5,7
Click to expand...

An alternative that scales more easily for more cells and doesn't require
anything other than built-in functionality.

=MOD(SUMPRODUCT(--(MOD(A1:C1,2)=0))-1,4)+1
 
Back
Top