Payroll SpreadSheet

Hi. I need help on the forumla's for my spreadsheet. I seem to have everything inputed correctly. My formula's for Federal Withholding and State Withholding - I found from the internet and they seem to be correct for the most part. In the my current spreadsheet it isn't. The federal withholding for lucy henderson should be $10 not $9. Also when you calculate these numbers by calculator or by hand it comes up differently. For instance, 20.08 x 7.25 = 145.60 not 145.58. I'm not quite sure what i'm doing wrong. Its been bothering me for a long time not and I gave up until i found you exceltip. If you can help me with this please email me at [email protected]. Thanks for all your help in advance.



I have attached the spreadsheet in a zip file.

Welcome to the forum.
I can't open your file. If you re-attach it in pre-2007 Excel format, it will be easier to open.

Unless you save your file as an .xls file and zip it, a lot of folks are not going to be able to help you. Many of us don't have Excel 2007 and can't open your file.

Dean

To correct your problem you have to understand that formatting a number to 2 decimals does not mean that Excel will actually round off the number to 2 decimals. It is still saved and carried on to calculations with it full number of decimals. The formatting just rounds off for the display in that actual cell.

Your calculated result that you think is 20.08 is actualy 20.0833333333333

If you need to round a number and keep the rounded result you have to use the ROUND function. Instead of =C28*24 you must use =ROUND(C28*24,2) to round off to 2 decimals.

I'm also using Excel 2003, but have installed Microsofts free version converter. Then you can open 2007 files just as easy as 2003 files.

Hi. I tried the rounding formula that was suggested above. It does not seem to help the situation. I still get the same numbers. You think anyone can help me with the federal withholding as well?

I tried the rounding formula that was suggested above. It does not seem to help the situation. I still get the same numbers.

I see you have used the ROUND function in your new attachment. And it seems to be working OK ??

20.08 x 7.25 = 145.58 . This is the correct answer.

In your first attachment (without the ROUND formula) you would get 145.60 if you used your formula, but you had hardcoded the answer to be 145.58 instead of using a formula

The federal withholding for lucy henderson should be $10 not $9.

I don't know much about federal witholding, so you have to tell me how to calculate the correct answer to be able to help you.
You say the answer should be $10. How do you calculate this ?

Maybe this could be a rounding issue too, as the formula you have used in your spreadsheet gives this result:

10% x (145.58 - 51) = 9.458 (With formatting this displays as $9)

This result is not very far from 9.5 and with a rounding error in the constant (51) in your formula the result could be different.

10% x (145.58 - 50.5) = 9.508 (Would be displayed as $10 with formatting)

Please let me know if this makes any sense to you, and if you tell me how you calculate the federal witholding, I can help you to translate this to a useable Excel formula.

bjornar

ok i seem to be getting the correct numbers after everyone's suggestions. I just wanted to say i appreciate it a lot. I still have one more issue tho. For SWH (state with holding) the actual number should be 6 not 5.

As far as how to calculate fwh and swh as a formula. I have no idea. I found the FWH formula on the internet and the SWH was faxed to me by the state. The state of north carolina provides us with a booklet that has a table. The SWH and FWH calculations are for weekley payees since we pay our employees weekly. I hope this gives you all an idea of what i'm going through! It takes forever to look through that table when you are doing it for multiple employees.

Thanks for your help in advance.

milan

For SWH (state with holding) the actual number should be 6 not 5.

This is probably just an rounding error too. Your result of your formula is actually $5.27.
If your state allows you to round this off to the nearest dollar, i don't think they will arrest you if your result sometimes goes $1 up or down compared to the table you got. After all there are a great possibility that in the long run this will even out.

I would even don't worry about the errors like you getting $ 145.60 instead of $ 145.58 . Think about this: You use a timetable for your employees working times rounded off to the nearest 5 minute. This rounding gives you already a possibility of an error of $0.30 , for just 2,5 minutes rounding of the work time.....

I think your formulas are working fine. Don't care about using the ROUND() function. Just format each cell to the number of desimals that is the same as the number of desimals in the amount you are paying out.

The small errors you sometimes get, will even out in the long run !

If you discover any big differences in the results from what you expect (more than just a dollar or two) you would have to check your formulas.