Rounding

As has been observed in many scientific and business circle, Precision would appear to always be preferable.  For example, .98977 is more precise that 1.0.  There are times, however, when precision is Not desirable, and may even not make much sense.

For instance, let’s say you are staffing for a project, and you have calculated that you need 11.92 people to complete the job.  Obviously, it would be difficult to find that .92 person (Please Note that we are not taking number of person-hours into consideration) so you Round up to 12.  The same may be said about currency, as fractions of a penny may not produce valid data.

 

You can, of course, use formatting in Excel that will make your numbers Appear to be rounded.  The trouble with relying on formatting, however, is that Excel retains the precision in the background, and any calculations will be based on that precision. If you are dealing with numbers representing currency that have four decimal places, you would obviously want to express the results in no more that 2 decimal places, so you can format for showing exactly that (the unwanted Precision will nonetheless be lurking in the invisible details)! 
 

So what is the solution? Enter the ROUND function!  The syntax for the ROUND function is:  ROUND( number, digits )

A Few Examples are Illustrated Below:

In the following worksheet, Column B contains the rounding functions:

1.      B1 has:  =ROUND(A1, 2) and therefore rounds to two decimal places.

2.      In B2, =ROUND(A1, 0) is being used to round to zero decimals.

3.      In B3, more extreme rounding is being achieved by using =ROUND(A3, -1)

4.      B4 uses the same function as B3 and applies it to B4:  =ROUND(A4, -1)

Note:  If you always want Excel to round up, you can use the ROUNDUP function.  In our illustration, this would result in B3 being 30 rather than 20.  The ROUNDDOWN function does (no surprise) just the opposite.

Sometimes Precision is simply not the best choice.  In such cases, use ROUND!