# EXCEL Experts

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.

Empassioned1, you can try this formula.
=RIGHT((A1+B1+C1),1)
That should leave you with just the ones position.

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.

Try this:

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

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

=MOD(A1+B1+C1, 10)

If your sum total is in E3

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

wsls

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!!

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

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

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

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.

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)) '