Welcome Guest
You last visited December 8, 2016, 10:44 pm
All times shown are
Eastern Time (GMT-5:00)

# EXCEL Experts

Topic closed. 11 replies. Last post 11 years ago by PitchDark.

 Page 1 of 1
Saint Louis, Missouri
United States
Member #17000
June 9, 2005
46 Posts
Offline
 Posted: June 29, 2005, 12:21 am - IP Logged

Can someone point me to or help me with an excel formula or how to perhaps an if then statement.

What I am trying to do is add a list of three numbers in three columns, with each sum I ONLY want the ones place and nothing more.  Since the largest number would be 27 (9+9+9)...I'm tossing out 10 or 20's place.

for example: if I added 6+4+2=12 I only want the 2, not the 10 infront of it.

Make sense?  So in Excel I would have a sum of say: A3+B2+C1  Now what I need to know is if that total is over 10 or 20 how to get the cell to show only the ones place

This brings me to another potential problem too.  IF I get it to show only the ones place, that number is used in the next sum, will it take the number shown or will it attempt to use the original sum of the cell?

I'm trying to save myself a lot of paper work, but if I have to write it all out by hand I will.  I have already figured out the base sums for each week (looking at box patterns based on previous week's draw)

Thanks,

C.K.

If the eyes are the windows to the soul, let me see you without the shades drawn

What's a Hades Bunny?  Well let's just say you don't wanna be jumpin' over MY back fence.  But hey, you ain't afraid a lil white rabbit now are ya?

Minnesota
United States
Member #1367
April 12, 2003
37 Posts
Offline
 Posted: June 29, 2005, 12:45 am - IP Logged
Empassioned1, you can try this formula.
=RIGHT((A1+B1+C1),1)
That should leave you with just the ones position.

I know the voices aren't real, but they sure have some good ideas.

Saint Louis, Missouri
United States
Member #17000
June 9, 2005
46 Posts
Offline
 Posted: June 29, 2005, 3:00 am - IP Logged

Hmmm, tried that, the chart I had suddenly filled each cell with "#value"

I think for now, since the 1's digit are consistant (nothing carries over like the 10's 100's or higher) I can plug the formula and simply make sure I am only pulling the 1's position for each sum in each cell.

It's nearly 2 am here, so perhaps I'll try this method again tomorrow with a clean Excel document and if it works (on more sleep) I'll redo the chart from the top.

Thanks for the help by the way!

C.K.

If the eyes are the windows to the soul, let me see you without the shades drawn

What's a Hades Bunny?  Well let's just say you don't wanna be jumpin' over MY back fence.  But hey, you ain't afraid a lil white rabbit now are ya?

The Quantum Master
West Concord, MN
United States
Member #21
December 7, 2001
3675 Posts
Offline
 Posted: June 29, 2005, 3:28 am - IP Logged

Try this:

=(A1+B1+C1)-10*(INT((A1+B1+C1)/10))

Presented 'AS IS' and for Entertainment Purposes Only.
Any gain or loss is your responsibility.

Order is a Subset of Chaos
Knowledge is Beyond Belief
Wisdom is Not Censored
Douglas Paul Smallish
Jehocifer

Chief Bottle Washer
New Jersey
United States
Member #1
May 31, 2000
23273 Posts
Online
 Posted: June 29, 2005, 6:57 am - IP Logged

The easiest was to strip the 10's column is to use a MOD function.

=MOD(A1+B1+C1, 10)

Check the State Lottery Report Card

Sign the Petition for True Lottery Drawings
Help eliminate computerized drawings!

Pennsylvania
United States
Member #2218
September 1, 2003
5387 Posts
Offline
 Posted: June 29, 2005, 9:53 am - IP Logged

If your sum total is in E3

=IF(E3<10,E3,IF(E3<20,E3-10,E3-20))

wsls

Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
 Posted: June 29, 2005, 12:11 pm - IP Logged

Looking for a formula that will return a result of how many days out a pair is.

Pair's location start in (V2) and in in V46.  Am I to use lookup?

Any help is appreciated!!

Pennsylvania
United States
Member #2218
September 1, 2003
5387 Posts
Offline
 Posted: June 29, 2005, 2:32 pm - IP Logged

lottaloot,

One idea is to add another tab to your excel worksheet and name it Pairs.

In cell a1 through cell a100 would be all your pairs 00 through 99.

The Pick 3 history drawings, I use is named Pick3. a5 contains my date, b5 is my 3 digit number drawn, and c5 is my Front Pair.

In cell b1 enter the following formula in the Pairs worksheet:

=MATCH(\$A1,INDIRECT("Pick3!c5:c1000"),0)-1

This will give you the number of drawings since this Front Pair hit. Just repeat for Back Pair and Side Pairs.

1000 draws should give you enough history for pairs.

Hope thsi helps!!

wsls

Chief Bottle Washer
New Jersey
United States
Member #1
May 31, 2000
23273 Posts
Online
 Posted: June 29, 2005, 4:25 pm - IP Logged

If your sum total is in E3

=IF(E3<10,E3,IF(E3<20,E3-10,E3-20))

wsls

Unfortunately, a limited forumla such as that will crash when the parameters are out of its range (for example, greater than 40).  You are much better off using a formula such as the MOD() that I recommended above, since it will not crash like that, no matter what number you use.  Always look for that which is (a) simplest, and (b) most flexible

Check the State Lottery Report Card

Sign the Petition for True Lottery Drawings
Help eliminate computerized drawings!

Pennsylvania
United States
Member #2218
September 1, 2003
5387 Posts
Offline
 Posted: June 29, 2005, 4:59 pm - IP Logged

I thought empassioned was just adding 3 columns for 3 digits.

The largest number would be 27. (9 + 9 + 9 =27)

That's what I thought the request was for.

My mistake.

wsls

Saint Louis, Missouri
United States
Member #17000
June 9, 2005
46 Posts
Offline
 Posted: June 29, 2005, 6:08 pm - IP Logged

OK! Now'm on the downhill slope!  The =MOD is the simplest, although the IF formula WSLS gave me works as well, just needed some extra cells to make the chart work.

First off, Thank YOU guys and gals!  I always appreciate the help, and as I get this initial calculation work done, I'll post more in a new thread or update the original thread where this all started with Atomic Dog's "Union Jack" method.

Thus far, I've had a box or straight hit every single week.  Now I'm doing the math to see what patterns evolve over close to a year's draws using the "computerized" draws rather than going back before when the draws were ball derived.

Thanks again for the help!!

This is getting to be just too cool!

C.K.

If the eyes are the windows to the soul, let me see you without the shades drawn

What's a Hades Bunny?  Well let's just say you don't wanna be jumpin' over MY back fence.  But hey, you ain't afraid a lil white rabbit now are ya?

New Member
Milwaukee
United States
Member #18118
July 5, 2005
1 Posts
Offline
 Posted: July 5, 2005, 2:55 pm - IP Logged

I've used this in a spread sheet and have tested it. What it will not do is catch values over 27.

This should do what you want it to ' =IF(A1> 19,A1-20,IF(A1>9,A1-10,A1)) '

 Page 1 of 1