Problem with rounding?

[Guest]

Hi
I use excel for calculating calibration errors based on range/target
value/actual value, and then round the answers to two decimal places. I have
noticed some errors where the figures are being rounded too high. Please see
example below:

Range 0 - 200
Target Value: 100
Input Value: 99.94
Actual error: -0.06
Error as % of range: -0.03
Using the formula: ROUNDUP((((100-99.94)/200)*100),2)
I get the answer -0.04

Can you tell me if I'm doing something wrong?!
Thank you.

 

[Stephen]

Hi
I use excel for calculating calibration errors based on range/target
value/actual value, and then round the answers to two decimal places. I
have
noticed some errors where the figures are being rounded too high. Please
see
example below:

Range 0 - 200
Target Value: 100
Input Value: 99.94
Actual error: -0.06
Error as % of range: -0.03
Using the formula: ROUNDUP((((100-99.94)/200)*100),2)
I get the answer -0.04

Can you tell me if I'm doing something wrong?!
Thank you.

Why are you using ROUNDUP? This forces any value greater than 0.03 (greater
even by a minuscule amount) to round UP to the next value, which is 0.04.
You should use the ROUND function.

The result of ((100-99.94)/200)*100), as calculated by my version of Excel,
is 0.0300000000000011, which is quite typical. That's why we bother with
rounding to some sensible number of decimal places. ROUNDUP is the wrong
function to get the right answer for what you want.

 

[Guest]

user "Round" rather than "Roundup", the systax is the same.

 

[Guest]

Hi,

If you do the calculation without rounding and expand the field of decimal
places you will see that the answer is :-

0.0300000000000011

Which looks like it is rounding up to 0.04

Mike

 

[Guest]

Great, thanks for the answers. That does appear to reslove my issue.
I guess the excel glitch that causes the 0.0300000000000011 answer will
never be overcome though... ;-)

 

[Guest]

Actually... I've still got a similar problem. For my calculations I need to
round down if the next decimal place is <5, and round up if the next decimal
place is >5.
Using the figures below, I am running into problems:

Range 0 - 200
Target Value: 50
Input Value: 49.85
Actual error: -0.15
Error as % of range: -0.08 (-0.075)
Using the formula: ROUND((((50-49.85)/200)*100),2)
I get the answer -0.07

That's why I was using the ROUNDUP option.
Is there a way to get around this?
Thanks again

 

[Stephen]

This is NOT an Excel glitch. It's caused by representing numbers to finite
precision, which you have to do to make the hardware. Whatever number of
bits you choose for the representation in binary (which is what digital
computers use), you cannot represent most numbers exactly. It's a fact of
life we must live with. So we need to be aware of it and design spreadsheets
accordingly. Hence ROUND.

 

[Stephen]

You still haven't taken on board what was said!

The result of calculating ((50-49.85)/200)
is
0.000749999999999993

Taking your requirements, as the next decimal place is 4 (which is less than
5) it should round down, which it does.

Neither have you said what you require if the next decimal place = 5. You
can't be cavalier about such things and expect it all to just do what you
want. Neither can you arbitrarily change the function to ROUNDUP and expect
that to work for all situations.

 

[Guest]

round down if the next decimal place is <5, and round up if the next decimal

place is >5.

What will you do if it =5?

Why use round at all why not format that cell to the precision you want by
displaying it as a number with (say) 2 decimal places. That way Excel will do
what you require and has the added advantage of not changing the underlying
value of that number which the rounding functions do.

Mike

 

[Guest]

Ok - I should have clarified further:
If the next decimal place <5 then round down.
If the next decimal place is greater than or equal to 5 then round up.
Is it possible to set this condition? If I just display it as a number with
two decimal places I get the lower value (-0.07 as per below).
Thanks again.

 

[Sandy Mann]

Ok So does:

=ROUND(ROUND((((50-49.85)/200)*100),3),2)

Do what you want?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

That does appear to work. I was kind of thinking along those lines myself.
Thanks for that.

 

[David Biddulph]

Use the ROUND function if you want to change the value, or just format the
cell to the required number of decimal places if you want only to affect the
display.
In both cases it will do as you request: >=5 rounds up, and <5 rounds down.
If you are asking Excel to round your 0.000749999999999993 to 0.00075 and
then round that result to 0.0008, then you'll have to do it in those two
stages, such as =ROUND(ROUND(your_formula,5),4).

 

[Sandy Mann]

Glad to help, hope that it continues to do what you want. Thanks for the
feedback.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk