Decimal places

[JCA1970]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: intel

I have a question. A formula I'm using returns a value of 19.478. However I want it displayed with no decimal places. If i remember my maths correctly as you reduce the number of decimal places the value changes to 19.48, then 19.5 and it should then be rounded up to 20. however Excel gives it a value of 19. This is wrong is it not?

If i use the command ROUNDUP and ask it to display 19.478 to no decimal places it displays 20.

Anyone have any ideas?

 

[JE McGimpsey]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: intel

I have a question. A formula I'm using returns a value of 19.478. However I
want it displayed with no decimal places. If i remember my maths correctly as
you reduce the number of decimal places the value changes to 19.48, then 19.5
and it should then be rounded up to 20. however Excel gives it a value of 19.
This is wrong is it not?

If i use the command ROUNDUP and ask it to display 19.478 to no decimal
places it displays 20.

Display (and formula) rounding depends on the actual value stored in the
cell, which doesn't change with number format.

So since 19.478 is less than 19.5, the displayed value will be 19.

If you want any value greater than 19 to display as 20, use ROUNDUP or
CEILING, e.g.:

=CEILING(19.00001,1)

 

[PhilD]

I have a question. A formula I'm using returns a value of 19.478. HoweverI want it displayed with no decimal places. If i remember my maths correctly as you reduce the number of decimal places the value changes to 19.48, then 19.5 and it should then be rounded up to 20. however Excel gives it a value of 19. This is wrong is it not?

When rounding numbers, remember always to take your result from the
*original* number, not from successive roundings.

With each rounding, you are effectively introducing a small error. If
you round, then round that, and then round *that*, you are introducing
more and more error. By referring always to the original number, you
only introduce one error.

PhilD

 

[JCA1970]

Still it is technically incorrect - if you have a number of 19.324 for example that would be 19 to 0 decimal places. if you have 19.456 then that is 20 to 0 decimal places not 19.

and roundup creates the problem the other way and just automatically rounds up the number to 20 even when it should be displayed as 19.

This means that if you are working on a column of figures to 0 decimal places then the totals could be wrong using either method.

 

[Bob Greenblatt]

Still it is technically incorrect - if you have a number of 19.324 for example
that would be 19 to 0 decimal places. if you have 19.456 then that is 20 to 0
decimal places not 19.

and roundup creates the problem the other way and just automatically rounds up
the number to 20 even when it should be displayed as 19.

This means that if you are working on a column of figures to 0 decimal places
then the totals could be wrong using either method.

Yes indeed, the totals COULD be wrong in either method. You need to remember
that Excel retains ALL values to 16 significant digits regardless of the way
the number is displayed. And, I beg to differ, 19.456 rounded to 0 decimal
places is 19 not 20.

 

[PhilD]

the number is displayed. And, I beg to differ, 19.456 rounded to 0 decimal
places is 19 not 20.

Precisely. 19.456 is closer to 19 than it is to 20. It's fundamental
to basic maths!

PhilD

 

[CyberTaz]

Sorry, but you're wrong on this...

Still it is technically incorrect - if you have a number of 19.324 for example
that would be 19 to 0 decimal places. if you have 19.456 then that is 20 to 0
decimal places not 19.

When you change the *formatting* of the cell to display fewer decimal places
you're changing the *display* of the value only, not changing the value
itself. It's still stored as 19.456 so it rounds to 19 because it's still
literally less than 19.5 - if you *enter* an actual value of 19.5 into a
cell rounded to 0 decimals it will display as 20.

Regards |:>)
Bob Jones
[MVP] Office:Mac