Shulou(Shulou.com)06/03 Report--

This article mainly introduces "what are the interesting bug in Excel". In daily operation, I believe that many people have doubts about the interesting bug in Excel. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the questions of "what are the interesting bug in Excel?" Next, please follow the editor to study!

It has been 36 years since Excel 1.0 entered the market as early as 1985. Although there is a similar software Lotus 1-2-3 on the market at the time of launch, Excel still relies on its overall transcendence of competitive products in function, coupled with the direct binding with Windows environment, directly pushes Excel to the king position of spreadsheet office software.

But in today's article, we're not talking about the familiar features of Excel. Let's take a look at some puzzling calculations even if they are as powerful as Excel.

February 29, 1900

Primary school students all know that the Gregorian calendar we use now stipulates that a year is a multiple of 4 and not a multiple of 100 is a leap year; a year is a multiple of 100 and must be a multiple of 400 to be a leap year.

So 1900 does not meet the above conditions is not a leap year, but in Excel there is February 29th, 1900. By dragging and filling, you can see that February 28th is followed by 29th. Is it because Excel is still using the four-year Julian calendar? After testing, it doesn't exist in Excel on February 29th, 2100, so why is only 1900 wrong?

Excel has officially replied that this is a bug of Lotus 1-2-3, the predecessor of Excel, but fixing the problem will lead to an one-day error in the historical file. For compatibility with the old file, and the impact of this Bug is very small, Excel did not fix the bug, which has been retained since February 29th, 1900.

Is 38-12-31 1938 or 2038?

At the beginning of a century of reincarnation in the 21st century, we are used to talking about the two years of the year, such as 1987 for short, and 2002 for short. The input of a 2-digit year is also supported in Excel, for example, the input of 87-12-31 is automatically recognized as 1987 Universe 1231, and the input of 20-12-31 is automatically recognized as 2020-12-31. However, on 30-12-31, it changed to 1930. what rules does Excel follow to judge the year?

Here Excel has a 2029 rule, remember that the numbers in 00-29 will be automatically recognized as the 21st century, and the rest is the 20th century. If you want to enter a year after 29 years, you need to enter four digits. Of course, you can also change the 2029 node by changing the operating system settings. For more information, please refer to the official description of Excel: https://docs.microsoft.com/zh-cn/office/troubleshoot/excel/two-digit-year-numbers.

The two numbers are not equal.

Enter = 1.2-1.1-0.1 in Excel, and the calculated result is FALSE after entering enter. This means that the result of 1.2 minus 1.1 is not 0.1. Is Excel miscalculated? But input = 1.2-1.1 does show 0.1.

This is indeed an Excel miscalculation, but this is not an Excel-specific error, but a floating-point calculation error caused by the binary nature of the computer. In short, the computer calculation process uses binary, for 1.2-1.1 results are binary cyclic decimals, decimal truncation will be carried out during storage, resulting in errors.

If you set the display number of 20 digits in Excel, you will find that the actual calculated value is 0.099999999999990000.

Usually, Excel can correct the results with small errors by itself, but Excel does not deal with the results from 1.2 to 1.1.

Therefore, it is a good habit to use ROUND when making numerical comparisons, and you can set parameters according to the decimal range involved in the business scenario. For example: = ROUND (1.2-1.1,10) = 0.1.

Because of the calculation error, if you want to get a rounded 2 decimal, you need to ROUND twice, first correct the error, and then round to retain 2 decimal places. Doing ROUND twice for all operations is too cumbersome, so you can use another setting provided by Excel to use "display accuracy", so that Excel will retain the precision according to the number of decimal places set by the cell when storing the calculation.

However, it should be noted that the subsequent calculation needs the real calculation result or the formatted result, and the incorrect use of this function may lead to an increasing error.

Numbers are not counted.

For example, in the screenshot, the SUM result in the cell is 0, and there is a green triangle prompt "number stored in text" in the upper left corner of the cell. This hint is why the SUM result is zero, and these numbers are actually stored as text, which does not participate in the operation of numbers.

There is a concept here is the storage format, when entering the number 0PowerExcel in the Excel cell will default to the numeric format as storage, that is, the previous binary, but if you first set this cell type to text, then 0 as text. In Excel, numbers and dates are displayed on the right by default, and text on the left.

This is also the problem that we often have in entering the phone number and ID card number. After entering the ID number, it is displayed as a scientific counting method. Here, we need to set the cell as text before entering.

At this point, the study of "what are the interesting bug in Excel" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.