Formula to subtract pay periods each payday

[Robert Wyatt]

I'm retired and have to watch my yearly income due to I can't go over 21,240.00 a year or they will take 3.00 dollars for every dollar I'm over from my social security check. Is there a formula in Excel that I can use that will subtract pay periods from the remainder of periods to go? I get paid bi-weekly that is 26 pay periods for the year. Right now I have it fixed up as if I was doing payroll year to date. but not sure it will be the same. I need it to subtract each pay period from 21,240.00 so I can keep track of how much I have left before going over the yearly limit.

 

[awoohaw]

would something like this help:

Mr excel questions 53.xlsm
	A	B	C	D
1	Payperiod	Pay	YTD	Amt To Earn Before Penalty
2	1	848	848	20392
3	2	915	1763	19477
4	3	617	2380	18860
5	4	828	3208	18032
6	5	904	4112	17128
7	6	857	4969	16271
8	7	949	5918	15322
9	8	842	6760	14480
10	9	678	7438	13802
11	10	980	8418	12822
12	11	961	9379	11861
13	12	915	10294	10946
14	13	660	10954	10286
15	14	866	11820	9420
16	15	782	12602	8638
17	16	684	13286	7954
18	17	692	13978	7262
19	18	640	14618	6622
20	19	888	15506	5734
21	20	711	16217	5023
22	21	898	17115	4125
23	22	650	17765	3475
24	23	736	18501	2739
25	24	805	19306	1934
26	25	996	20302	938
27	26	735	21037	203
RobertWyatt

Cell Formulas
Range	Formula
C2:C27	C2	=SUM($B$2:B2)
D2:D27	D2	=21240-C2

 

[Robert Wyatt]

Yes I think this will work better than what I have now. thanks for the help.

 

[awoohaw]

Happy to have helped you find a solution.
Best Wishes!

 

[Robert Wyatt]

I know I mark this as work, but I have a question how come it keeps showing an error when it post's about three times? How can I correct it?

 

[awoohaw]

I know I mark this as work, but I have a question how come it keeps showing an error when it post's about three times? How can I correct it?

Robert, I'm not sure what the error is. Can you post a mini worksheet using xl2bb with the underlying data and the formulas that have the errors?

 

[Robert Wyatt]

Asked to here is the program you helped me out with but it keeps having Eorros in the YTD Figures

Blank Payroll Keeper.xlsm
	A	B	C	D	E
1			Allowed Annually:		$21,240.00
2			Multiplied By 26 Pay Periods:		$13,017.01
3			Amount To Earn Before Penatly's:		$8,222.99
4
5
6	Pay Period	Pay Date	Pay	YTD	Amount Before Penalty
7	1	01/05/23	$840.98	$840.98	$20,399.02
8	2	01/19/23	$807.48	$1,648.46	$19,591.54
9	3	02/02/23	$819.00	$2,467.46	$18,772.54
10	4	02/16/23	$840.98	$3,308.44	$17,931.56
11	5	03/02/23	$840.46	$4,148.90	$17,091.10
12	6	03/16/23	$798.50	$4,947.39	$16,292.61
13	7	03/30/23	$805.64	$5,753.03	$15,486.97
14	8	04/13/23	$798.50	$6,551.53	$14,688.47
15	9	04/27/23	$798.50	$7,350.03	$13,889.97
16	10	05/11/23	$798.50	$8,148.53	$13,091.47
17	11	05/25/23	$798.50	$8,947.02	$12,292.98
18	12	06/08/23	$850.00	$9,797.02	$11,442.98
19	13	06/22/23	$824.50	$10,621.52	$10,618.48
20	14	07/06/23	$798.50	$11,420.01	$9,819.99
21	15	07/20/23	$798.50	$12,218.51	$9,021.49
22	16	08/03/23	$798.50	$13,017.01	$8,222.99
Social Security Register

Cell Formulas
Range	Formula
E2	E2	=SUM(C7:C32)
E3	E3	=SUM(E1-E2)
D7:D22	D7	=IF(ISBLANK(C7)," ",SUM($C$7:C7))
E7:E22	E7	=IF(ISBLANK(C7)," ",SUM(21240-D7))

 

[awoohaw]

I don't see any errors in your example.
What cell(s) has an Error Value?

 

[Robert Wyatt]

Cell D8:D21 say: The formula in this cell refers to a range that has an additional number adjacent to it. from cell D8:D21 each time I add a new entry it error's the next cell as well.

 

[Sufiyan97]

Try

Book2
	A	B	C	D	E	F
1			Allowed Annually:		21240
2			Multiplied By 26 Pay Periods:		13017.01
3			Amount To Earn Before Penatly's:		8222.993
4
5
6	Pay Period	Pay Date	Pay	YTD	Amount Before Penalty
7	1	44931	840.98	840.98	20399.02
8	2	44945	807.48	1648.46	19591.54
9	3	44959	819	2467.46	18772.54
10	4	44973	840.98	3308.44	17931.56
11	5	44987	840.457	4148.897	17091.1
12	6	45001	798.497	4947.394	16292.61
13	7	45015	805.6405	5753.035	15486.97
14	8	45029	798.497	6551.532	14688.47
15	9	45043	798.497	7350.029	13889.97
16	10	45057	798.497	8148.526	13091.47
17	11	45071	798.497	8947.023	12292.98
18	12	45085	849.997	9797.02	11442.98
19	13	45099	824.497	10621.52	10618.48
20	14	45113	798.497	11420.01	9819.987
21	15	45127	798.497	12218.51	9021.49
22	16	45141	798.497	13017.01	8222.993
23
Sheet2

Cell Formulas
Range	Formula
E2	E2	=SUM(C7:C32)
E3	E3	=SUM(E1-E2)
D7:D22	D7	=IF(C7="","",SUM(D6,C7))
E7:E22	E7	=IF(C7="","",SUM(21240-D7))