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