Excel Number Frequency Chart

In terms of banging one's head against a wall; I can't succeed with formulas for constructing a number frequency chart in Excel. I'm working with the Canadian 649 only. Any advice much appreciated.

Best regards,



This is the frequency function for Excel, replace my
$a$1:$f$2000 with your column and row info

etc etc to



Frequency calculations

An easier way may be:

If your data (2010 draws) is in cells A2:H2011, with draw number in column A, winning numbers in columns B to G and bonus number in column H, place the values 1 to 49 in cells J2 to J50. You can use cells in row 1 to put headings.

Select cells K2:K50 and in K2 enter the formula =FREQUENCY(B2:G2501,J2:J50). DO NOT press ENTER but press CTRL-SHIFT-ENTER and you get the number of times each number appeared as a winning number. In cells L2:L50, repeat the same process but replace B2:G2501 by B2:H2501 and you will get the number of times each number came out including as a bonus number.

Using 2501 as the lower limit for the data will allow you to add winning combinations and the data will be automatically updated.

For an easier understanding of the data, then use the range J2:L50 to create a Bar- chart.