2

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?

3
  • 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. Aug 17, 2011 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, 2011 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, 2011 at 14:14

1 Answer 1

3

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.

2
  • 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, 2011 at 23:27
  • correction: precision should not be auto update of the cell format.
    – mservidio
    Aug 16, 2011 at 23:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .