For statistical and mathematical purposes, OpenOffice.org's Calc spreadsheet includes a variety of ways to round off numbers. If you're a programmer, you may also be familiar with some of these methods. However, you don't need to be a specialist to find some of these methods useful. You may want to round off for billing purposes, or because decimal places don't translate well into the physical world -- for instance, if the parts you need come in packages of 100, then the fact you only need 66 is irrelevant to you; you need to round up for ordering.. By learning the options for rounding, you can make your spreadsheets more immediately useful for you.

When you use a rounding function, you have two choices about how to set up your formulas. If you choose, you can nest a calculation within one of the rounding functions. For instance, the formula =ROUND((SUM(A1;A2)) adds the figures in cells A1 and A2, then rounds them off to the nearest whole number. But, even though you don't need to work with exact figures everyday, you may still want to refer to them occasionally. If that is the case, then you are probably better off separating the two functions, placing =SUM(A1;A2) in cell A3, and =ROUND (A3) in A4, and clearly labelling each function. Which choice you make for layout depends largely on your work habits; personally, I prefer the second, on the grounds that I may not remember the details of what I am doing if I look at the spreadsheet six months after I create it.

Rounding methods

The most basic function for rounding numbers in Calc is ROUND. This function will round off a number according to the usual rules of symmetric arithmetic rounding: a decimal place of .4 or less gets rounded down, while one of .5 or more gets rounded up. However, at times, you may not want to follow these rules. For instance, if you are one of those contractors who bills a full hour for any fragment of an hour you work, you would want to always round up so you didn't lose any money. Conversely, you might choose to round down to give a slight discount to a long-established customer. In these cases, you might prefer to use ROUNDUP or ROUNDDOWN, whose names, which, just as their names suggest, round a number to the nearest integer above or below it.

All three of these functions require the single argument of number -- the cell or number to be rounded. Used with only this argument, all three functions round to the nearest whole number, so that 46.5 would round to 47 with ROUND or ROUNDUP and 46 with ROUNDDOWN. However, if you use the optional count argument, you can specify the number of decimal places to include. For instance, if number was set to 1, then 48.65 would round to 48.7 with ROUND or ROUNDUP and to 48.6 with ROUNDDOWN.

As an alternative to ROUNDDOWN when working with decimals, you can use TRUNC, which -- unsurprisingly -- is short for "truncate." It takes exactly the same arguments as ROUNDDOWN, so which function you use is a matter of choice. If you aren't working with decimals, you might choose to use INT (short for "integer"), which takes only the number argument.

Another option is the ODD and EVEN pair of functions. ODD rounds up to the nearest odd number if what is entered in the number argument is a positive number, and rounds down if it is a negative number, while EVEN does the same for an even number.

Still another option are the CEILING and FLOOR functions. As you can guess from the names, CEILING rounds up and FLOOR rounds down. For both functions, the number that they round to is determined by the closest multiple of the number that you enter as the significance argument. For instance, if your business insurance is billed by the work week, the fact that you were only open three days one week would be irrelevant to your costs; you would still be charged for an entire week, and therefore might want to use CEILING in your monthly expenses. Conversely, if you are building customized computers and completed 4.5 in a day, your client would only be interested in the number ready to ship, so you might use FLOOR in a report of your progress.

Besides number and significance, both CEILING and FLOOR include an optional argument called mode, which takes a value of 0 or 1. If mode is set to 0, and both the number and the significance are negative numbers, then the result of either function is rounded up; if it is set to 1, and both the number and the significance are negative numbers, the the results are rounded down. In other words, if the number is -11 and the significance is -5, then the result is -10 when the mode is set to 0, but -15 when set to 1.

However, if you are exchanging spreadsheets between Calc and MS Excel, remember that the mode argument is not supported by Excel. If you want the answers to be consistent between the two spreadsheets, set the mode in Calc to -1.

A function somewhat similar to CEILING and FLOOR is MROUND. Like CEILING AND FLOOR, MROUND requires two arguments, although, somewhat confusingly, the second one is called multiple rather than significance, even though the two are identical. The difference between MROUND and CEILING and FLOOR is that MROUND rounds up or down using symmetric arithmetic rounding. For example, if the number is 77 and the multiple is 5, then MROUND gives a result of 75. However, if the multiple is changed to 7, then MROUND's result becomes 77.

Conclusion

Once you tease the various rounding functions out of Calc's long, undifferentiated list of functions, you can start to decide which is most useful for your purposes.

However, one last point is worth mentioning: If you are working with more than two decimal places, don't be surprised if you don't see the same number of decimal places on the spreadsheet as you do on the function wizard. If you don't, the reason is that Tools -> Options -> OpenOffice.org Cal -> Calculate -> Decimal Places defaults to 2. Change the number of decimal places, and, if necessary, uncheck the Precision as shown box on the same tab, and the spreadsheet will display as expected.

__________________________
--
Bruce Byfield (nanday)