SUMIFS with difference?

[virtuosok]

Hi all,
I have my data in the following format (clip attached), 1000 rows.
It is about patients in treatment: they all started treatment on different dates noted in column Q and then either completed treatment in full (column S) or dropped early (column R).
I have a feeling that patients tend to drop earlier with time, i.e. back in 2021 they were more compliant with treatment and tended to stay longer.
Looking for a formula that will tell me an average treatment duration for patients discontinuing treatment early in each specific month. In other words, if in below clip there were 2 patients who discontinued treatment early within the month of Mar 22, I need the formula that does the following for Mar 22: ((R5-Q5)+(R9-Q9))/2
So, in this particular case, I'll see that for all those patients who discontinued during the month of Mar 22, their average duration of treatment before discontinuation was (318+315)/2=316.5 days
I need this for all months from Mar 21 till now (and onwards). Some months have zero patients dropping the treatment and other months have dozens.
Thank you

 

[sleek12]

To calculate the average treatment duration for patients discontinuing early in each month, you can use the following steps:

1. Create a new column in your spreadsheet to calculate the number of days between the start of treatment (column Q) and discontinuation (column R). You can do this by using the formula =Rx - Qx, where "x" is the row number.
2. Create another new column to categorize each row by the month and year in which treatment was discontinued. You can do this by using the formula =TEXT(Rx, "MMM YY"). This formula will return a string in the format "MMM YY", where "MMM" is the abbreviation of the month (e.g. Jan, Feb, Mar, etc.) and "YY" is the year.
3. Sort the data by the new column created in step 2, so that all the rows with the same month and year are grouped together.
4. Use the AVERAGEIF function to calculate the average treatment duration for each month and year. The syntax for this function is: =AVERAGEIF(range, criteria, average_range)

Where:

• "range" is the range of cells containing the dates in which treatment was discontinued (created in step 2)
• "criteria" is the month and year you want to calculate the average for (e.g. "Mar 21")
• "average_range" is the range of cells containing the number of days between the start of treatment and discontinuation (created in step 1)

For example, to calculate the average treatment duration for patients who discontinued in March 2021, you would use the formula: =AVERAGEIF(C2:C1000, "Mar 21", D2:D1000)

1. Repeat step 4 for each month and year you want to calculate the average for. You can copy and paste the formula, changing only the "criteria" argument to match the desired month and year.

Note: You may need to adjust the ranges and row numbers in the formulas to match your specific data.

 

[virtuosok]

Thanks! I was looking for one clean formula, but helper columns works just as well in this case. Implemented a bit modified solution, but thanks for guidance in the right direction!