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?

"dartanion" <(E-Mail Removed)> wrote in message
news:4776767D-2482-4543-A6FF-(E-Mail Removed)...
>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.

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

"dartanion" <(E-Mail Removed)> wrote in message
news:4776767D-2482-4543-A6FF-(E-Mail Removed)...
>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?

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!

"David Biddulph" wrote:

> 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
>
> "dartanion" <(E-Mail Removed)> wrote in message
> news:4776767D-2482-4543-A6FF-(E-Mail Removed)...
> >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?
>
>
>

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

"dartanion" <(E-Mail Removed)> wrote in message
news:5478743B-D914-49D9-B0E5-(E-Mail Removed)...
> 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!
>
> "David Biddulph" wrote:
>
>> 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
>>
>> "dartanion" <(E-Mail Removed)> wrote in message
>> news:4776767D-2482-4543-A6FF-(E-Mail Removed)...
>> >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?
>>
>>
>>