469,645 Members | 1,306 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,645 developers. It's quick & easy.

Excel: Format Decimal Places Without Rounding or Changing Cell Value

63
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.6361538461539
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.
Nov 16 '11 #1

✓ answered by 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 indistinguishable from the value 78.64, NOT 78.63. Ignoring this leads to errors between what is displayed and what is subsequently calculated.

-Stewart

2 5710
Stewart Ross
2,545 Expert Mod 2GB
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 indistinguishable from the value 78.64, NOT 78.63. Ignoring this leads to errors between what is displayed and what is subsequently calculated.

-Stewart
Nov 16 '11 #2
postman
63
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!
Nov 17 '11 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Dean G | last post: by
1 post views Thread by Next | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.