Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

excel converts formula to exponential notation

Posted on 2011-09-14
5
Medium Priority
?
406 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:COBOLforever
  • 3
  • 2
5 Comments
 
LVL 50
ID: 36540149
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
 

Author Comment

by:COBOLforever
ID: 36540164
But I cannot format as number because the cells are formulas, right?
0
 

Author Comment

by:COBOLforever
ID: 36540169
interesting - I did as I was told ... it works. It appears that it only happens when the formula equates to 0.00?
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 36540180
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
 

Author Comment

by:COBOLforever
ID: 36540221
excellent - - thanks for the great clarification and explanation.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question