Cap Value

[Payrolltbeinc]

I want to make this monthly PTO sheet as accurate and clean as possible. I want to combine columns K-M. The cap and rate of accrual is set by an employee's years of service. Can you recommend a formula that will calculate the Available balance that includes the maximum value? Thank you.

PTO ACCRUALS_SEPT 2021.xlsx
	C	D	E	F	G	H	I	J	K	L	M
1	Anniversary	Current PR	DOH	YOS	Hours Worked	Used PTO	BEGINNING BAL	Accrued	S/B AVAILABLE	CAP	BALANCE
2	8/6/2021	10/31/2021	8/6/2020	1.00	13.75	-	10.450000	0.3437500	10.793750	80.00	10.793750
3	6/24/2021	10/31/2021	6/24/2003	18.00	160.00	-	23.273860	9.2320000	32.505860	240.00	32.505860
4	3/6/2021	10/31/2021	3/6/2018	3.00	198.75	-	116.656071	7.6518750	124.307946	160.00	124.307946
5	6/5/2021	10/31/2021	6/5/2019	2.00	207.50	-	54.469216	7.9887500	62.457966	160.00	62.457966
6	4/5/2021	10/31/2021	4/5/2005	16.00	207.50	160.00	240.000000	11.9727500	91.972750	240.00	91.972750
7	1/23/2021	10/31/2021	1/23/2020	1.00	178.25	-	21.943800	4.4562500	26.400050	80.00	26.400050
8	4/27/2021	10/31/2021	4/27/2020	1.00	184.25	-	24.162500	4.6062500	28.768750	80.00	28.768750
9	1/30/2021	10/31/2021	1/30/2018	3.00	79.50	4.99	5.505007	3.0607500	3.575757	160.00	3.575757
10	3/1/2021	10/31/2021	3/1/1995	26.00	64.00	-	26.495276	3.6928000	30.188076	240.00	30.188076
11	4/27/2021	10/31/2021	4/27/2020	1.00	213.25	-	31.998784	5.3312500	37.330034	80.00	37.330034
12	9/2/2021	10/31/2021	9/2/2018	3.00	163.00	-	7.149350	6.2755000	13.424850	160.00	13.424850
13	9/19/2021	10/31/2021	9/19/2019	2.00	200.00	-	10.613423	7.7000000	18.313423	160.00	18.313423
14	10/6/2020	10/31/2021	10/6/2020	1.00	185.50	-	21.937500	4.6375000	26.575000	80.00	26.575000
15	5/8/2021	10/31/2021	5/8/2017	4.00	38.00	-	64.273971	2.1926000	66.466571	160.00	66.466571
16	10/4/2021	10/31/2021	10/4/2021	-	126.00	-	-	3.1500000	3.150000	80.00	3.150000
17	11/13/2020	10/31/2021	11/13/2020	-	170.00	-	8.936250	4.2500000	13.186250	80.00	13.186250
18	10/14/2021	10/31/2021	10/14/2014	7.00	0.75	-	2.898649	0.0432690	2.941918	240.00	2.941918
19	5/2/2021	10/31/2021	5/2/2017	4.00	194.50	-	43.365926	11.2226500	54.588576	160.00	54.588576
20	10/15/2020	10/31/2021	10/15/2018	3.00	136.75	-	(72.635800)	5.2648750	(67.370925)	160.00	-
21	8/1/2021	10/31/2021	8/1/2000	21.00	183.25	-	23.691445	10.5735250	34.264970	240.00	34.264970
22	6/6/2021	10/31/2021	6/6/2017	4.00	87.25	-	86.172418	5.0343250	91.206743	160.00	91.206743
23	4/29/2021	10/31/2021	4/29/2016	5.00	12.00	-	15.557808	0.6924000	16.250208	240.00	16.250208
24	6/10/2021	10/31/2021	6/10/2019	2.00	-	-	5.519133	-	5.519133	160.00	5.519133
25	10/26/2020	10/31/2021	10/26/1993	28.00	206.75	-	137.816147	11.9294750	149.745622	240.00	149.745622
26	6/5/2021	10/31/2021	6/5/2019	2.00	-	-	9.032250	-	9.032250	160.00	9.032250
27	9/12/2021	10/31/2021	9/12/2014	7.00	204.00	-	54.987730	11.7708000	66.758530	240.00	66.758530
28	3/25/2021	10/31/2021	3/25/2021	-	160.00	-	25.118750	4.0000000	29.118750	80.00	29.118750
29	9/1/2021	10/31/2021	9/1/2007	14.00	198.25	-	58.091116	11.4390250	69.530141	240.00	69.530141
30	5/21/2021	10/31/2021	5/21/1999	22.00	192.25	-	107.666569	11.0928250	118.759394	240.00	118.759394
31	11/1/2020	10/31/2021	11/1/1999	21.00	40.00	37.33	35.016216	2.3080000	(0.005784)	240.00	(0.005784)
32	7/12/2021	10/31/2021	7/12/2018	3.00	9.00	-	21.662621	0.3465000	22.009121	160.00	22.009121
33	9/30/2021	10/31/2021	9/30/2013	8.00	214.50	-	7.459521	12.3766500	19.836171	240.00	19.836171
34	4/19/2021	10/31/2021	4/19/2021	-	186.50	-	62.603934	4.6625000	67.266434	80.00	67.266434
35	9/8/2021	10/31/2021	9/8/2015	6.00	227.50	-	48.997505	13.1267500	62.124255	240.00	62.124255
36	8/6/2021	10/31/2021	8/6/2019	2.00	-	-	1.949280	-	1.949280	160.00	1.949280
37	9/21/2021	10/31/2021	9/21/2018	3.00	134.50	-	34.636990	5.1782500	39.815240	160.00	39.815240
38	1/15/2021	10/31/2021	1/15/2018	3.00	152.00	-	137.720868	5.8520000	143.572868	160.00	143.572868
39	9/16/2021	10/31/2021	9/16/2021	-	209.25	-	-	5.2312500	5.231250	80.00	5.231250
40	7/2/2021	10/31/2021	7/2/2007	14.00	195.50	-	210.645650	11.2803500	221.926000	240.00	221.926000
41	10/29/2020	10/31/2021	10/29/2018	3.00	137.75	24.00	42.021158	5.3033750	23.324533	160.00	23.324533
42	6/3/2021	10/31/2021	6/3/2019	2.00	168.50	-	21.065650	6.4872500	27.552900	160.00	27.552900
43	6/21/2021	10/31/2021	6/21/2004	17.00	195.00	-	83.636170	11.2515000	94.887670	240.00	94.887670
44	4/15/2021	10/31/2021	4/15/2019	2.00	-	-	11.695695	-	11.695695	160.00	11.695695
45	6/12/2021	10/31/2021	6/12/2017	4.00	164.00	-	143.704298	9.4628000	153.167098	160.00	153.167098
46	7/17/2021	10/31/2021	7/17/2020	1.00	135.50	-	49.637500	3.3875000	53.025000	80.00	53.025000
47	4/3/2021	10/31/2021	4/3/2006	15.00	164.00	-	50.365016	9.4628000	59.827816	240.00	59.827816
48	7/24/2021	10/31/2021	7/24/2005	16.00	196.00	-	153.942487	11.3092000	165.251687	240.00	165.251687
49	3/7/2021	10/31/2021	3/7/2011	10.00	130.25	-	141.211668	7.5154250	148.727093	240.00	148.727093
50	7/10/2021	10/31/2021	7/10/2017	4.00	189.00	-	173.674900	10.9053000	184.580200	160.00	184.580200
OCT 21

Cell Formulas
Range	Formula
K2:K50	K2	=(J2+I2)-H2
L2:L50	L2	=IFS($F2<=1,80,$F2<=2,160,$F2<=3,160,$F2<=4,160,$F2>=4.55,240)
M21:M50,M2:M19	M2	=K2
J2:J17,J19:J50	J2	=IFS(F2<=1,(ROUND(G2*0.025,6)),(F2<=3),(ROUND(G2*0.0385,6)),(G2>=5),(ROUND(G2*0.0577,6)))
F2:F50	F2	=DATEDIF(E2,DATE(2021,10,31),"y")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:Q50	Expression	=EVEN(ROW())=ROW()	text	NO
A2:Q50	Expression	=ODD(ROW())=ROW()	text	NO

 

[jasonb75]

Column M appears to be nothing more than a duplication of column K anyway so there is nothing to combine, it just needs to be deleted.

 

[Payrolltbeinc]

Column M appears to be nothing more than a duplication of column K anyway so there is nothing to combine, it just needs to be deleted.

In most cases yes. for example row 50 the employee has reached his max value. I don't want to override the formula cells if I can find a solution around it.

PTO ACCRUALS_SEPT 2021.xlsx
	E	F	G	H	I	J	K	L		N	O
1	DOH	YOS	Hours Worked	Used PTO	BEGINNING BAL	Accrued	S/B AVAILABLE	CAP		FNDTN EB	VARIANCE
2	8/6/2020	1.00	13.75	-	10.450000	0.3437500	10.793750	80.00		10.793750	-
3	6/24/2003	18.00	160.00	-	23.273860	9.2320000	32.505860	240.00		32.504580	0.001280
4	3/6/2018	3.00	198.75	-	116.656071	7.6518750	124.307946	160.00		124.300195	0.007751
5	6/5/2019	2.00	207.50	-	54.469216	7.9887500	62.457966	160.00		54.469216	7.988750
6	4/5/2005	16.00	207.50	160.00	240.000000	11.9727500	91.972750	240.00		82.509602	9.463148
7	1/23/2020	1.00	178.25	-	21.943800	4.4562500	26.400050	80.00		26.400050	-
8	4/27/2020	1.00	184.25	-	24.162500	4.6062500	28.768750	80.00		28.768750	-
9	1/30/2018	3.00	79.50	4.99	5.505007	3.0607500	3.575757	160.00		0.515007	3.060750
10	3/1/1995	26.00	64.00	-	26.495276	3.6928000	30.188076	240.00		30.187564	0.000512
11	4/27/2020	1.00	213.25	-	15.998784	5.3312500	21.330034	80.00		21.330034	-
12	9/2/2018	3.00	163.00	-	7.149350	6.2755000	13.424850	160.00		13.418493	0.006357
13	9/19/2019	2.00	200.00	-	10.613423	7.7000000	18.313423	160.00		18.305624	0.007799
14	10/6/2020	1.00	185.50	-	21.937500	4.6375000	26.575000	80.00		26.575000	-
15	5/8/2017	4.00	38.00	-	62.782949	2.1926000	64.975549	160.00		64.244467	0.731082
16	10/4/2021	-	126.00	-	-	3.1500000	3.150000	80.00		3.150000	-
17	11/13/2020	-	170.00	-	9.936250	4.2500000	14.186250	80.00		14.186250	-
18	10/14/2014	7.00	0.75	-	2.898649	0.0432690	2.941918	240.00		2.941918	-
19	5/2/2017	4.00	194.50	-	43.365926	11.2226500	54.588576	160.00		50.846591	3.741985
20	10/15/2018	3.00	136.75	-	7.364200	5.2648750	12.629075	160.00		12.623743	0.005332
21	8/1/2000	21.00	183.25	-	2.691445	10.5735250	13.264970	240.00		13.032736	0.232234
22	6/6/2017	4.00	87.25	-	86.172418	5.0343250	91.206743	160.00		89.528141	1.678602
23	4/29/2016	5.00	12.00	-	15.557808	0.6924000	16.250208	240.00		16.250112	0.000096
24	6/10/2019	2.00	-	-	5.519133	-	5.519133	160.00		5.519133	-
25	10/26/1993	28.00	206.75	-	137.816147	11.9294750	149.745622	240.00		149.397816	0.347806
26	6/5/2019	2.00	-	-	9.032250	-	9.032250	160.00		9.032250	-
27	9/12/2014	7.00	204.00	-	54.987730	11.7708000	66.758530	240.00		66.756898	0.001632
28	3/25/2021	-	160.00	-	25.118750	4.0000000	29.118750	80.00		29.118750	-
29	9/1/2007	14.00	198.25	-	58.091116	11.4390250	69.530141	240.00		69.124711	0.405430
30	5/21/1999	22.00	192.25	-	107.666569	11.0928250	118.759394	240.00		118.757856	0.001538
31	11/1/1999	21.00	40.00	37.33	35.016216	2.3080000	(0.005784)	240.00		(0.006104)	0.000320
32	7/12/2018	3.00	9.00	-	22.662621	0.3465000	23.009121	160.00		23.008771	0.000350
33	9/30/2013	8.00	214.50	-	27.459521	12.3766500	39.836171	240.00		39.834455	0.001716
34	4/19/2021	-	186.50	-	62.603934	4.6625000	67.266434	80.00		66.108602	1.157832
35	9/8/2015	6.00	227.50	-	48.997505	13.1267500	62.124255	240.00		62.122435	0.001820
36	8/6/2019	2.00	-	-	1.797978	-	1.797978	160.00		1.797978	-
37	9/21/2018	3.00	134.50	-	17.636990	5.1782500	22.815240	160.00		22.809995	0.005245
38	1/15/2018	3.00	152.00	-	129.720868	5.8520000	135.572868	160.00		135.566940	0.005928
39	9/16/2021	-	209.25	-	-	5.2312500	5.231250	80.00		5.231250	-
40	7/2/2007	14.00	195.50	-	210.645650	11.2803500	221.926000	240.00		221.924436	0.001564
41	10/29/2018	3.00	137.75	24.00	42.021158	5.3033750	23.324533	160.00		23.319161	0.005372
42	6/3/2019	2.00	168.50	-	21.062882	6.4872500	27.550132	160.00		27.543562	0.006570
43	6/21/2004	17.00	195.00	-	83.636170	11.2515000	94.887670	240.00		94.886110	0.001560
44	4/15/2019	2.00	-	-	11.695695	-	11.695695	160.00		11.695695	-
45	6/12/2017	4.00	164.00	-	143.704298	9.4628000	153.167098	160.00		150.011902	3.155196
46	7/17/2020	1.00	135.50	-	49.637500	3.3875000	53.025000	80.00		53.025000	-
47	4/3/2006	15.00	164.00	-	50.365016	9.4628000	59.827816	240.00		59.827816	-
48	7/24/2005	16.00	196.00	-	153.942487	11.3092000	165.251687	240.00		165.250119	0.001568
49	3/7/2011	10.00	130.25	-	141.211668	7.5154250	148.727093	240.00		148.726051	0.001042
50	7/10/2017	4.00	189.00	-	160.000000	10.9053000	160.000000	160.00		160.000000	-
OCT 21

Cell Formulas
Range	Formula
L2:L50	L2	=IFS($F2<=1,80,$F2<=2,160,$F2<=3,160,$F2<=4,160,$F2>=4.55,240)
J2:J17,J19:J50	J2	=IFS(F2<=1,(ROUND(G2*0.025,6)),(F2<=3),(ROUND(G2*0.0385,6)),(G2>=5),(ROUND(G2*0.0577,6)))
K2:K49	K2	=(J2+I2)-H2
F2:F50	F2	=DATEDIF(E2,DATE(2021,10,31),"y")
O2:O50	O2	=M2-N2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:Q50	Expression	=EVEN(ROW())=ROW()	text	NO
A2:Q50	Expression	=ODD(ROW())=ROW()	text	NO

 

[jasonb75]

So what has happened to column M, Did it just grow legs and walk out of the sheet?

Based on what you've given us to work with so far, the best I can offer is =(J2+I2)-H2-N2 in K2. Remember the answer is only as good as the question.

 

[Payrolltbeinc]

So what has happened to column M, Did it just grow legs and walk out of the sheet?

Based on what you've given us to work with so far, the best I can offer is =(J2+I2)-H2-N2 in K2. Remember the answer is only as good as the question.

My original question still remains unanswered. I want to write (or have someone write for me) a formula that will allow me to delete the redundant columns on my sheet. I want to eliminate the need for Column L because the formula in Column K has that information taking into consideration in the formula. The current version of this file makes it so I have to manually override the cell formula if their PTO hours earned has reached the max levels. Column M was my attempt to not loose the original calculation. My question is how can I accomplish what I want to do?

 

[jasonb75]

Best guess

=MIN(J2+I2,LOOKUP(F2,{0,2,5},{80,160,240}))-H2-N2

 

[Payrolltbeinc]

Best guess

=MIN(J2+I2,LOOKUP(F2,{0,2,5},{80,160,240}))-H2-N2

PTO ACCRUALS_SEPT 2021.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O
2	8/6/2021	10/31/2021	8/6/2020	1.00	13.75	-	10.450000	0.3437500	-	80.00	-	10.793750	(10.793750)
OCT 21

Cell Formulas
Range	Formula
F2	F2	=DATEDIF(E2,DATE(2021,10,31),"y")
J2	J2	=IFS(F2<=1,(ROUND(G2*0.025,6)),(F2<=3),(ROUND(G2*0.0385,6)),(G2>=5),(ROUND(G2*0.0577,6)))
K2	K2	=MIN(J2+I2,LOOKUP(F2,{0,2,5},{80,160,240}))-H2-N2
L2	L2	=IFS($F2<=1,80,$F2<=2,160,$F2<=3,160,$F2<=4,160,$F2>=4.55,240)
M2	M2	=K2
O2	O2	=M2-N2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:Q50,A2:J2,L2:Q2	Expression	=EVEN(ROW())=ROW()	text	NO
A3:Q50,A2:J2,L2:Q2	Expression	=ODD(ROW())=ROW()	text	NO

Thank you for your suggestion. I really appreciate you taking the time to help me problem solve.

 

[jasonb75]

Thank you for your suggestion. I really appreciate you taking the time to help me problem solve.

Does that mean it is giving you what is needed? If not then please repost your full example highlighting any rows where the result is wrong and enter the correct result should be next to it.

 

[Payrolltbeinc]

Does that mean it is giving you what is needed? If not then please repost your full example highlighting any rows where the result is wrong and enter the correct result should be next to it.

No it didn't work. I pasted =MIN(J2+I2,LOOKUP(F2,{0,2,5},{80,160,240}))-H2-N2 into cell K2 and got the result of 0. I have created a new tab for when I have my Nov numbers and adjusted the formula in the YOS (Column F) to reflect the change in Column D. I copied the values from Column K and pasted the values on the new sheet Column I. I also updated Column C for those who had an anniversary. Maybe it will be easier for you to see what I am trying to do looking at a blank sheet. I want to utilize formulas more to cut down on the total number of columns used.

PTO ACCRUALS_SEPT 2021.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N
1	Anniversary	Current PR	DOH	YOS	Hours Worked	Used PTO	BEGINNING BAL	Accrued	S/B AVAILABLE	CAP	Pay Rate	Value
2	8/6/2021	11/30/2021	8/6/2020	1.00			10.793750	-	10.793750	80.00	$ 13.85	$ 149.49
3	6/24/2021	11/30/2021	6/24/2003	18.00			32.505860	#N/A	#N/A	240.00	$ 45.00	#N/A
4	3/6/2021	11/30/2021	3/6/2018	3.00			124.307946	-	124.307946	160.00	$ 26.00	$ 3,232.01
5	6/5/2021	11/30/2021	6/5/2019	2.00			54.469216	-	54.469216	160.00	$ 16.35	$ 890.57
6	4/5/2021	11/30/2021	4/5/2005	16.00			91.972750	#N/A	#N/A	240.00	$ 40.00	#N/A
7	1/23/2021	11/30/2021	1/23/2020	1.00			26.400050	-	26.400050	80.00	$ 24.00	$ 633.60
8	4/27/2021	11/30/2021	4/27/2020	1.00			28.768750	-	28.768750	80.00	$ 25.00	$ 719.22
9	1/30/2021	11/30/2021	1/30/2018	3.00			3.575757	-	3.575757	160.00	$ 27.00	$ 96.55
10	3/1/2021	11/30/2021	3/1/1995	26.00			30.188076	#N/A	#N/A	240.00	$ 19.10	#N/A
11	4/27/2021	11/30/2021	4/27/2020	1.00			21.330034	-	21.330034	80.00	$ 28.00	$ 597.24
12	9/2/2021	11/30/2021	9/2/2018	3.00			13.424850	-	13.424850	160.00	$ 20.35	$ 273.20
13	9/19/2021	11/30/2021	9/19/2019	2.00			18.313423	-	18.313423	160.00	$ 30.35	$ 555.81
14	10/6/2021	11/30/2021	10/6/2020	1.00			26.575000	-	26.575000	80.00	$ 24.35	$ 647.10
15	5/8/2021	11/30/2021	5/8/2017	4.00			64.975549	#N/A	#N/A	160.00	$ 16.00	#N/A
16	10/4/2021	11/30/2021	10/4/2021	-			3.150000	-	3.150000	80.00	$ 18.00	$ 56.70
17	11/13/2021	11/30/2021	11/13/2020	1.00			14.186250	-	14.186250	80.00	$ 23.00	$ 326.28
18	10/14/2021	11/30/2021	10/14/2014	7.00			2.941918	0.0432690	2.985187	240.00	$ 19.35	$ 57.76
19	5/2/2021	11/30/2021	5/2/2017	4.00			54.588576	#N/A	#N/A	160.00	$ 28.35	#N/A
20	10/15/2021	11/30/2021	10/15/2018	3.00			12.629075	-	12.629075	160.00	$ 20.85	$ 263.32
21	8/1/2021	11/30/2021	8/1/2000	21.00			13.264970	#N/A	#N/A	240.00	$ 31.00	#N/A
22	6/6/2021	11/30/2021	6/6/2017	4.00			91.206743	#N/A	#N/A	160.00	$ 21.00	#N/A
23	4/29/2021	11/30/2021	4/29/2016	5.00			16.250208	#N/A	#N/A	240.00	$ 25.55	#N/A
24	6/10/2021	11/30/2021	6/10/2019	2.00			5.519133	-	5.519133	160.00	$ 18.35	$ 101.28
25	10/26/2021	11/30/2021	10/26/1993	28.00			149.745622	#N/A	#N/A	240.00	$ 32.00	#N/A
26	6/5/2021	11/30/2021	6/5/2019	2.00			9.032250	-	9.032250	160.00	$ 17.00	$ 153.55
27	9/12/2021	11/30/2021	9/12/2014	7.00			66.758530	#N/A	#N/A	240.00	$ 23.00	#N/A
28	3/25/2021	11/30/2021	3/25/2021	-			29.118750	-	29.118750	80.00	$ 19.00	$ 553.26
29	9/1/2021	11/30/2021	9/1/2007	14.00			69.530141	#N/A	#N/A	240.00	$ 30.00	#N/A
30	5/21/2021	11/30/2021	5/21/1999	22.00			118.759394	#N/A	#N/A	240.00	$ 37.00	#N/A
31	11/1/2021	11/30/2021	11/1/1999	22.00			(0.005784)	#N/A	#N/A	240.00	$ 34.00	#N/A
32	7/12/2021	11/30/2021	7/12/2018	3.00			23.009121	-	23.009121	160.00	$ 20.00	$ 460.18
33	9/30/2021	11/30/2021	9/30/2013	8.00			39.836171	#N/A	#N/A	240.00	$ 25.35	#N/A
34	4/19/2021	11/30/2021	4/19/2021	-			67.266434	-	67.266434	80.00	$ 26.00	$ 1,748.93
35	9/8/2021	11/30/2021	9/8/2015	6.00			62.124255	#N/A	#N/A	240.00	$ 27.00	#N/A
36	8/6/2021	11/30/2021	8/6/2019	2.00			1.797978	-	1.797978	160.00	$ 27.00	$ 48.55
37	9/21/2021	11/30/2021	9/21/2018	3.00			22.815240	-	22.815240	160.00	$ 20.35	$ 464.29
38	1/15/2021	11/30/2021	1/15/2018	3.00			135.572868	-	135.572868	160.00	$ 40.00	$ 5,422.91
39	9/16/2021	11/30/2021	9/16/2021	-			5.231250	-	5.231250	80.00	$ 22.00	$ 115.09
40	7/2/2021	11/30/2021	7/2/2007	14.00			221.926000	#N/A	#N/A	240.00	$ 35.00	#N/A
41	10/29/2021	11/30/2021	10/29/2018	3.00			23.324533	-	23.324533	160.00	$ 28.00	$ 653.09
42	6/3/2021	11/30/2021	6/3/2019	2.00			27.550132	-	27.550132	160.00	$ 22.00	$ 606.10
43	6/21/2021	11/30/2021	6/21/2004	17.00			94.887670	#N/A	#N/A	240.00	$ 33.00	#N/A
44	4/15/2021	11/30/2021	4/15/2019	2.00			11.695695	-	11.695695	160.00	$ 13.85	$ 161.99
45	6/12/2021	11/30/2021	6/12/2017	4.00			153.167098	#N/A	#N/A	160.00	$ 31.00	#N/A
46	7/17/2021	11/30/2021	7/17/2020	1.00			53.025000	-	53.025000	80.00	$ 25.35	$ 1,344.18
47	4/3/2021	11/30/2021	4/3/2006	15.00			59.827816	#N/A	#N/A	240.00	$ 31.00	#N/A
48	7/24/2021	11/30/2021	7/24/2005	16.00			165.251687	#N/A	#N/A	240.00	$ 38.00	#N/A
49	3/7/2021	11/30/2021	3/7/2011	10.00			148.727093	#N/A	#N/A	240.00	$ 32.00	#N/A
50	7/10/2021	11/30/2021	7/10/2017	4.00			160.000000	#N/A	160.000000	160.00	$ 26.00	$ 4,160.00
NOV 21

Cell Formulas
Range	Formula
M3	M3	=ROUND(1800/40,2)
J2:J17,J19:J50	J2	=IFS(F2<=1,(ROUND(G2*0.025,6)),(F2<=3),(ROUND(G2*0.0385,6)),(G2>=5),(ROUND(G2*0.0577,6)))
M31	M31	=ROUND(1360.154/40,2)
M38	M38	=ROUND(1600/40,2)
K2:K49	K2	=(J2+I2)-H2
F2:F50	F2	=DATEDIF(E2,DATE(2021,11,30),"y")
L2:L50	L2	=IFS($F2<=1,80,$F2<=2,160,$F2<=3,160,$F2<=4,160,$F2>=4.55,240)
N2:N50	N2	=ROUND(M2*K2,2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:N50	Expression	=EVEN(ROW())=ROW()	text	NO
A2:N50	Expression	=ODD(ROW())=ROW()	text	NO

 

[jasonb75]

Maybe it will be easier for you to see what I am trying to do looking at a blank sheet.

It will be easier to see if you could simply do as I asked

If not then please repost your example highlighting any rows where the result is wrong and enter the correct result next to it.

When you posted your second example in post 3, I had assumed (due to lack of clarity) that column O was showing the results that were expected.

a formula that will calculate the Available balance that includes the maximum value?

Which is what the formula that I suggested takes into consideration. If the figures in column N bear no relevance then try removing that part of the equation from the formula and using

=MIN(J2+I2,LOOKUP(F2,{0,2,5},{80,160,240}))-H2

instead.
If that is still not what you want then please do as I asked and repost your example with the formula filled down the column and add a column showing the expected results in rows where the formula is not correct.