Number Formatting Annoyance

[hrlngrv]

Not the first time Excel has done entirely screwy things with number formatting.

Excel 2010.

I have a workbook with only the Normal style (I deleted all the others), and its number format is #,##0. I have cell G26 with number format 0.00% with cell F26 to its immediate left formatted the same, and all other surrounding cells with default Normal style. I start off with the formula =F26/E26 (a ratio of percentages), and it appears in 0.00% format. Then I change the formula to =F26/E26-1, and Excel changes the number format to 0.000000000000000%. Excel is the most annoying program I use.

 

[Joe4]

Excel is the most annoying program I use.

Then you must not use Microsoft Word very much which, I am convinced, was written by the devil himself!

Have you formatted the cells to only show the number of decimal places you want? That may satisfy your needs.

There is a known "floating point arithmetic" error in how Excel stores the data that may be what you are experiencing (see here: http://support.microsoft.com/kb/78113). The easiest away around this is to use the ROUND formula in your formulas to limit the answer to exactly how many decimal places you want.

 

[Firefly2012]

Excel is like an addictive drug though: even though you may think it does you harm, you can't help yourself from coming back for more.

Personally I thought PowerPoint was the epitome of Hell in software - until I tried Visio

 

[xenou]

Not sure what you mean by the number format in your normal template, but the cell in question must have that number format applied. Since you're value shows a percent sign, I would assume this is not a number format (?) but a percent format. Personally I have never seen this behavior you speak of, when dealing with percents. Maybe reset the template to defaults and try again?