When I first heard that cases were missing I thought “can’t be an Excel problem, the rows run out at 1,048,576 which is bigger than even total cases”.
I never thought they’d have a case per COLUMN. Unbelievable. And yes: Excel columns end at 16,384 aka “XFD”. https://twitter.com/MaxCRoser/status/1313046638915706880
I never thought they’d have a case per COLUMN. Unbelievable. And yes: Excel columns end at 16,384 aka “XFD”. https://twitter.com/MaxCRoser/status/1313046638915706880
In Excel on a Mac hit the command-⌘ key with 'right arrow' and you'll jump to the last column (I think Windows is Ctrl+Right). Columns count up alphabetically and you can check that "XFD" is 'Excel base-26' for 16,384 which is 2^14. Excel stores column numbers as 14-bit binary.
All of that said: I’ve not been able to verify that it was a column problem. Just that it was an issue with the limits of Excel. It’s a national disgrace either way, but if anyone has more definitive info please do let me know. matt@standupmaths.com
The BBC are reporting that the data was in Excel but was indeed in rows (not columns, phew).
HOWEVER, Public Health England used .xls format and not .xlsx which means they were limited to the pre-Excel-2007 limit of 65,536 (aka 2^16). Which is still breathtaking incompetence.
HOWEVER, Public Health England used .xls format and not .xlsx which means they were limited to the pre-Excel-2007 limit of 65,536 (aka 2^16). Which is still breathtaking incompetence.
This is not how to run a database.
“commercial firms paid to carry out swab tests for the virus … filed their results in the form of text-based lists, without issue. PHE had set up an automatic process to pull this data together into Excel templates” https://www.bbc.co.uk/news/uk-54422505
“commercial firms paid to carry out swab tests for the virus … filed their results in the form of text-based lists, without issue. PHE had set up an automatic process to pull this data together into Excel templates” https://www.bbc.co.uk/news/uk-54422505