Excel Function doesn't work properly

  pj123 14:52 03 Nov 2007

I run a Lottery syndicate and have all the Saturday draw history from the start, (Nov 1994). We don’t do the Wednesday draw.

I have a spreadsheet which checks the numbers weekly on one worksheet and automatically updates another worksheet which shows the frequency of the numbers drawn. Works fine.

I have now today tried to run a frequency check on the EuroMillions draw. I downloaded the Draw History from Camelot and imported it into Excel.

When I apply the Function =COUNTIF($b$5:$f$210,m1) it returns drawn numbers 1 to 4 as 0 (zero). I have physically counted each of these numbers and 1 has been drawn 28 times, 2 = 14, 3 = 27 and 4 = 19.

The range b5:f210 contain the 5 main drawn numbers. Column M contains the ball numbers 1 to 50.

All numbers from 5 to 50 have a frequency number.

This is the same Function I am using in the Lottery spreadsheet, except of course, the range is bigger and there are only 49 numbers to be drawn.

Also, column A contains the Drawn Date which is in the format yyyy-mm-dd and I have tried to change it to dd-mm-yyyy but it doesn’t like it.

Any help appreciated, thanks.

  pj123 15:35 03 Nov 2007

Sorry Guys. False alarm on the Function.

I looked hard and long at it again just to make sure I have typed it all in correctly. I made a mistake. It should have been m5 at the end and not m1. That bit has now been rectified but I would still like to know how to re-format the date.


  johnnyrocker 15:45 03 Nov 2007

i believe a solution to a similar post was to widen columns?


  pj123 16:56 03 Nov 2007

johnnyrocker, thanks. I will give that a try.

  VoG II 17:21 03 Nov 2007


Select the dates, Data > Text to Columns. Click Next twice. On the third screen tick Date and select YMD. Then click Finish.

That should convert what are probably 'text dates' into real dates that should then display as desired.

  pj123 17:30 03 Nov 2007

VoG™. Brilliant.

Thank you.



This thread is now locked and can not be replied to.

Elsewhere on IDG sites

WPA2 hack: How secure is your Wi-Fi?

HP’s new Surface Pro rival is designed specifically for Adobe-using designers and artists

Best kids apps for iPhone & iPad

Que faire si son iPhone ou iPad est tombé dans de l'eau ?