Excel help for Pick 3

[Icewynd]

Hi PAB,

I'm hoping that you can help me complete the automation of my Pick 3 spreadsheet. Everything else in the spreadsheet has Excel formulas to complete what I want to do, but this set of tasks has stumped me.

Here's what I am trying to do:
1. Calculate the frequency of each digit (0-9) for the last 30 games.
2. Sort the digits by frequency. The top 3 are designated as "Hot" digits, the middle 4 are "Warm" and the bottom 3 are considered "Cold".
3. Record the 3 winning digits from each draw sorted according to frequency and record the pattern of Hot, Warm, Cold.
4. The sorted digits are assigned to a letter of the alphabet with "A" being the hottest digit and "J" being the coldest. The alpha code for the 3 winning digits is recorded.
5. A hit-and-skip chart of the Alpha values is created, such that a hit is given a zero and a skip adds one to the previously existing value.

It is the alphabetic values that seem to be interfering here, as my normal Excel formulas (e.g. for hit-and-skip) don't recognize the alpha equivalents.

I have uploaded a sheet to MediaFire to help you understand what I am doing:

Pick 3 ON Frequency.xlxs

http://www.mediafire.com/?cv417drv1k7u420

Thanks for looking at this.

 

[PAB]

Hi Icewynd,

I have had a quick look at your SpreadSheet.
There are quite a few numeric values ONLY in cells AH:AY & BC:BE & BR:BT without any formulas.
As you know, I don't play this sort of game so telling me how you arrived at the values will help me to understand clearer what you are trying to do.

It is the alphabetic values that seem to be interfering here, as my normal Excel formulas (e.g. for hit-and-skip) don't recognize the alpha equivalents.

Without fully understanding I would assume that you are looking towards some sort of a LOOKUP table for the transference or maybe a coherce formula from text to value or vice versa!!!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[Icewynd]

There are quite a few numeric values ONLY in cells AH:AY & BC:BE & BR:BT without any formulas.
As you know, I don't play this sort of game so telling me how you arrived at the values will help me to understand clearer what you are trying to do.

Hi PAB,
Thanks for having a look.

By way of explanation, Pick 3 draws a 3-digit combination based on the digits 0-9. These can be all unique digits (e.g. 094), can have one digit repeat (e.g. 227) or can have only one digit (e.g. 333). The game is to predict the correct combination that will be drawn next. One tool I use for prediction is the frequency of hits for each digit in recent games. For example, about 25% of game winners will contain only "Hot" or only "Cold" digits.

AH:AQ contains the digits 0-9 sorted according to frequency (the number of times they have hit in the last 30 games). As you can see, I count the hits in columns R:AA for each game, then sort them at the top of the page (AH2:AQ3) and then use Paste,Values to transfer them into columns AH:AQ for that draw. Pretty klunky, huh?

AS:AU contains the 3 winning digits for each draw, color-coded as Hot/Warm/Cold, and in AW:AY I have recorded the number of Cold/Warm/Hot digits for each draw. Column BA counts the number of digits in each category and gives the pattern (I have a formula for this one).

Starting in column BC I assign a letter to each winning digit, based on its frequency. For example, A is the hottest (highest frequency) digit each time, but will have a different digit assigned to it as the patterns of the game shift. BG:BP records the hit and skip values for each of these alphabetic designations. What this chart says is "the hottest digit of the 10 has hit 3 times in a row and this is not likely to happen a fourth time", or "no cold digits have hit for 5 games -- look for a turnaround".

Without fully understanding I would assume that you are looking towards some sort of a LOOKUP table for the transference or maybe a coherce formula from text to value or vice versa!!!

Yes, that is the idea. In fact, the "Lookup Table" is in AH2:AQ2 and AH4:AQ4. The problem that I am having is that this table changes with each new draw as the numbers are sorted. I have never heard of a coherence table, so I can't comment.

Thanks, again!

 

[PAB]

Hi Icewynd,

AH:AQ contains the digits 0-9 sorted according to frequency (the number of times they have hit in the last 30 games). As you can see, I count the hits in columns R:AA for each game, then sort them at the top of the page (AH2:AQ3) and then use Paste,Values to transfer them into columns AH:AQ for that draw. Pretty klunky, huh?

Am I right in saying that the reason there are NO formulas in this section is because when a new draw is added it would screw up all the previous draws?

If that is the case perhaps you could do one of two things:
(1) Code the process to calculate the new figures and then enter the values into cells AH:AQ.
(2) Use part Formula & part Code.

Yes, that is the idea. In fact, the "Lookup Table" is in AH2:AQ2 and AH4:AQ4. The problem that I am having is that this table changes with each new draw as the numbers are sorted. I have never heard of a coherence table, so I can't comment.

Is it just the formula in cells BC:BE that you are after???
What you could do is formulate cells BC:BE and then just code it to paste the values only before you enter the next draw data???

Am I on the right sort of lines???

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[PAB]

Hi Icewynd,

I have just been thinking about this .
You will NOT be able to formulate cells BC:BE because an HLOOKUP table needs to be in ascending order, which will NOT be the case, well, on a very rare occasion maybe!

What you could do is to add a VLOOKUP table, say in columns CA:CB, and then sort that table in ascending Numberic values and put the values in cells CD:CE using Code, then use a formula to pick up the relevant Alphas in cells BC:BE and then use code to replace the formulas with values, OR, do the whole process using Code!

Am I along the right sort of track or am I totally over thinking and complicating this and I am totally on the wrong track???

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[Icewynd]

Am I right in saying that the reason there are NO formulas in this section is because when a new draw is added it would screw up all the previous draws?

No. Alas, it is just because I haven't been able to sort out how to write a formula that will put the sorted digits where I want them.

Is it just the formula in cells BC:BE that you are after???
What you could do is formulate cells BC:BE and then just code it to paste the values only before you enter the next draw data???

BC:BE is an important part of it, but I also use AH:AQ in another sheet to calculate Hot (top 5) and Cold(bottom 5). The patterns in AW:BA are also information that I use.

You will NOT be able to formulate cells BC:BE because an HLOOKUP table needs to be in ascending order, which will NOT be the case, well, on a very rare occasion maybe!

Yes, I have already struggled with HLOOKUP.

What you could do is to add a VLOOKUP table, say in columns CA:CB, and then sort that table in ascending Numberic values and put the values in cells CD:CE using Code, then use a formula to pick up the relevant Alphas in cells BC:BE and then use code to replace the formulas with values, OR, do the whole process using Code!

Am I along the right sort of track or am I totally over thinking and complicating this and I am totally on the wrong track???

Yes, this sounds like what I need. One possible issue that I see is that currently I am assigning the Alpha values in BC:BE by hand, so it would be better if, once the sort was done, that the Alphas could be assigned to the sorted order as in AH2:AQ4.

As for over-complicating -- I think that I have done that for you!

Thanks for all the hard work you are putting into this problem!

 

[PAB]

Hi Icewynd,

I have uploaded the new file for you.
I have modified some of the formulas so that the values are NOT hard coded now as well as adding some new formulas.
I have written some code to calculate the Alpha numbers, please let me know what you think!

You could also write some added code to:

(1) Calculate The Frequency For The Last [30] Draws.
(2) Calculate HWC in columns AH:AQ.

http://www.mediafire.com/download/6bvqp545ct2wx0n/Pick_3_ON_Frequency_v1.0.xlsm

EDIT:

You will need to enter the new draw digits in columns C:E BEFORE you hit the Calculate button in the top left hand corner of the SpreadSheet.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[Icewynd]

Hi PAB,

Thank you! This appears to be working.

Small problem -- I cannot see the values in columns C:E and column AS. If I click on the cells I can see the number in the formula bar, but it does not show up on the spreadsheet. I have checked font color, format etc.

 

[PAB]

Hi Icewynd,

Thank you! This appears to be working.

Small problem -- I cannot see the values in columns C:E and column AS. If I click on the cells I can see the number in the formula bar, but it does not show up on the spreadsheet. I have checked font color, format etc.

I have just downloaded the file and I can see the values in columns C:E!!! - it's not column width is it???

As for column AS, I can also see the values in column AS, BUT, change the formula anyway in cell AS50 to...

=IF(C50="","",C50)

...and copy it across and down.

Did you see my EDIT in my previous post???

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[PAB]

Hi Icewynd,

I decided to have a go and totally automate the file for you so you ONLY have to input the three digits of the draw and then click the Calculate button and it calculates ALL the data.
I have also amended some of the formulas that stay formulas within the WorkSheet.
I also had to fill in the gap from cells AW356:BE510 in order for one of the formulas in the code to work properply.
DO NOT insert columns or rows otherwise the program will produce the wrong results.
I know the code is very messy, I have left the code that I tested in there, but if it works OK then I will tidy it up for you.
Please let me know what you think!

http://www.mediafire.com/download/ct7jucj3sjsmq39/Pick_3_ON_Frequency_v4.2.xlsm

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[jack]

Hello, ice very good, you can also make by position separately, as well as vertically every three draws, ie now you're doing across by all the pick3 but can vertically in each three draws, and position, example the frequency in a horizontal
* The digit hot is 5.6 vertical is 5.8 position is 3 understands!

 

[PAB]

Well, we will ignore the previous post .

It has NOTHING to do with what we are talking about or anything to do with what Icewynd is after, he is after an Excel formula solution for using ALPHA values as opposed to NUMERIC values in his SpreadSheet.

Right, back to normal transmission.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[Icewynd]

Hello, ice very good, you can also make by position separately, as well as vertically every three draws, ie now you're doing across by all the pick3 but can vertically in each three draws, and position, example the frequency in a horizontal!

Yes, but that is different than what I am trying to do here.

* The digit hot is 5.6 vertical is 5.8 position is 3 understands!

 

[Icewynd]

I decided to have a go and totally automate the file for you so you ONLY have to input the three digits of the draw and then click the Calculate button and it calculates ALL the data.
I have also amended some of the formulas that stay formulas within the WorkSheet.
I also had to fill in the gap from cells AW356:BE510 in order for one of the formulas in the code to work properply.

Wow! PAB that is absolutely above and beyond. Thank You!

DO NOT insert columns or rows otherwise the program will produce the wrong results.

Understood.

[I know the code is very messy, I have left the code that I tested in there, but if it works OK then I will tidy it up for you.
Please let me know what you think!

Not worried about messy code, no need to do a clean up.

Everything is working correctly right now, except for the assignment of alpha codes for the winning numbers.

For example,

.................A_B_C_D_E_F_G_H_I_J___Assigned Code__Correct Code
6/07 M 577_2_4_7_3_1_5_6_8_0_9 _____ CHH________FCC
6/07 E 256_7_2_4_3_1_5_6_8_0_9______ FCJ ________BFG

This is odd, because the alpha-numeric correspondence established in the table in BY:BZ is correct.

 

[PAB]

Hi Icewynd,

Wow! PAB that is absolutely above and beyond. Thank You!

You're most welcome!

Everything is working correctly right now, except for the assignment of alpha codes for the winning numbers.

For example,

.................A_B_C_D_E_F_G_H_I_J___Assigned Code__Correct Code
6/07 M 577_2_4_7_3_1_5_6_8_0_9 _____ CHH________FCC
6/07 E 256_7_2_4_3_1_5_6_8_0_9______ FCJ ________BFG

This is odd, because the alpha-numeric correspondence established in the table in BY:BZ is correct.

My data only goes upto 06/06/2013 E 6 2 6.

OK, I think it might be in the timing of calculating one of the LOOKUP tables for the data referenced because one of the tables gets sorted twice, and on each sort the relevant information is extracted and then used.
I will have a look at this later on tonight if I get a chance.
I would think that the automation should save you a bit of time because from what I saw there was a lot of work involved in producing the results?

Is that the only thing that is calculating incorrectly???

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[Icewynd]

My data only goes up to 06/06/2013 E 6 2 6.

Yes, the examples I used were two new draws that I added.

OK, I think it might be in the timing of calculating one of the LOOKUP tables for the data referenced because one of the tables gets sorted twice, and on each sort the relevant information is extracted and then used.
I will have a look at this later on tonight if I get a chance.

No rush, whenever you have a chance.

I would think that the automation should save you a bit of time because from what I saw there was a lot of work involved in producing the results?

The automation will save me a HUGE amount of time.

Whenever I didn't get a chance to update the spreadsheet on a daily basis this sheet would take ages to complete -- that's why there was a big gap in the middle of the page.

Is that the only thing that is calculating incorrectly???

Yes, I checked all the rest of the results and everything else is working correctly. Once again, Thanks.

 

[PAB]

Hi Icewynd,

The automation will save me a HUGE amount of time.
Whenever I didn't get a chance to update the spreadsheet on a daily basis this sheet would take ages to complete -- that's why there was a big gap in the middle of the page.
Yes, I checked all the rest of the results and everything else is working correctly.

I am glad you mentioned about updating the results.
It is VERY IMPORTANT that you ONLY enter one draw at a time and then click CALCULATE otherwise ALL the results produced will be based on the LOOKUP tables last draw results.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[Icewynd]

I am glad you mentioned about updating the results.
It is VERY IMPORTANT that you ONLY enter one draw at a time and then click CALCULATE otherwise ALL the results produced will be based on the LOOKUP tables last draw results.

Yes, I assumed that would be the case. I added each of the two new draws one at a time, clicking "Calculate" after adding each new result.

 

[PAB]

Hi Icewynd,

I ended up re-writing the code for you in the end because the structure really needed streamlining anyway. I think this should be OK now, I did test it with the two new draws you posted and all was well. I also tweaked some of the formulas.

http://www.mediafire.com/download/2dwobepz8de4ie7/MediaFire_-_Pick_3_ON_Frequency_v4.5_NEW.xlsm

Please let me know what you think.
Could you also let me know when you have downloaded the file so I can take it down?

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[Icewynd]

Hi PAB,

Thanks again for doing this.

I tried to download the file this morning and it is asking me for a password, which I assume means you've taken it down? Please let me know.