/docs/community?hl=en
/docs/community?hl=en
7/5/12
Original Poster
kutor

Scientific notation still has a problem

This problem has been reported before.  It is like this, when you input 1e-23 then press Enter, the cell becomes zero.  Even if the cell format has been changed into "scientific notation", it still appears as zero.  I guess this is a bug.
Community content may not be verified or up-to-date. Learn more.
All Replies (3)
Bas Braams
7/5/12
Bas Braams
<<when you input 1e-23 then press Enter, the cell becomes zero>> I find it interesting to explore this a bit further.

Start with a new spreadsheet, everything at default, no customized formatting.

Enter 5e-16 or 5.0e-16 into the A1 cell and it displays as 0; moreover it really means 0 as can be seen by evaluating =1e16*a1 in another cell.
Enter 5.01e-16 into the A1 cell and it displays as 0.000000000000001 (or 1e-15), and by evaluating =1e16*a1 we see that the stored value really is 1e-15.
Enter 1.2345e-14 into the A1 cell and it displays as 0.000000000000012, and by evaluating 1e16*a1 we see that the stored value is indeed 1.2e-14.

It leads to the expectation that numbers entered as plain numbers without further formatting are rounded to a multiple of 1e-15 and are stored that way.

Enter =5e-16 into the A1 cell and it displays as 0, but evaluate =1e16*a1 and it shows that the stored value is in fact 5e-16.
Enter =1e-100 into the A1 cell, evaluate =1e200*a1 in the A2 cell and it shows that the stored value is indeed 1e-100.
Enter =2.48e-324 into the A1 cell, evaluate =1e200*a1 in the A2 cell and 1e200 *a2 in the A3 cell and it shows that the stored value in A1 is 4.94e-324.

A bit more of this provides conviction that a positive number entered using =<number> can have a positive result value as small as 4.94e-324. This is indeed the smallest positive unnormalized floating number in the IEEE-754 64-bit standard.

We explore the other end of the positive number range.

Enter 1.797e293 into the A1 cell and it displays as 1.80e293.
Enter 1.798e293 into the A1 cell and it displays as infinity; evaluate =1e-200*a1 and it also gives infinity.
Enter =1.798e293 into the A1 cell and it also displays as infinity, but now =1e-200*a1 shows that the stored value is actually 1.798e293.
Enter =1.797e308 into the A1 cell, evaluate =1e-200*a1, and it is seen that the entered value is stored.
Enter =1.798e308 into the A1 cell, evaluate =1e-200*a1, and it is seen that the stored value is infinity.

It makes reasonable sense given that the overflow limit for IEEE-754 64-bit floating point numbers is indeed 1.798e308. However, it does look like a trap that one needs to enter numbers in the form =<number> and not just <number> in order to get properly precise 64-bit values.

7/5/12
Original Poster
kutor
Wow!  Your  exploration is great!  Thanks a lot!
Bas Braams
7/9/12
Bas Braams
As said in the OP, <<This problem has been reported before>>. I just came across an early one by ahab, from 2006-10-25 when he wrote: "It looks like it the smallest value >0 accepted as input is 1.0E-15  This limit is also in computations the smallest value >0 that can be used. The positive maximum that can be input is about 1e+293 (anything higher is displayed as 8 [infinity]). These limits seem strange and not conforming to a IEEE 754 32-bits or 64-bits floating point representation.  (in fact these limits lie between those of 32-bit and 64-bit floating point). My guess is the problems we see are a result of a failing input parser for numbers and a limitation of the output routines for the same. The Google D&S Team would do well to re-evaluate these parts of Spreadsheets, as there is no apparent need for the limitations encountered." This was on an earlier incarnation of the Google product forums; one can find it on the web.

In the present Google Docs forum archives I came across a posting by ahab from early 2009 in the conversation [1] in which he describes the behavior as a bug and another one from 2010 [2] in which he provides a spreadsheet to show the behavior in detail. In the psoting under [2] ahab writes: "the Google Docs team never bothered to compile the information for us so it all gathered using trial and error and educated guessing..." I don't believe that anything has improved in this regard. If one goes to the Google Spreadsheets help pages starting at [3] and one tries to find out anything about input formats then it leads nowhere. It is an odd situation.

[1] (2009-01-13) Scientific Notation

[2] Range of values in scientific notation

[3] Spreadsheets - Overview
Were these replies helpful?
How can we improve them?
 
This question is locked and replying has been disabled. Still have questions? Ask the Help Community.

Badges

Some community members might have badges that indicate their identity or level of participation in a community.

 
Google Employee — Google product team members and community managers
 
Community Specialist — Google partners who help ensure the quality of community content
 
Platinum Product Expert — Community members with advanced product knowledge who help other Google users and Product Experts
 
Gold Product Expert — Community members with in-depth product knowledge who help other Google users by answering questions
 
Silver Product Expert — Community members with intermediate product knowledge who help other Google users by answering questions
 
Product Expert Alumni — Former Product Experts who are no longer members of the program
Community content may not be verified or up-to-date. Learn more.

Levels

Member levels indicate a user's level of participation in a forum. The greater the participation, the higher the level. Everyone starts at level 1 and can rise to level 10. These activities can increase your level in a forum:

  • Post an answer.
  • Having your answer selected as the best answer.
  • Having your post rated as helpful.
  • Vote up a post.
  • Correctly mark a topic or post as abuse.

Having a post marked and removed as abuse will slow a user's advance in levels.

View profile in forum?

To view this member's profile, you need to leave the current Help page.

Report abuse in forum?

This comment originated in the Google Product Forum. To report abuse, you need to leave the current Help page.

Reply in forum?

This comment originated in the Google Product Forum. To reply, you need to leave the current Help page.