Writing a Macro

[Guest]

I'm trying to set up a macro on my payroll sheet that will calculate if FICA
is due. Basically, I have a list of employees and I need to be able to
account for those employees that cap out of FICA. Right now, I've got a YTD
column (C), Employees (Rows 1-15), FICA (D). The amount entered in Column D
(FICA) depends on how much the employee has earned YTD. For example, FICA
will be paid by all employees who have made less than 97000 (Column C). Once
an employee reaches 97000, then they no longer pay FICA. We used to use
Lotus and I'm not familiar with how a macro works in Excel. Where would I
start to create a macro????

 

[Bernie Deitrick]

No need for a macro. Use a formula like this in cell D2:

=IF(C2<97000,B2,IF(C2-B2>97000,0,(B2-(C2-97000))))*0.062

This assumes that the weekly amount is in cell B2. 6.2% is the FICA rate for 2007.


HTH,
Bernie
MS Excel MVP

 

[Guest]

That appears to work, however, I think I need to tweak it a little. Is there
a way to make the formula so it recognizes if a cell is greater than or equal
to the 97000??? The formula works if the number is less than the 97000 or
greater than. However, if it is exactly 97000, the formula still calculates
FICA.

Thanks for your help!

 

[Bernie Deitrick]

Tia,

=IF(C2<=97000,B2,IF(C2-B2>97000,0,(B2-(C2-97000))))*0.062

But if it is 97000 exactly, you should still pay FICA..... Note that the 97000 should be the YTD
column, but to get to that total, you would still need to pay FICA, even if you were over 97000,
since you owe on the amount up to 97000.

Let's say that you made 10000 per week (just for an easy example). In each of the first 9 weeks,
you would pay 620 in FICA, and the 10th week, you would pay on the first 7000, to get to the final
yearly requirement.

HTH,
Bernie
MS Excel MVP