**I have an Excel spreadsheet for my small business that I use to figure employee payroll. I enter the hours worked, and Excel calculates gross wages, tax deductions, and net payroll. Every now and then the net payroll doesn't add up correctly; it will be a penny off. I know it's because of rounding, but I can't find the right combination in a formula to correct it. What formula can I use so that Excel will always round across the columns so they add correctly?**

**Paul Daniels**

As you note, the problem involves rounding. Suppose I get $99.75 per hour and work for 7.5 hours. Excel calculates my wages as $748.125, but you can't write a check for that amount. You've probably got Excel configured to use Currency formatting, in which case that value would display as $748.13. That is the amount you'll write on my check. Now suppose my three coworkers get the same. We're each owed $748.125, and we each get 1/2 cent more. If you sum our wages within your spreadsheet, you'll get a total that's 2 cents less than the sum of the values on the checks you wrote.

The solution is simple. When a value in your spreadsheet represents a real amount of money that must paid or collected in actual dollars and cents, change your formula to incorporate the rounding. For example, instead of **=**B2*****C2, use the formula **=**ROUND**(**B2*****C2**,2)**. That will actually round the result to two decimal places. The value displayed and the value used in calculations will be the same, so you'll have no more discrepancies.

## No comments:

Post a Comment