Shulou(Shulou.com)11/24 Report--

Although it seems that operators just fill in numbers and text in the form, Excel software always seems to have its own ideas.

Automatically convert the ID card number into scientific counting, turn a lot of numbers into dates. Not to mention the difficult action of copying and pasting forms. Every wrong result of Excel seems to be full of "treason".

You never know what will happen when you press enter and what has just been filled in the cell.

▲ Excel, thank you for your affectionate cleverness.

In a random search on the Internet, you can see all kinds of Excel Q & An and learning packages. You may have thought that "it's just filling out a form", but after reading the Excel incidents below, you may really want to sign up for a class.

▲ Excel's philosophy of use

Automatic conversion is not as good as not turning Excel always wants to help you, the kind of unhelpful.

Excel has done some pre-programming to make it easier for you, according to Microsoft's website. This gives Excel some "ideas" that can help us automatically correct or predict text. For example, automatically change the number or letter string to the date, or delete the 0 before the number directly. Adhering to the automatic conversion attitude of "the mountain does not turn, the water does not turn, I turn", Excel has helped users a lot.

No one in ▲ can stop the cleverness of Excel. Microsoft

But sometimes, these automatic conversions will only make the user feel that the sky is spinning.

Many geneticists suffer from Excel. A 2016 paper mentioned that in top genomics journals, there are 1P5 papers that have the wrong gene name in the supplementary data. In larger statistics, people collected 11000 papers from 2014 to 2020 and found that almost all of the genes in the papers were misspelled.

All these mistakes are due to Excel's "warm-hearted heart".

Date of change of ▲ gene name | The Verge

In the table, when you type the MARCH1 gene and press enter, the gene name becomes the wrong 1-Mar (March 1). Excel "thoughtfully" automatically recognizes MARCH1 as a date and converts it into a standard date format. The genes SEPT1 and DEC1 were also not immune, thus incarnating into September 1 and December 1.

Scientists discovered the problem as early as 2004 and called on Excel to provide options to turn off automatic filling and correction. But this has not brought about a change, Excel has been trapped by more and more scientists. By 2017, HGNC, a committee specializing in naming genes, had to rename 27 "Excel victim genes", including SEPT4 (now SEPTIN4) and MARCH1 (now MARCHF1).

Fake news site The Allium said sarcastically that the scientific community had decided to surrender to Microsoft. Officially change the names of all genes to dates!

▲ since 2021, researcher Mark Ziemann has decided to publish a monthly ranking of illegal journals to count the problem of gene name errors in well-known journals (ziemann-lab.net/ public / gene_name_errors).

It is enough for Excel to sow this mistake, because it will give the fruit of the mistake to the whole world. British MI5 was also tricked into "social death" by Excel. In 2010, MI5 eavesdropped on hundreds of people because of incorrect tabular data. Excel automatically changed the last three digits of some users' mobile phone numbers to 1, 000, causing 134 ordinary people who had nothing to do with the investigation to be eavesdropped on, but the suspect narrowly escaped. MI5 later had to check the numbers manually before each data processing.

The automatic filling intelligence of ▲ Excel is about ⬆️ (@ meme.xlsx/ Twitte).

Fortunately, in the Microsoft Office preview version (version 2207) released this year, users can finally manually turn off the unneeded automatic data conversion function, and the world-class problem has finally been solved!

If you can't use a function, you'll have to die of all the Excel victims, and JPMorgan Chase should be the one to lose the most. In 2021, they lost $6 billion due to tabular data errors. They investigated the root cause behind it: the functions in Excel were misused.

According to the JPMorgan Chase report, these advanced models and data are manually copied into Excel. If you do not check whether the function is valid after copying the error, it may cause a very serious error in subsequent use.

Kodak has also suffered heavy losses. In 2005, an employee called a few more zeros when using Excel to calculate severance pay, resulting in Kodak's severance loss of $11 million that year. This was even worse for Kodak, which was losing $100 million a quarter.

Whether it is Excel's own stupidity or the user's hand-shaking typing mistakes, the widespread use of spreadsheets in data work also foreshadowing a large number of errors. It's really not that easy to find problematic data in a vast expanse of cells.

To document these serious errors, an organization that specializes in spreadsheet errors has emerged: the European spreadsheet risk interest Organization (EuSpRIG). On the EuSpRIG website, we can see that many companies have lost millions because they are not proficient in using Excel. Research recorded on the website shows that more than 90 per cent of spreadsheets are caused by personal clerical errors, while almost half of spreadsheet models in large enterprises have "major defects".

James Kwak, a law professor, commented: no matter how smart the bank's experts are and no matter how complex the technology is, when the numbers in the Excel table are wrong, the table won't tell you it's wrong. After all, software is software, not to mention Excel with few barriers to use.

Excel use guide, do not learn is not a civil servant, but Excel does not have a threshold, which may be the biggest misunderstanding about it.

Without data review, testing and training, even civil servants of the British government are humiliated because they "can't" use Excel. In 2020, the COVID-19 case tracking system, which was developed in the UK at a huge cost (£12 billion), missed information on 16000 COVID-19-positive cases because of tabular errors. In 2012, a financial model in the Department of Transport's Excel went wrong, resulting in the withdrawal of a £9 billion contract and a loss of £300m. In 2010, a miscalculation of Excel in a paper led to sharp cuts in Britain's public services and benefits.

After this series of events, the United Kingdom may really have to train its civil servants to learn how to use Excel.

There are people who don't know how to use forms, and there are people who play with Excel. In the hands of some gods, drawing a pixel picture on the Excel table can only be regarded as a basic operation. Drawing a Cowherd and Weaver Girl with Excel data can be said to be the most dazzling local romance. Others have designed "2048" and "Legend of Zelda" that can be played in tables based on functions and plug-ins in Excel.

▲ and other developers have ported Zelda to Excel, named Excelda (also Zelda) (GamesExcel / Youtube).

Even more powerful is the "Excel Championship". Financiers can solve the Excel problem in the Financial Modeling World Cup (FMWC). However, in this year's competition, some finalists actually lost the championship because of the misplaced or copied format of the formula. It seems that even table experts are not immune from the fate of being tricked by Excel.

There is still a long way to go to learn Excel. But thanks to Excel, publishers can publish more books: give up Excel quickly in 30 days.

Referenc

[1] 'Scientists rename human genes to stop Microsoft Excel from misreading them as dates', https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates'

[2] Excel autocorrect errors still plague genetic research, raising concerns over scientific rigour', https://theconversation.com/excel-autocorrect-errors-still-plague-genetic-research-raising-concerns-over-scientific-rigour-166554'

[3] 'Autocorrect errors in Excel still creating genomics headache'

[4] Ziemann, M., Eren, Y., & El-Osta, A. (2016). Gene name errors are widespread in the scientific literature. Genome biology, 17 (1), 1-3.

[5] Zeeberg, Barry R., et al. "Mistaken identifiers: gene name errors can be introduced inadvertently when using Excel in bioinformatics." BMC bioinformatics 5.1 (2004): 1-6.

[6] 'Escape gene name-mangling with' Escape Excel', https://blogs.nature.com/naturejobs/2017/02/27/escape-gene-name-mangling-with-escape-excel/

[7] 'MI5 makes 1061 bugging errors', https://www.theguardian.com/government-computing-network/2011/jul/01/mi5-data-collection-errors

[8] 'Report Details Missteps in Trading at JPMorgan', https://archive.nytimes.com/dealbook.nytimes.com/2013/01/16/report-details-missteps-in-trading-at-jpmorgan/

[9] 'The Importance of Excel', https://baselinescenario.com/2013/02/09/the-importance-of-excel/

[10] http://www.eusprig.org/horror-stories.htm

[11] 'Kodak restates, adds $9 million to loss', https://www.marketwatch.com/story/kodak-restates-earnings-adds-9-million-to-latest-loss

[12] 'Excel errors: the UK government has an embarrassingly long history of spreadsheet horror stories' https://theconversation.com/excel-errors-the-uk-government-has-an-embarrassingly-long-history-of-spreadsheet-horror-stories-147606

[13] 'Excel esports on ESPN show world the pain of format errors', https://arstechnica.com/gaming/2022/08/excel-esports-on-espn-show-world-the-pain-of-format-errors/

This article comes from the official account of Wechat: fruit Shell (ID:Guokr42), author: Chen Mou Editor: Owl, window knocking Rain

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.