Conditional Rounding

[Guest]

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
Annie

 

[ceauke]

Hi 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.

 

[Trevor Shuttleworth]

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

Trevor

 

[Guest]

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

 

[Guest]

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