[CE logo] Thanks for visiting our world wide site. We hope to leave you with a handy tip.

We will periodically post a new tip, about once a month or faster. So if the one below is not applicable to your software, be sure to drop by soon.

If you have a suggestion you'd like to share with others or you need a tip on a software application, drop me an e-mail.

[*] Rounding in Electronic Spreadsheets

Computers, and all spreadsheet applications, cannot resolve real-world rounding problems.

For example, you decide to give 3 kids $10.00. You will have a conflict, for 2 kids receive equal amounts, $3.33; but one kid gets $3.34. Unless, of course, you pocket the extra penny...

A computer would calculate each person's share to be 3.3333333 (and so on). When totalled, a computer would yield 9.99999. That's a big problem if you are dealing with company financials.

Electronic spreadsheets include a ROUND function to accommodate for these scenarios. Using formatting or styles is not sufficient! You only need worry about this in a spreadsheet if you have division, percentages, averages, etc.

The ROUND function truly rounds the value or calculation to the number of decimals specified. Again, formatting does not do this.

Consider the following point-of-sale (Canadian, eh) example.

Excel 1-2-3 or Quattro Pro incorrect
pre-tax amount 29.95 in cell A1 29.95 in cell A1 29.95
PST tax =A1*8% +A1*.08 2.396
GST tax =A1*7% +A1*.07 2.0965
amount tendered =SUM(A1:A3) @SUM(A1..A3) 34.4425

Excel 1-2-3 or Quattro Pro corrected
pre-tax amount 29.95 in cell A1 29.95 in cell A1 29.95
PST tax =ROUND(A1*8%,2) @ROUND(A1*.08,2) 2.4
GST tax =ROUND(A1*7%,2) @ROUND(A1*.07,2) 2.1
amount tendered =SUM(A1:A3) @SUM(A1..A3) 34.45

[*] Old Tips

And, remember, never let a computer know you're in a hurry...

[Home]   [Up]   [Le Menu]   [Suits]   [Calendar]   [PSION]   [HTML]   [small world]
Home | Top | Services | Resources | Availability | PSION | Samples | blake's world
Last updated: 29 January 1996

~end of page~