Calculate deductible amount based on year

[JohnZ1156]

I have pet insurance for my dog. As I submit a medical claim, there is a $100 deductible for each new year.
I have a table.
Column C is the Date (MM/DD/YYYY),
Column E is a $ Amount Covered
Column F is the 90% Amount
Column G is the Deductible amount.
Column H is the Reimbursement Amount

I would like a formula in column G to automaticall calculate the deductible at the start of each new year in column C until it reached $100 for that year.
If the amount of the bill is not enough to cover the $100 deductible, then deduct it from the next date entry until a max of $100 is satisfied.

 

[StephenCrump]

You can calculate the reimbursement in one formula. I have added Column D just to illustrate the deductible. I've assumed "year" means calendar year.

	A	B	C	D	E
1	Deductible	100
2
3	Date	Covered	Reimbursement	Deductible
4	Jan 15 2020	90	0	90
5	May 15 2020	40	30	10
6	Sep 15 2020	70	70	0
7	Jan 15 2021	20	0	20
8	May 15 2021	60	0	60
9	Sep 15 2021	200	180	20
10	Jan 15 2022	140	40	100
11	May 15 2022	80	80	0
12	Sep 15 2022	180	180	0
13	Jan 15 2023	140	40	100
14	May 15 2023	200	200	0
15	Sep 15 2023	160	160	0
16	Jan 15 2024	140	40	100
17	May 15 2024	120	120	0
18	Sep 15 2024	160	160	0
19	Jan 15 2025	20	0	20
20	May 15 2025	100	20	80
21	Sep 15 2025	100	100	0
22	Jan 15 2026	60	0	60
23	May 15 2026	60	20	40
24	Sep 15 2026	80	80	0
25	Jan 15 2027	160	60	100
26
Sheet1

Cell Formulas
Range	Formula
C4:C25	C4	=MEDIAN(0,B4,SUM(FILTER(B$4:B4,YEAR(A$4:A4)=YEAR(A4)))-B$1)
D4:D25	D4	=B4-C4

 

[kvsrinivasamurthy]

In G2 , copy down.

=IFERROR(1/(1/MIN(SUM(F2),100,100-SUMPRODUCT($G$1:$G1*(YEAR($C$1:$C1)=YEAR($C2))))),"")

If you want avoid display of 0, format cells.
Custom --> 0.00;;

01-01-2021​			191.64​	100.00​
31-05-2021​			170.39​
28-10-2021​			18.36​
27-03-2022​			37.74​	37.74​
24-08-2022​			85.48​	62.26​
21-01-2023​
20-06-2023​
17-11-2023​			925.25​	100.00​
15-04-2024​

 

[JohnZ1156]

Thanks, StephenCrump,
I was trying to come up with a formula to calculate the deductible, then figure out the reimbursement.
I never thought of calculating the reimbursement to get the deductible.