Welcome Guest
You last visited January 22, 2017, 7:16 pm
All times shown are
Eastern Time (GMT-5:00)

# Odd Even formula help

Topic closed. 12 replies. Last post 6 years ago by frenchie.

 Page 1 of 1
Los Angeles
United States
Member #75410
June 2, 2009
489 Posts
Offline
 Posted: May 22, 2011, 7:50 pm - IP Logged

Hi everyone,

I need a formula that will return 1 if the criteria is : E,O,E,O,E,O,E ( in that order ), If not a blank cell.

Example :

A1:G1      2-9-8-5-6-3-0

E-O-E-O-E-O-E       H1,     will return 1 if the digits are in that order if not a Blank cell.

Could you explain what need to be change in the formula to adapt it to a diferent order with the E,O as needed, please.

Example :

If I want        E,E,E,O,O,O,O        which formula ?

OR                 O,E,O,E,O,E,O        which formula ?

OR                 O,E,O,E,E,E,O         which formula ?

Etc... Etc...

From there I think I'll be able to change the formula to adapt it to what I need.

I'd like to have a formula for each posible order of E,O.

Serge.

NASHVILLE, TENN
United States
Member #33372
February 20, 2006
1044 Posts
Offline
 Posted: May 22, 2011, 8:48 pm - IP Logged

Is this for an Excel program?  If not, then what kind of programming language do you have?

Los Angeles
United States
Member #75410
June 2, 2009
489 Posts
Offline
 Posted: May 22, 2011, 10:34 pm - IP Logged

Excel 2007

United States
Member #73037
April 3, 2009
147 Posts
Offline
 Posted: May 22, 2011, 10:56 pm - IP Logged

Hello frenchie,

I use Lotus 123. The below formula works in a Pick 3 file.

B25 = 9

C25 = 9

D25 = 4

@IF(B25=@ODD(B25),"O","E")&" "&@IF(C25=@ODD(C25),"O","E")&" "&@IF(D25=@ODD(D25),"O","E")

and it results in "O O E"

If your using Excel, look at =ISEVEN (Returns TRUE if number is even, or FALSE if number is odd).

and =ISODD (Returns TRUE if number is odd, or FALSE if number is even). You can write a =IF formula using one of these

functions. You should be able to string them together as I have in Lotus 123.

I may have misunderstood your question, but the idea should help...

The only DUMB question is the one question you DID NOT ask...

Los Angeles
United States
Member #75410
June 2, 2009
489 Posts
Offline
 Posted: May 23, 2011, 12:11 am - IP Logged

Thanks for the reply but I don't know how to write those formula that's why I ask for help.

Los Angeles
United States
Member #75410
June 2, 2009
489 Posts
Offline
 Posted: May 23, 2011, 4:30 am - IP Logged

Hello frenchie,

I use Lotus 123. The below formula works in a Pick 3 file.

B25 = 9

C25 = 9

D25 = 4

@IF(B25=@ODD(B25),"O","E")&" "&@IF(C25=@ODD(C25),"O","E")&" "&@IF(D25=@ODD(D25),"O","E")

and it results in "O O E"

If your using Excel, look at =ISEVEN (Returns TRUE if number is even, or FALSE if number is odd).

and =ISODD (Returns TRUE if number is odd, or FALSE if number is even). You can write a =IF formula using one of these

functions. You should be able to string them together as I have in Lotus 123.

I may have misunderstood your question, but the idea should help...

You can also use this one :

=CONCATENATE(IF(MOD(A1,2)=0,"E","O"),IF(MOD(B1,2)=0,"E","O"),IF(MOD(C1,2)=0,"E","O"))

have your 3 digits in A1:C1.

But this is not, what I was asking for, what I want is a formula for each set of E-O of 7 digits.

The formula would return a ( 1 ) or a ( BLANK CELL ) when the digits math the criteria.

If the formula is set to find    EOEOEOE,   then it will return a ( 1 ) if not a ( Blank cell ).

And this for every different set of E-O that I would set up, that'is why I need to understand how the formula work, to set it up for the other set of E-O that I will create.That's it.

Thank you.

United States
Member #73037
April 3, 2009
147 Posts
Offline
 Posted: May 23, 2011, 12:29 pm - IP Logged

Morning Frenchie,

I see wait it is your looking for now. I misunderstood before. Since I work in Lotus, Momma has a computer that has Excel on it. Let me see if I can put a formula together for you. I would think that one of the other members that use Excel could do it quicker though.

I'll get back to you.

Sorry for the misunderstanding,

The only DUMB question is the one question you DID NOT ask...

United States
Member #73037
April 3, 2009
147 Posts
Offline
 Posted: May 23, 2011, 2:30 pm - IP Logged

frenchie,

Let's try this again...

I created the formula below using Quattro Pro 12 running in Excel mode. The program keep putting the "Array" around the formula each time I tested it until I got it built. It did work for what you were asking about. For future number patterns, you can modify each of the "ISEVEN" and "ISODD" to get the result that your looking for. The "=1" in each formula section means that the formula is "True".

@ARRAY(@IF(@ISEVEN(A1)=1#AND#@ISODD(B1)=1#AND#@ISEVEN(C1)=1#AND#@ISODD(D1)=1#AND#@ISEVEN(E1)=1#AND#@ISODD(F1)=1#AND#@ISEVEN(G1)=1,"Y","N "))

I modified this one by exchanging the "@" signs for the "=" signs and replacing the "Y" for "1" and "N" for " ".

=ARRAY(=IF(=ISEVEN(A1)=1#AND#=ISODD(B1)=1#AND#=ISEVEN(C1)=1#AND#=ISODD(D1)=1#AND#=ISEVEN(E1)=1#AND#=ISODD(F1)=1#AND#=ISEVEN(G1)=1,"1"," "))

I modified this one by removing the "Array" portion of the formula.

=IF(=ISEVEN(A1)=1#AND#=ISODD(B1)=1#AND#=ISEVEN(C1)=1#AND#=ISODD(D1)=1#AND#=ISEVEN(E1)=1#AND#=ISODD(F1)=1#AND#=ISEVEN(G1)=1,"1"," ")

The formula was built in cell H1. All I can suggest is to copy one formula at a time and see if 1 will work.

Good luck with it.

The only DUMB question is the one question you DID NOT ask...

Los Angeles
United States
Member #75410
June 2, 2009
489 Posts
Offline
 Posted: May 24, 2011, 2:09 am - IP Logged

I really do appreciate your help, and I gave it a try with no success.

I don't think Excel recognizes this kind of formula, It looks different than others.

But I'm no one to judge since I don't know how to build them.

obviously no one can help, so I'm will stop for this tread.

Thank you.

Serge.

United States
Member #105312
January 29, 2011
435 Posts
Offline
 Posted: May 24, 2011, 8:34 am - IP Logged

frenchie,

Let's try this again...

I created the formula below using Quattro Pro 12 running in Excel mode. The program keep putting the "Array" around the formula each time I tested it until I got it built. It did work for what you were asking about. For future number patterns, you can modify each of the "ISEVEN" and "ISODD" to get the result that your looking for. The "=1" in each formula section means that the formula is "True".

@ARRAY(@IF(@ISEVEN(A1)=1#AND#@ISODD(B1)=1#AND#@ISEVEN(C1)=1#AND#@ISODD(D1)=1#AND#@ISEVEN(E1)=1#AND#@ISODD(F1)=1#AND#@ISEVEN(G1)=1,"Y","N "))

I modified this one by exchanging the "@" signs for the "=" signs and replacing the "Y" for "1" and "N" for " ".

=ARRAY(=IF(=ISEVEN(A1)=1#AND#=ISODD(B1)=1#AND#=ISEVEN(C1)=1#AND#=ISODD(D1)=1#AND#=ISEVEN(E1)=1#AND#=ISODD(F1)=1#AND#=ISEVEN(G1)=1,"1"," "))

I modified this one by removing the "Array" portion of the formula.

=IF(=ISEVEN(A1)=1#AND#=ISODD(B1)=1#AND#=ISEVEN(C1)=1#AND#=ISODD(D1)=1#AND#=ISEVEN(E1)=1#AND#=ISODD(F1)=1#AND#=ISEVEN(G1)=1,"1"," ")

The formula was built in cell H1. All I can suggest is to copy one formula at a time and see if 1 will work.

Good luck with it.

Nice post KH.  Thanks.

United States
Member #73037
April 3, 2009
147 Posts
Offline
 Posted: May 25, 2011, 12:54 pm - IP Logged

Morning frenchie,

Sorry to read that the formula didn't work.

After thinking on your problem for a few days now, what I think you'll need is not a formula but a "custom" function that contains each of your "E-O-E-O-E-O-E"  patterns and can place a number in the cell to designate the "pattern number" for you.

This way whatever "E-O-E-O-E-O-E" pattern comes up in a draw, you'll know it right away and then you could "add" up how many times that pattern apperas in the draw history. I've created this type of "custom" function in Lotus 123 for a different problem, but not in Excel.

I am fairly confident that someone on this site has the capabilities to create a "custom" function for your idea, the problem is will they?

I'll keep looking into a solution for you though.

KH

The only DUMB question is the one question you DID NOT ask...

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
5980 Posts
Online
 Posted: May 25, 2011, 1:07 pm - IP Logged

Serge,

Is this what you're looking for?

 2985630 EOEOEOE

You will need the Morefunc add-in on your computer. Enter the Array formula with the Ctrl-Shift-Enter

=MCONCAT(IF(MOD((MID(\$AH11,{1,2,3,4,5,6,7},1)+0),2)=0,"E","O"))

Los Angeles
United States
Member #75410
June 2, 2009
489 Posts
Offline
 Posted: June 4, 2011, 2:58 pm - IP Logged

Hi CARBOB,

Sorry to take so long to respond, I tried your formula but I guess it won't work without the Morefunc add-in, and I don't know how to work it, so I find this formula that works fine for me :

=CONCATENATE(IF(MOD(A1,2)=0,"E","O"),IF(MOD(B1,2)=0,"E","O"),IF(MOD(C1,2)=0,"E","O"),IF(MOD(D1,2)=0,"E","O"),IF(MOD(E1,2)=0,"E","O"),IF(MOD(F1,2)=0,"E","O"),IF(MOD(G1,2)=0,"E","O"))

But I want to thank you for the help.

 Page 1 of 1