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.

"AmImad?" wrote in message
...
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.

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

"AmImad?" wrote:

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.

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

"AmImad?" wrote:

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.

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... ;-)

"Mike H" wrote:

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

"AmImad?" wrote:

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.

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


"AmImad?" wrote:

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.

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.


"AmImad?" wrote in message
...
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... ;-)

"Mike H" wrote:

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

"AmImad?" wrote:

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.

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.


"AmImad?" wrote in message
...
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


"AmImad?" wrote:

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.

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

"AmImad?" wrote:

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


"AmImad?" wrote:

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.

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.

"Stephen" wrote:

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.


"AmImad?" wrote in message
...
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


"AmImad?" wrote:

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.

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


Replace @mailinator.com with @tiscali.co.uk


"AmImad?" wrote in message
...
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.

"Stephen" wrote:

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.


"AmImad?" wrote in message
...
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


"AmImad?" wrote:

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.

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

"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"AmImad?" wrote in message
...
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.

"Stephen" wrote:

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.


"AmImad?" wrote in message
...
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


"AmImad?" wrote:

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.

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).
--
David Biddulph

"AmImad?" wrote in message
...
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.

"Stephen" wrote:

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.


"AmImad?" wrote in message
...
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


"AmImad?" wrote:

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.

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


Replace @mailinator.com with @tiscali.co.uk


"AmImad?" wrote in message
...
That does appear to work. I was kind of thinking along those lines myself.
Thanks for that.

"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"AmImad?" wrote in message
...
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.

"Stephen" wrote:

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.


"AmImad?" wrote in message
...
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


"AmImad?" wrote:

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.