Automate Formula | Excel

Question

I have quite a complex formula issue that I am trying to fix with excel, not sure if it's possible.

Basically what I want to do is the following for my total treated patients by Product I want to do is:

Drug A: Jan-24 = Treated Patients by Product (current month, Jan in this case) - Drops off (Jan24) Feb-24 = Patients from Jan24 + Treated Patients by Product (from Feb) - Drops off (Feb24) March24 = Patients from Jan24 + Patients from Feb24 + Treated Patients by Product (from March) - Drops off (March)

For April24, I want to start over again as Jan, cause the treatments are only for 3 months. So basically, on April24 would be = Current Treated Patients (April) - Drops off (April) May24 = Patients from April 24 + Treated Patients by Product (from May) - Drops off (May24) June24 = Patients from April24 + Patients from May24 + Treated Patients by Product (from June) - Drops off (June)

And this every 3 months, and I would like to enter a formula on the cell for Jan24 and when I drag it it just automate this process, not sure if it's possible.

Please note that I have my data as shown on the picture. Please note that I am trying to place this formula on the C52

If someone has any idea on how to do it I will appreciate a lot! I have been struggling with these for the last two weeks

The best thing that I could do was this:

=OFFSET(D36,0,MAX(0,C55-2),1,1)-OFFSET(D49,0,MAX(0,C55-2),1,1)+OFFSET(D24,0,MAX(0,C55-2),1,1)

But only did it right for Feb24, for March24 was already not working

Answer 1

Reducing with MOD:

=LET(
    treated_patients_by_product, $C$34:$P$37,
    months, $C$33:$P$33,
    n_months, 3,
    treatment_failure_by_product_as_percentage, $C$40:$G$43,
    treatment_failure_for_year, LAMBDA(y,
        INDEX(treatment_failure_by_product_as_percentage, , XMATCH(y, $C$39:$G$39))
    ),
    inital_totals, EXPAND(0, ROWS(treated_patients_by_product), , 0),
    net_patients_for_month, LAMBDA(accumulated, year_month,
        HSTACK(
            accumulated,
            IF(
                MOD(MONTH(year_month), n_months) = 1,
                inital_totals,
                TAKE(accumulated, , -1)
            ) +
                INDEX(treated_patients_by_product, , XMATCH(year_month, months)) *
                    (1 - treatment_failure_for_year(YEAR(year_month)))
        )
    ),
    DROP(REDUCE(inital_totals, months, net_patients_for_month), , 1)
)