Before we get to the macro, you need to know about other errors. On the Archive sheet you had the following formula in cell AH2 =COUNTIF($C6:$C3000,AG2)
You copied this down the column but the test range (your main balls table) was moving down also with it. In cell AH3 this became =COUNTIF($C7:$C3001,AG3). By the time you got to AH8 your formula was =COUNTIF($C12::$C3006,AG8). The top part of your results table are not now included.
in AH2 it should be =COUNTIF($C$6:$C3000,AG2) to anchor the top row to row 6. so when copied down it is still anchored to $C$6. I have corrected this for you. I let the bottom boundary drift as theres nothing below the range to corrupt your results.
You did not have the last draw number (765 ) in. This meant that cell A1 (=max of col C) is not recording how many draws. You need this, see later.
If you know how many draws you can create a checksum.Checksums make sure that when you use countif or frequency formulas on tables, that you havent made a mistake.
Adding up all your countif results (Col AH) should equal how many draws. If it does equal how many draws (cell A1) you havent made a mistake with your countifs. But you MUST have the draw number up to date.
I don't like your frequency/probability table, youve made a major error there. Your formula in cell V3 is =COUNTIF($C:$G,V2) Using whole column references.
This is very lazy!! and causes a host of problems. You are saying .. count EVERYTHING between C1 and H1048576
This means you've counted the latest result TWICE! Youve copied this formula to all the other ball numbers making the same mistake for them all. To avoid errors BE SPECIFIC where your table begins. it begins in cell $C$6 the $ signs are important because your other balls are on different rows and columns but you must anchor the top left of your table to $C$6, so your formula in cell V3 should be =COUNTIF($C$6:$G10000,V2). note Ive set an upper bound to how many rows it checks, 10,000 . Why waste time and energy checking the other 990,000 rows? Had you set up a checksum for your frequency counts you'd have seen the error. The sum of all main ball frequencies should equal how many balls have ever been drawn = (how many draws ) x 5. if it doesn't you've made an error! I have not corrected your frequency formulas , ill leave that to you, but Ive set up a checksum so you'll know if you get it
right!
Macro2 you comitted a cardinal sin. You copied a "live" table with formulas linking to other sheets, pasted it elsewhere with the live fromula links still live then sorted it. Obviously after the sort, the formulas in the sorted table updated and put the original values back in corresponding to the whatever the formulas said they should do, but whilst in different positions. Or to be precise,that is it would have done, had you included the last column (skips) in the sort which link to a different page, but you didnt. You left that part out of the sort so it was showing the skips of balls 1,2,3,4 in order despite the fact that the ball numbers were now jumbled up after a sort. I suggest when you copy a table for sorting, you PASTE VALUES but make sure you include ALL the columns you want to move with the sort. I have corrected this. https://www.mediafire.com/?khdsk50l230ms4c
As for looking professional, its coming along nicely, but you must focus on accuracy before looks. Use checksums, always click in the formula bar after youve written a formula, to see which cells Excel outlines, showing you what cells or ranges your formula acts on. The rest is just time and confidence, it takes years not months. Good luck!
