Lottery Filter#2, Low/High Digits, in Single CELL with "-"

[david gonzalez]

Hi,

How i do, i'm trying to apply some lottery filters on a Tickets that i'm producing on daily basis,
(i know many on them)

this filter i Called "Low/High"

For example :

1-.the Lottery have number is 36 (lowest 1, Highest 36)

2-.the 36 Numbers are Identified as two section (Low= 1~18), and (High=19~36)

3-.In order to Filter out "bad combination", the Array Formula (if any) should
a-.identify / count how many numbers are from LOW and
b-.how many numbers are HIGH

Note :
Combination are in a single CELL,
Combination are with "-" e.i ( 01-02-03-04-05 )
Combination are with 2 digits e.i ( 01-02-03-04-05 )


Example :

          Column G         Column H    Column J     
          Pick Numbers       SUM       LOW/HIGH
G6  =    22-33-34-35-36      160        0/5 
G7  =    08-28-29-30-35      130        1/4
G8  =    01-18-23-33-35      110        2/3

please I need to get L/H on Column J

Is there a way to do this with function formula?


Thanks YOU ALL.

David.

[Motu040602]

Try this in your J row cells

=IF(INT(LEFT(G1,2))<19,1,0)+IF(INT(MID(G1,4,2))<19,1,0)+IF(INT(MID(G1,7,2))<19,1)+IF(INT(MID(G1,10,2))<19,1,0)+IF(INT(RIGHT(G1,2))<19,1,0)&"/"&(5-(IF(INT(LEFT(G1,2))<19,1,0)+IF(INT(MID(G1,4,2))<19,1,0)+IF(INT(MID(G1,7,2))<19,1)+IF(INT(MID(G1,10,2))<19,1,0)+IF(INT(RIGHT(G1,2))<19,1,0)))

It is long sorry!

[david gonzalez]

Hi Mr Motu040602,

For some reason i'm getting #VALUE message,
I copied the string in G6 (and i replaced G1 for G6)

i appreciated a lot, don't worry its working with this Array :

=SUM(IF(INT(MID(G6,1,2))<=18,1,0)+IF(INT(MID(G6,4,2))<=18,1,0)+IF(INT(MID(G6,7,2))<=18,1,0)+IF(INT(MID(G6,10,2))<=18,1,0)+IF(INT(MID(G6,13,2))<=18,1,0)) & "/" & SUM(IF(INT(MID(G6,1,2))>18,1,0)+IF(INT(MID(G6,4,2))>18,1,0)+IF(INT(MID(G6,7,2))>18,1,0)+IF(INT(MID(G6,10,2))>18,1,0)+IF(INT(MID(G6,13,2))>18,1,0))

Best Regards

David

[NigelFrost]

I would think a simpler way of doing it would be via your own user function or by first splitting the cells into columns eg. column H to L would be the individual numbers (use MID). However if you really want to do it with a single formula try:

=SUM(IF(INT(MID(G6,1,2))<=18,1,0)+IF(INT(MID(G6,4,2))<=18,1,0)+IF(INT(MID(G6,7,2))<=18,1,0)+IF(INT(MID(G6,10,2))<=18,1,0)+IF(INT(MID(G6,13,2))<=18,1,0)) & "/" & SUM(IF(INT(MID(G6,1,2))>18,1,0)+IF(INT(MID(G6,4,2))>18,1,0)+IF(INT(MID(G6,7,2))>18,1,0)+IF(INT(MID(G6,10,2))>18,1,0)+IF(INT(MID(G6,13,2))>18,1,0))

basically just separates out each number using MID and will do a count if <=18 then if > 18

Apologies, did not refresh before answering the question, already answered above

[david gonzalez]

Hi Mr NigelFrost,

Thank you so much, it work perfect.


Thank you

David

[Motu040602]

Funny how much the 2 formulas are alike...lol!

[XOR LX]

Or:

=REPLACE(TEXT(SUM(ABS(10^{2,0}*((1+LEN(G6)-LEN(SUBSTITUTE(G6,"-","")))*{0,1}-SUMPRODUCT(--((--TRIM(MID(SUBSTITUTE(G6,"-",REPT(" ",255)),255*(ROW(INDIRECT("1:"&1+LEN(G6)-LEN(SUBSTITUTE(G6,"-",""))))-1)+1,255)))<=18))))),"000"),2,1,"/")

Regards

[david gonzalez]

Hi Mr XOR LX,

Thank you, it worked !!!


Best regards

David

[XOR LX]

You're welcome.

[Tony Valko]

Here's another one...

Data Range

	G	H	I	J
5		-----	-----	-----
6	22-33-34-35-36	160	7	0/5
7	08-28-29-30-35	130	4	1/4
8	01-18-23-33-35	110	2	2/3

Entered in J6 and copied down:

=SUM(--(--MID(G6,{1,4,7,10,13},2)<=18))&"/"&SUM(--(--MID(G6,{1,4,7,10,13},2)>=19))