If Formula Between Values

[dennyvle]

Hello,

I'm trying to set up a formula to calculate social security expense every month. The problem is the social security has an annual max limit of $7,254.

So I have the monthly salary and the social security rate of 6.20%.

Here is an example:

	Jan	Feb	Mar	Apr	May
Salary	$ 40,000.00	$ 40,000.00	$ 40,000.00	$ 40,000.00	$ 40,000.00
SS Rate	6.20%	6.20%	6.20%	6.20%	6.20%
SS	$ 2,480.00	$ 2,480.00	$ 2,480.00	$ 2,480.00	$ 2,480.00

<colgroup><col><col span="5"></colgroup><tbody>
</tbody>

In March, the social security already exceeds the $7,254 limit. I need the total social security in Jan-Mar to equal exactly $7,254.

Thanks in advance,

 

[bbott]

welcome to the board.

Assuming your table starts in A1, enter this in B4:

=IF(B2*B3>7254,7254,B2*B3)

Enter this in C4 and drag across:

=IF(SUM($B$4:B4)+C2*C3>=7254,7254-SUM($B$4:B4),C2*C3)

 

[Comfy]

This can probably be simplified but in the meantime I will post what I have:

Excel 2010
	B	C	D	E	F	G
3		Jan	Feb	Mar	Apr	May
4	Salary	40000	40000	40000	40000	40000
5	SS Rate	6.20%	6.20%	6.20%	6.20%	6.20%
6	SS	2480	2480	2294	0	0
Sheet2

Cell Formulas
Range	Formula
C6	=IF(SUM($B$6:B6)+(C4*C5)<=7254,C4*C5,C4*C5-(ABS(7254-(SUM($B$6:B6)+(C4*C5)))))

 

[dennyvle]

Perfect. Thanks you!