# Excel goodies for PowerBall, and a question....

#### hypersoniq

##### Member
Here are some simple Excel formulas to apply up-down, Decade, and Even-Odd transforms to Powerball Data

Formula for Up-Down...
=IF(C2<C3,"U",IF(C2=C3,"x","D"))

=IF(C2>49,6,IF(C2>39,5,IF(C2>29,4,IF(C2>19,3,IF(C2>9,2,1)))))

Formula for Even-Odd
=IF((C2/2)-(INT(C2*0.5))=0,"E","O")

Counting times a number was drawn...
1. number 1 to 53 down a column, using the first cell for a label (for example column AD)
2. enter this into cell AE2...
3. autofill down to 53
4. enter the following in cell AF2...
5. autofill across to column AK
6. autofill columns AF thru AJ down to 53
7. autofill column AK down to 42
this gives times a number was drawn and breaks it down into what position (SORTED)

my sheet is set up with the following columns...
A = Draw Date
B = Draw Number
C = White Ball 1 (SORTED order)
D = White Ball 2
E = White Ball 3
F = White Ball 4
G = White Ball 5
H = PowerBall (Red)
also: sum, sum+powerball, day of draw (W or S), powerplay, jackpot value and # of winners in each prize tier

The formulas are designed to exploit autofill, only need to type once I have a question... what are they good for? How can you use the results to help in making your pick?
Is it just meant as a guide or is there some way to apply this with precision? I'm relatively new to all of this...

#### hypersoniq

##### Member
another question, is there an easy way to leave a formula open-ended? such as a range to include the latest drawing? I have just been changing and re auto-filling the number count range.
ie... \$C\$2:\$H\$76 needs to be \$C\$2:\$H\$77 after saturday's drawing.

#### PAB

##### Member
Formula for Even-Odd

For working out if a Number is "ODD" or "EVEN" you could use the Formula :-

=IF(MOD(C2,2)=0,"ODD","EVEN")

Or

=IF(MOD(C2,2)=0,"O","E")

For counting the number of times a Ball has been drawn you could use the "FREQUENCY" Function.

Regards
PAB

#### PAB

##### Member
Conditional Formatting

Hi Everyone,

I know this is probably easy to do but I seem to have a mental block.
I want to set up a "Conditional Format" that will turn a cell ( or cells ) red if the "Minimum" is greater than 0 ( Zero ). The column does contain Zeros which I want ignored.

The Conditional Format :-
Cell Value Is Equal To =MIN(A\$1:A\$1000) will Highlight the Zeros.
I have tried different combinations of putting >0 into the Formula but nothing seems to work.
It is for a column I have set up for Consecutive Numbers, and I want it to ignore Zero.

Any help would be appreciated.

All the Best
PAB 