Excel Function doesn't work properly

  pj123 14:52 03 Nov 07

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 07

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 07

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


  pj123 16:56 03 Nov 07

johnnyrocker, thanks. I will give that a try.

  VoG II 17:21 03 Nov 07


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 07

VoG™. Brilliant.

Thank you.



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

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation technologies coming to Siggraph 2017

iPad Pro 12.9 vs Surface Pro 5