excel converts formula to exponential notation

Hi all,
I have a column that contains a formula. I have formatted the column general. I type in the formula and copy it down the column. Some of the clls calc fine, others show as exponential notation. There is nothing special that I can see about the cells being used in the calculation. It seems random but I am sure it's not. What's up? I have attached sheet. Check out rows starting 294. Thanks. QCC01-T2-Process-20110914v3.xlsx
COBOLforeverAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
There is a tiny, tiny bug in Excel that sometimes surfaces, where numbers that should be 0 are actually very, very small amounts.

You could use this formula instead and keep the General format, starting in I2 and copied down.

=IF((H2+G2)>590,590-G2,ROUND(H2+G2,10))

That will round the number to the 10th digit, and for this bug should work fine.

And, by the way, if the cell shows a number, then that number can be formatted. It does not matter if the number has been typed into the cell or is the result of a formula.

cheers, teylyn
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

the value in cell I294 is

-0.000000000000113686837721616

The General format will display this in exponential notation. To change that, format the cells as Number instead.

cheers, teylyn
0
 
COBOLforeverAuthor Commented:
But I cannot format as number because the cells are formulas, right?
0
 
COBOLforeverAuthor Commented:
interesting - I did as I was told ... it works. It appears that it only happens when the formula equates to 0.00?
0
 
COBOLforeverAuthor Commented:
excellent - - thanks for the great clarification and explanation.
0
All Courses

From novice to tech pro — start learning today.