Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I've run into an issue in Excel 2010 where precision in percentage type cells is changing. To reproduce:

  • Create two cells in A1 and B1, both with Percentage format (2 decimals) and numbers with decimals
  • In a new cell, go to cell format, and change it to Percentage (2 decimals).
  • Enter this formula in the cell:

    =A1/B1
    

The cell format will now change to Percentage with a higher precision, which seems to fit the amount of decimals required for the calculation.

Why does the precision change?

share|improve this question
    
I know Excel will auto-format cells if it can logically work out the new format. I tried out your situation and had the same trouble. Personally, I think that's ridiculous! I think all you can do is: 1: write your formulas, 2: format your formula cells, 3: write the source values. –  Hand-E-Food Aug 17 '11 at 2:50
    
@mservidio I see your point now, you're right that is bad behavior from excel it shouldn't change your display format once you've specified it. Fortunately if you do the format after you've done the formula it stays there and doesn't change if the value varies in precision. –  Col Aug 17 '11 at 8:00
    
I think it's a nice feature to have if the user wants to enable it. However, the behavior by default is strange. Once a user defines a cell format, they shouldn't automatically update the precision. I have a feeling that this may actually be a bug, and not a feature. –  mservidio Aug 17 '11 at 14:14

1 Answer 1

The format of the cell does not affect the precision of the numbers stored in it, just how they are displayed. So if you enter 1.7985 and format it to 2 decimals the number is still know to 4 decimals and any calculations use full precision. If you want to reduce the precision you'll need to use the round function.

share|improve this answer
    
Agreed, the format doesn't affected the precision of the underlying calculation. However, I have defined a precision for the display format (cell format), the display format precision should not change. The underlying values are the original and full precision numbers, however what is displayed should only be my configured number with precision of 2 which I already defined. Precision should not be auto updated. –  mservidio Aug 16 '11 at 23:27
    
correction: precision should not be auto update of the cell format. –  mservidio Aug 16 '11 at 23:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.