Serious bug with certain decimal numbers: Countifs and Roundup failed

Viz 

After a lot of rummaging around on Google, I came to this result.

The short answer is, you can't edit your numbers the way you want after you've entered the numeric value and hit enter. The moment you do this, the number is truncated to 15 digits and shown as exponential.

This is not a bug, it is a function.

Everything you do after that uses the truncated value, so no formatting trick is helpful. However, you can use the “Text to columns“ option to convert the exponential notation to text (click on the column heading, click “Text to data“ then click Delimited, Next uncheck all delimiting boxes, select “Text in Column data format "And then from" Finish ") to convert the values ​​into text and immediately display them as a 15-digit number. But it will only be 15 digits so if you had a longer number the pause is lost.

To have the number in the worksheet exactly as you entered it, you need to save it as text. So you have to prepend an apostrophe or format cells as text before you can enter / copy values. If it "sometimes doesn't work" then you are either doing something wrong or you have some kind of autocorrect enabled (if you use an apostrophe and a large number> 15 digits, Excel will treat it as an incorrect value and mark the cell with a warning hence it is this remark is neither personal nor critical.

Another way to do this in bulk is to import values ​​from a text file. Dare I say it's the only way out for most situations. Be sure to import the file and not just open it, as Excel treats the csv type like the native format and of course cuts large numbers to 15 digits.

The bottom line is, if you type a large numeric value into an Excel cell formatted as text, it will continue to be displayed in scientific notation as long as the cell is not wide enough. It's annoying, but a side effect of some of Excel's internal algorithms. If you just make the cell wider you will see the full value every time.

A pleasant task for most simple users like me, when you have nothing to do or don't want to do anything.

With my little knowledge, I cannot 100% verify whether all of this is the case.

Who am I already! ... I know that I don't know anything.

Nikolino