Looking for Excel Formula

Reply #19

AllenB — Fri, 21 Aug 2015 19:25:30 GMT

Hits

Reply #18

AllenB — Fri, 21 Aug 2015 19:24:41 GMT

I don't know where the Snip came from. i was using Game snip for the range name

This post has been automatically changed by the Lottery Post computer system to remove inappropriate content and/or spam.

Reply #17

AllenB — Fri, 21 Aug 2015 19:22:16 GMT

Depending on Your data source there are several ways to do this. For CA Fantasy 5 I have used this approach.

Dates (A), Game# (B) and Picks (C-G) are listed in the first 6 columns in the second row.

in the next 39 (H-AT) columns (top row) enter 1 - 39

under each number enter the following formula. =if(countif($C3:$G3,H$1)=1,+$B3,-1)

This formula places the game number in the cell if the number in line 1 was in the set drawn and -1 if it is not.

Copy the formula in all of the cell... [ More ]

Reply #16

Raven62 — Fri, 21 Aug 2015 18:27:37 GMT

Thanks for responding, I'm always open to food for thought when it comes to Excel and Tracking Lottery Results.

Reply #15

CARBOB — Fri, 21 Aug 2015 17:34:11 GMT

The results are too unevenly split, not what I was looking for.

Reply #14

CARBOB — Fri, 21 Aug 2015 16:48:57 GMT

=SUBSTITUTE(ADDRESS(1,(D3-1)/50+1,4), 1 , )

This formula puts everything in a group of 50.

Reply #13

Raven62 — Fri, 21 Aug 2015 12:30:15 GMT

Your Welcome Carbob!

If you're willing to share the alternate way of doing the same thing I'd like to hear about it.

Reply #12

Raven62 — Fri, 21 Aug 2015 12:26:22 GMT

You can: Teach Yourself, Take a DVD Course, Attend a Class at the Library or Local School where you live.

Reply #11

CARBOB — Fri, 21 Aug 2015 11:10:30 GMT

You can forget this thread, figured out a different way of doing the same thing. Thanks to Raven62 and Steve, much appreciated.

Reply #10

CARBOB — Fri, 21 Aug 2015 07:42:08 GMT

1726

956

748

48

Thanks Steve, that works, with one exception. Next question, just to clarify. The values listed are in descending order, current to oldest. I count 3 draws as being current skip, right or wrong? Or is it 4, which is what your formula returns. If my count is right, I can compensate with a -1. Just want to be sure of the count.

It only works for one instance. It doesn't work if I change the range from =50 =100, returns wrong value.

Reply #9

sully16 — Fri, 21 Aug 2015 07:13:04 GMT

Wish I even knew how to use it.

Reply #8

winsumloosesum — Thu, 20 Aug 2015 23:27:01 GMT

Bob,

Not sure is this is what you wanted:

=MATCH(1,--(B2:B5136 =1)*--(B2:B5136 =50),0) Ctrl Shift Enter

Reply #7

CARBOB — Thu, 20 Aug 2015 19:07:27 GMT

No, the formula still gives an error message.

=IF(COLUMNS($R10:R10) =COUNTIFS($B$2:$B$5316, =1 ),COUNTIFS($B$2:$B$5316, =50 ),COUNTIFS($B$2:$B$5316, =1 ),COUNTIFS($B$2:$B$5316, =50 ),ROW($B$2:$B$5316)-ROW($B$2)),COLUMNS($R10:R10

Reply #6

Raven62 — Thu, 20 Aug 2015 17:49:24 GMT

Oops missing )'s

=IF(COLUMNS($R10:R10) =COUNTIFS($B$2:$B$5316, =1 ),COUNTIFS($B$2:$B$5316, =50 ),COUNTIFS($B$2:$B$5316, =1 ),COUNTIFS($B$2:$B$5316, =50 ),ROW($B$2:$B$5316)-ROW($B$2)),COLUMNS($R10:R10))

Was it your intention to include an AND or an OR

Reply #5

CARBOB — Thu, 20 Aug 2015 17:16:55 GMT

Still getting error message.

https://app.box.com/s/tbonkt9m0jxv2q6hed2zfj8domuygz9j

Reply #4

Raven62 — Thu, 20 Aug 2015 17:09:52 GMT

Compare the formula you posted with the copy pasted formula posted with corrections applied.

Reply #3

CARBOB — Thu, 20 Aug 2015 15:37:49 GMT

Thanks for responding, where is that entered into the formula

Reply #2

Raven62 — Thu, 20 Aug 2015 15:25:41 GMT

=IF(COLUMNS($R10:R10) =COUNTIFS($B$2:$B$5316, =1 ,COUNTIFS($B$2:$B$5316, =50 ,COUNTIFS($B$2:$B$5316, =1 ,COUNTIFS($B$2:$B$5316, =50 ,ROW($B$2:$B$5316)-ROW($B$2)),COLUMNS($R10:R10))

Missing , =1

Reply #1

CARBOB — Thu, 20 Aug 2015 14:38:51 GMT

This is the formula, I am hoping someone can explain the syntax. Keep getting formula contains an error.

=IF(COLUMNS($R10:R10) =COUNTIFS($B$2:$B$5316, =1 ,COUNTIFS($B$2:$B$5316, =50 ,COUNTIFS($B$2:$B$5316 =1,COUNTIFS($B$2:$B$5316 =50,ROW($B$2:$B$5316)-ROW($B$2)),COLUMNS($R10:R10

Looking for Excel Formula

CARBOB — Thu, 20 Aug 2015 14:02:31 GMT

Need an Excel formula that will measure skips using the = and = commands. Anyone have one and would like to share, I would be much obliged.