software

Please, please, please could anyone help with a conditional rounding problem
I urgently need to solve.

A figure needs to be calculated to 3 decimal places with the fourth decimal
place rounded DOWN when it is 5 or less but rounded UP if it is 6 or greater.

Example: 10,000/7,000 = 1.4285714 Rounded to 1.428
Example: 10,000/7,700 = 1.2987013 Rounded to 1.299

Many thanks
AnnieHi Annie

For some strange reason you are shifting the general rounding rule by
0.0001. Normally 5 would round up but you want it to round down. So you
have to adjust your number.

E.g. if you number is in A1. The formula in B1 is =Round(A1-0.0001,3)
That way you shift it and then round to 3.Annie

why conditional ? Doesn't ROUND do it ?

=ROUND(A1,3)

But I think the ROUND will Round UP on a 5

Maybe ROUNDDOWN(A1,3)

Regards

Trevorquot;Anniequot; gt; wrote in message
...
gt; Please, please, please could anyone help with a conditional rounding
gt; problem
gt; I urgently need to solve.
gt;
gt; A figure needs to be calculated to 3 decimal places with the fourth
gt; decimal
gt; place rounded DOWN when it is 5 or less but rounded UP if it is 6 or
gt; greater.
gt;
gt; Example: 10,000/7,000 = 1.4285714 Rounded to 1.428
gt; Example: 10,000/7,700 = 1.2987013 Rounded to 1.299
gt;
gt; Many thanks
gt; Annie
gt;
Thanks for your reply. I tried it but unfortunately 1.298701 is rounded down
to 1.298 and should be 1.299.

I need to be able to round a figure in accordance with the following
government regulations:
Amounts calculated to three decimal places only:
a) adding one thousandth where there would be more than five
ten-thousandths; and
b) ignoring the ten-thousandths where there would be five, or less than
five, ten-thousandths.

I've tried ROUND and ROUNDDOWN but neither seem to work in all scenarios.

Annie

quot; wrote:

gt; Hi Annie
gt;
gt; For some strange reason you are shifting the general rounding rule by
gt; 0.0001. Normally 5 would round up but you want it to round down. So you
gt; have to adjust your number.
gt;
gt; E.g. if you number is in A1. The formula in B1 is =Round(A1-0.0001,3)
gt; That way you shift it and then round to 3.
gt;
gt;

Correction:
Your suggestion does work - I was picking up the wrong cell when I tested it
in haste (oops!). It has been a long day.

Many thanks for your quick and very helpful reply.
VERY SORRY for not testing it properly before replying
Annie

quot;Anniequot; wrote:

gt; Thanks for your reply. I tried it but unfortunately 1.298701 is rounded down
gt; to 1.298 and should be 1.299.
gt;
gt; I need to be able to round a figure in accordance with the following
gt; government regulations:
gt; Amounts calculated to three decimal places only:
gt; a) adding one thousandth where there would be more than five
gt; ten-thousandths; and
gt; b) ignoring the ten-thousandths where there would be five, or less than
gt; five, ten-thousandths.
gt;
gt; I've tried ROUND and ROUNDDOWN but neither seem to work in all scenarios.
gt;
gt; Annie
gt;
gt; quot; wrote:
gt;
gt; gt; Hi Annie
gt; gt;
gt; gt; For some strange reason you are shifting the general rounding rule by
gt; gt; 0.0001. Normally 5 would round up but you want it to round down. So you
gt; gt; have to adjust your number.
gt; gt;
gt; gt; E.g. if you number is in A1. The formula in B1 is =Round(A1-0.0001,3)
gt; gt; That way you shift it and then round to 3.
gt; gt;
gt; gt;