For most users, the ROUND function is pretty self explanatory. A decimal of .5 or above rounds up and anything else rounds down. With ROUND, you can select the number of decimal places. You can also choose negative decimal places.
=ROUND(1524,-3)
will return 2,000. The -3 tells Excel to round to the nearest thousand.
That’s useful for factors of 10, but what about lesser factors, like 5. For that, you can use the MROUND function which is part of the Analysis Toolpack Add-in.
=MROUND(22,5)
will return 20, the closest multiple of 5. If you don’t want to install the ATP, you can achieve the same result with a formula like this
=ROUND(22/5,0)*5
This divides the number by 5, rounds it to no decimal places, then multiplies it by 5.
You can also use MROUND for multiples less than 1. To round a price to the nearest nickel, you can use
=MROUND(10.56,.05)
to get 10.55. If you’re a shrewd business person, you might want to always round up. For that, use the CEILING function
=CEILING(10.56,.05)
to get 10.60. Mmmm…profits.
There are a lot of other rounding functions. See help on ROUND if none of the above fill the bill.
“For most users, the ROUND function is pretty self explanatory.”
I was for me until I read:
How To Implement Custom Rounding Procedures
http://support.microsoft.com/default.aspx?scid=kb;en-us;196652
Now I always check the rounding assumptions of the function I’m using.
Jamie.
But I haven’t seen any decent function to perform a rounding while eliminating the error between the “sum of the rounded” and the “rounded sum”, e.g. like the following table
A B C
descvaluerounded
a1000.491001
b1000.491001
c1.49 1
d1.49 1
sum2003.962004
I came with (cell C2, but copied down):
{=ROUND(B2-((1/((ROUND(B2,0)-B2)/B2))/(SUM(1/((ROUND($B$2:$B$5,0)-$B$2:$B$5)/$B$2:$B$5)))*(SUM(ROUND($B$2:$B$5,0))-SUM($B$2:$B$5))),0)}
which does the trick, but is a bit awkward.
The basic idea is correcting the error on the values in a way that creates the least error on each individual value.
Any suggestions for a more esthetically acceptable solution?
Could you please explain how round function is used in time sheets to calculate the time in and out of employees??
thanks