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
3
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)
)
1
See if this is the sort of thing you’re looking for. The summation restarts after three months.
=LET(startCol,COLUMN($C:$C),startIndex,QUOTIENT(COLUMN()-startCol,3)*3+1,SUM(INDEX($C34:$N34,startIndex):C34)-SUM(INDEX($C46:$N46,startIndex):C46))
One thing I’m not sure of is that it looks like the treatment failure % applies to the whole of 2024. That’s what I’ve assumed but it’s not clear from your screenshot.
1