Excel is forcing percentages to 15+ decimals long after I removed any and all references to e and LN

It sounds like a bug. Which version of Excel is it, and what number format is shown for the Normal cell style?

Excel 2013. I have the format set to Percentage in some areas. Custom in other areas. If I open a new worksheet the format is set to General. It specifically happens after manual calculation, which I must use. And it seems to be random within any worksheet that had previously used EXP(1) or LN. Its happened before and its so frustrating that I actually have to open some scrap worksheet. And copy and paste EXP(I) into the worksheet I am using as a workaround. But as soon as i use LN(anything) it comes back.

Its as if any cell that was ever at some point in time linked to or calculated from some other cell that had used the LN function, these cells go haywire.

Can you provide a workbook that shows it (via OneDrive/Dropbox etc) and instructions to reproduce it? If I can replicate it, I may be able to get it filed as a bug.

Excelerate2014 said:

Subject: Excel is forcing percentages to 15+ decimals long after I removed any and all references to e and LN
As soon as i change a number and run manual calculation boom 15+ decimals.

Click to expand...

This sounds similar to a problem I mentioned 2 years ago in another forum (click here) [1]. Your description is not precise enough to duplicate the problem and to know for sure. It might even be a simple user error.

Here is a description of the problem I encountered with very different Excel functions. I can duplicate the problem in Excel 2010 SP1, but I never could duplicate the misbehavior in XL2007 SP2 or XL2003 SP3.

1. In A1, enter the formula =FV(0.33%,151-12,0,-53311.6).
2. In A3, enter the formula =FV(0.33%,151-24,0,-110335)-SUM($A$1:A2).
3. Select A1:A3 and format as Number with 2 dp, no commas, and subtype -1234.10 without color.
4. Make column A extra wide. I use twice the default width.
5. Copy A3 and paste into A5. In A5, change -24 to -36 and and -110335 to -171327.47, then press Enter.

The behavior after that varies.

Sometimes, A5 will appear with 11 decimal places (15 significant digits) immediately after step #5.

Sometimes, A3 is affected instead of A5.

Sometimes, simply right-clicking on A5 or A3 and clicking on Format Cells changes that cell's format (or the other cell!) to 11 decimal places.

Sometimes, selecting A5 or A3, pressing F2 (edit) and then Enter changes that cell's format or the other cell.

In June 2012, HansV indicated that he "reported" the problem in the Excel MVP forum.

I'm still interested in seeing an example Excel file (no an image) from Excelerate2014 that duplicates the behavior that Excelerate2014 is experiencing.


-----
[1] http://answers.microsoft.com/en-us/office/forum/office_2010-excel/xl2010-format-oddity-defect-or-bad-option-or/d0d00fb0-1a64-4bb2-9f44-7b97d773a4dd

Excelerate2014 said:

Steps:
a) open new worksheet, set all cells to Number with 4 decimals
b) Set sheet to manual calcs with 1,000 iterations
c) Input the following formulas

Click to expand...

I was unable to duplicate this formatting misbehavior with XL2010.

It might be better if you uploaded an example Excel file that duplicates the problem to a file-sharing website, then posting the "shared" URL here.

(I don't believe this forum provides a way to attach the Excel file to a response directly. Other similar forums do provide that feature. That would be preferable, if allowed and I simply overlooked it.)

Some questions:

1. Any chance you can try this with XL2010, XL2007 or XL2003? I have those, but not XL2013.

2. When you say "open new worksheet", do you really mean a new workbook in a new instance of Excel (as I did)? Or do you have other worksheets with formulas and perhaps some macros?

3. Is it really necessary to set "Enable iterative calculation" with Max Iterations set to 1000 (as I interpreted step #b) in order to duplicate the formatting misbehavior? Your formulas have no circular references.

4. Is it really necessary to set Manual calculation mode in order to duplicate the formatting misbehavior? Presumably you are do that to defer any recalculations until we press F9. But can you duplicate the problem by setting Automatic calculation mode, perhaps pressing ctrl+alt+F9 if necessary?

5. Must the formulas be entered in any particular order to duplicate the formatting misbehavior? I entered all of column D from top to bottom, then E, F and G.

6. G10 could be written more simply: =EXP(G9). Is the form =EXP(1)^G9 necessary to duplicate the formatting misbehavior?

7. For future note, F5 and F6 could be written more simply: =ABS(COS(D5)) and =ABS(COS(D6)). Are the IF() constructs necessary to duplicate the formatting misbehavior?

FYI, I also tried right-clicking on G10 and clicking on Format Cells, then Number tab -- which caused a similar formatting misbehavior for me 2 years ago. That failed to cause any formatting misbehavior in your scenario.

Hello Joe,

I am working on getting you a DropBox link. As to your questions. I don't use XL 2010, 2007 or 2003 so not sure why I would try it there. New Worksheet = New Workbook (No Macros)

Yes the iterative calcs, and especially F9 is the key. On auto calc there is no error, but I never use auto calc, it is a non-starter for me so there is no viable solution for me that relies on auto-calc. Although there is no direct circular ref, the irrational #'s, are in a way circular.

And yes, the other steps while not explicitly necessary, they provide a way to make sure someone is performing this test using appropriately sized numbers and number ranges. Also, it provides a way to have Pi() running through a series of calcs with no decimal explosion (that is a critical point.)