Odd Even formula

[serge]

I need a formula that will return 1 if the criteria is : Even,Odd,Even,Odd,Even,Odd,Even.
If not a blank cell.

Example :

A1:G1 2-5-8-5-6-3-0
H1 will return 1 or Blank.

Thank you.

 

[mikerickson]

How about
=IF(AND(ISODD(B1*D1*F1), ISEVEN(A1+C1), ISEVEN(E1)), 1, "")

or
=IF(6=SUMPRODUCT(MOD(COLUMN(A1:F1)+A1:F1, 2)), 1, "")

 

[serge]

Hi Mike thanks for answering.

The formula don't work !! it doesn't include the G cell could it be the problem ?

 

[pushkardey]

I need a formula that will return 1 if the criteria is : Even,Odd,Even,Odd,Even,Odd,Even.
If not a blank cell.

Example :

A1:G1 2-5-8-5-6-3-0
H1 will return 1 or Blank.

Thank you.

=IF((MOD(A1+C1+E1+G1,2)&MOD(B1+D1+F1,2))="01",1,"")

and

=IF(SUM(MOD(A1:G1,2))=3,1,"")

this is an array formula use Crtl+shift+enter after pasting this in H1

hope this helps...

 

[Armando Montes]

Maybe something like this:
=IF(AND(MOD(A1+C1+E1+G1,2)=0,MOD(B1+D1+F1,2)=1),1,"")

 

[serge]

Thank you pushkardey,

Your formula works fine.

 

[serge]

Armando, you formula also works good, Thank you for the help.

I should Have had ask this question in the first post but thought about it afterword:

Which formula would do the other way around ?
Example :

If I have : A1:G1 2-5-8-5-6-3-0

The formula will return : EOEOEOE or what ever the number would be.

Hope it's not to late to ask ?

Thank you for helping.

 

[Marcelo Branco]

Hi Serge,

You could use CONCATENATE like
=CONCATENATE(IF(MOD(A1,2)=0,"E","O"),IF(MOD(B1,2)=0,"E","O"),....

but with a long range it would be very tedious and prone to error

I think a better solution is:

Copy this UDF to a standard module

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
    Dim y As Variant
    If TypeOf a Is Range Then
        For Each y In a.Cells
            aconcat = aconcat & y.Value & sep
        Next y
    ElseIf IsArray(a) Then
        For Each y In a
            aconcat = aconcat & y & sep
        Next y
    Else
        aconcat = aconcat & a & sep
    End If
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

and in H1 enter this array-formula
=aconcat(IF(MOD(A1:G1,2)=0,"E","O"))
Ctrl+Shift+Enter

HTH

M.

 

[pushkardey]

Armando, you formula also works good, Thank you for the help.

I should Have had ask this question in the first post but thought about it afterword:

Which formula would do the other way around ?
Example :

If I have : A1:G1 2-5-8-5-6-3-0

The formula will return : EOEOEOE or what ever the number would be.

Hope it's not to late to ask ?

Thank you for helping.

=IF((MOD(A1+C1+E1+G1,2)&MOD(B1+D1+F1,2))="01",1,"")


just replace the underlined 1 with anything for e.g. "EOEOEOE" with the quotes...

should look like

=IF((MOD(A1+C1+E1+G1,2)&MOD(B1+D1+F1,2))="01","EOEOEOE","")

if i have understood the question properly this should work fine...

 

[serge]

Thank you Marcelo for giving me both solution.

But I choose your 1st one and it work fine, I'm trying not to put macro in my file, I'm able to play around with the formulas.

Thank you for the precious help.
Serge.