How do I get Excel 2003 to automatically round up the result of a

[dartanion]

I have a cell, which is the result of one cell minus another, but when the
result is something point 5, excel rounds this down, and I want it to round
the result up. Any ideas?

 

[Stephen]

I have a cell, which is the result of one cell minus another, but when the
result is something point 5, excel rounds this down, and I want it to
round
the result up. Any ideas?

Excel will automatically round "something point 5" up. I suspect the result
of your calculation is actually "something point 499999...", which correctly
is rounded down.

For more help you need to be specific about the the actual data in your
cells (as seen in the formula bar, NOT what is displayed), what your formula
is, how the cell is formatted, etc.

 

[David Biddulph]

I don't know whether you are looking at the rounding when you are displaying
a format with zero decimal places, or whether you are using the ROUND
function, but in either case 0.5 *will* round up to 1.

If, of course, your number is 0.46, for example, and it had originally been
displayed to 1 decimal place it would *look like* 0.5, but it would round
down.
I would therefore recommend that you display your original number to more
decimal places to check exactly what it is.
There is a slight possibility that you've fallen victim to the fact that
most decimal numbers cannnot be represented exactly in fixed point binary.
0.5 can, but 0.1 can't. [You can't represent 1/10 exactly in fixed point
binary, just as you can't represent 1/3 exactly in fixed point decimal.]
You may therefore have small rounding errors on your original numbers and
thus ended up after your subtraction with something which isn't exactly 0.5
although you would expect that it would be. Again if you extend the number
of decimal places to see all 15 significant figures you should see if this
is the case.

 

[dartanion]

Thanks David, dut it isn't working as you suggest. Yes the display is to zero
decimal places. I have expanded the cells to 15 decimal places, and they are
as follows A is 79 exactly, B is 16.500000000000000, and this displays as 17,
which is as you suggest, but the next cell, C, has the formula =A-B, and the
answer it displays is 63. The answer I expect is 62, as when this is printed,
everyone tells me my math is wrong!

I don't know whether you are looking at the rounding when you are displaying
a format with zero decimal places, or whether you are using the ROUND
function, but in either case 0.5 *will* round up to 1.

If, of course, your number is 0.46, for example, and it had originally been
displayed to 1 decimal place it would *look like* 0.5, but it would round
down.
I would therefore recommend that you display your original number to more
decimal places to check exactly what it is.
There is a slight possibility that you've fallen victim to the fact that
most decimal numbers cannnot be represented exactly in fixed point binary.
0.5 can, but 0.1 can't. [You can't represent 1/10 exactly in fixed point
binary, just as you can't represent 1/3 exactly in fixed point decimal.]
You may therefore have small rounding errors on your original numbers and
thus ended up after your subtraction with something which isn't exactly 0.5
although you would expect that it would be. Again if you extend the number
of decimal places to see all 15 significant figures you should see if this
is the case.
--
David Biddulph

I have a cell, which is the result of one cell minus another, but when the
result is something point 5, excel rounds this down, and I want it to
round
the result up. Any ideas?

 

[David Biddulph]

79 - 16.5 is 62.5. This rounds up to 63, so you are getting the correct
answer.
You need to remember that formatting the 16.5 to *display* to zero decimal
places will change only the display, not the stored number.

If you want to round the 16.5 to 17 *before* you do the subtraction, then
you either need to use the ROUND function such as =A1-ROUND(B1,0), or you
can use Tools/ Options/ Calculation: Precision as Displayed, but I wouldn't
recommend the latter as it may well give you unexpected results elsewhere if
you don't think carefully about what it is doing.
--
David Biddulph

Thanks David, dut it isn't working as you suggest. Yes the display is to
zero
decimal places. I have expanded the cells to 15 decimal places, and they
are
as follows A is 79 exactly, B is 16.500000000000000, and this displays as
17,
which is as you suggest, but the next cell, C, has the formula =A-B, and
the
answer it displays is 63. The answer I expect is 62, as when this is
printed,
everyone tells me my math is wrong!

I don't know whether you are looking at the rounding when you are
displaying
a format with zero decimal places, or whether you are using the ROUND
function, but in either case 0.5 *will* round up to 1.

If, of course, your number is 0.46, for example, and it had originally
been
displayed to 1 decimal place it would *look like* 0.5, but it would round
down.
I would therefore recommend that you display your original number to more
decimal places to check exactly what it is.
There is a slight possibility that you've fallen victim to the fact that
most decimal numbers cannnot be represented exactly in fixed point
binary.
0.5 can, but 0.1 can't. [You can't represent 1/10 exactly in fixed point
binary, just as you can't represent 1/3 exactly in fixed point decimal.]
You may therefore have small rounding errors on your original numbers and
thus ended up after your subtraction with something which isn't exactly
0.5
although you would expect that it would be. Again if you extend the
number
of decimal places to see all 15 significant figures you should see if
this
is the case.
--
David Biddulph

I have a cell, which is the result of one cell minus another, but when
the
result is something point 5, excel rounds this down, and I want it to
round
the result up. Any ideas?