Thursday, May 8, 2014

Round like a Circle

On the face of things, Precision seems to always be preferable.  For instance, 12.324 is more precise that 12.3.  There are times, however, when precision is Not desireable, and may even not make much sense.

For example, let’s say you are staffing for a project, and you have calculated that you need 11.92 people to complete the job.  Clearly, you can’t find that .92 person (although one of my ex-brother-in-laws comes to mind…), so you Round up to 12.  The same may be said about currency, as fractions of a penny may not produce valid data.
There is always formatting in Excel that will make your numbers Appear to be rounded, of course.  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 trouble with relying on formatting, however, is that Excel retains the precision in the background, and any calculations will be based on that precision. 
So what do you do?  (Trumpets please…)  Use the ROUND function!  The syntax for the the ROUND function is:  ROUND( number, digits )

Four Examples are in Order:
In the worksheet below, 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 the enemy.  In such cases, use ROUND!


No comments: