Tracking Changes in Scores Over Time

Courtney Coules

We track changes in PHQ-9 and GAD-7 scores for patients who have a certain number of collaborative care appointments. I want to show an aggregate widget on the dashboard for all active and relapse prevention patients, which would mean calculating averages based on each session score for multiple patients. I am having trouble creating a formula that will calculate this and also want this to be automatic so it is complex. Anyone have any ideas for sheet summary formulas?

Genevieve P.

Hi @Courtney Coules

You can use an AVERAGEIF function to average a specific column IF another column has a criteria.

For example, you note that you need the average if the patients are either Active or Relapse Prevention. In this case, the criteria for the Range will need to have an OR to identify both scenarios.

Try this:

=AVERAGEIF([Treatment Status]:[Treatment Status], OR(@cell = "Active", @cell = "Relapse Prevention"), [PHQ-9 Score]:[PHQ-9 Score])

For your other average, just swap out the column referenced at the end of the formula:

=AVERAGEIF([Treatment Status]:[Treatment Status], OR(@cell = "Active", @cell = "Relapse Prevention"), [GAD-7 Score]:[GAD-7 Score])

If you have more than one criteria to filter down what you want to Average (ex. the Type of Contact or something), then you would use an AVG(COLLECT formula to specify the columns and criteria, like so:

=AVG(COLLECT([Column to Avg]:[Column to Avg], [Column 1]:[Column 1], "Criteria 1", [Column 2]:[Column 2], "Criteria 2"... etc

Let me know if this has helped or if I've misunderstood what you're looking to do.

Cheers,

Genevieve

Courtney Coules

@Genevieve P.

I think the hard part is this is intended to track patient's over time including new patients. Each type of patient has a set number of 15 sessions and they have scores per session. My goal is to track average scores for all patients per session number. So all session one average scores for all patients, then session two average scores for all patients, etc. Does this make sense? I want this process to be as automated as possible but have been hitting a wall on how to accomplish this, especially as new patients are added.

Genevieve P.

Hi @Courtney Coules

Thank you for this additional information! As long as you have a column that tracks what session is currently in the sheet, then this is no problem. You'd use my second example of an AVG(COLLECT to identify your two criteria, like so:

=AVG(COLLECT([PHQ-9 Score]:[PHQ-9 Score], [Treatment Status]:[Treatment Status], OR(@cell = "Active", @cell = "Relapse Prevention"), [Follow-Up Contact Number]:[Follow-Up Contact Number], 1)

This will AVG the column [PHQ-9 Score]

But only if:

[Treatment Status] is "Active" OR "Relapse Prevention"

AND if

[Follow-Up Contact Number] is 1

You'll just need to change out the number you're looking for, for each AVG:

=AVG(COLLECT([PHQ-9 Score]:[PHQ-9 Score], [Treatment Status]:[Treatment Status], OR(@cell = "Active", @cell = "Relapse Prevention"), [Follow-Up Contact Number]:[Follow-Up Contact Number], 2)

etc

Does this make sense?

Courtney Coules

@Genevieve P.

Seems to be working, thank you so much!!

Genevieve P.

No problem at all! 🙂