Excel: Format Decimal Places Without Rounding or Changing Cell Value

[postman]

Hi there.

I'm trying to format numbers to display a limited number of decimal places without rounding the displayed result or altering the actual value of the cell.

For example, the value in the cell is: 78.636153846153 9
I need it to be displayed as: 78.63, but still keep the underlying value for the purposes of calculation.

I've searched everywhere and cannot find a solution. Changing the decimal places in the number format will round the displayed result and the ROUNDDOWN or TRUNC functions alter the underlying value.

Is this possible using number formatting or formulas? In the worksheet I'm working on, a VBA solution is not feasible.

Thanks in advance for any help you can provide.

[Stewart Ross]

What you describe is just the normal behaviour of Excel with number formatting applied to cells - Excel always rounds the displayed value up or down in accordance with normal convention. Of course it uses its full precision in any calculation regardless of formatting.

If you want to truncate a value downward but retain the full precision for calculations you will need to keep two copies, using a hidden column for the original, say. You'd refer to the hidden column with the full-precision values for use in calculations, and use the other version truncated down for display. You can do this using a formula such as =Int(B1*100)/100 to do the truncation to two decimal places for display. You would need to ensure that you do not refer to the truncated values in any subsequent calculations though or you will lose precision.

I do not question your need to display the value in the way you state, as I do not know your application at all. However, the reason for the convention of rounding up if a value is over the half-way point of the next nearest decimal becomes clearer the higher the value gets. When it comes to values such as 78.639999, say, they are for most practical purposes indistinguishab le from the value 78.64, NOT 78.63. Ignoring this leads to errors between what is displayed and what is subsequently calculated.

-Stewart

[postman]

That's basically what I ended up doing. I did the calculations in hidden columns and just referred to those cells in the displayed columns using the TRUNC function.

I was just hoping there was a simpler, more elegant solution.

Thanks for your reply. You've saved me from researching further for no reason!