Average times by month

Office of the Medical Director

Hi. I am working on creating metrics for my dashboard, but I'm unable to figure the correct formula for collecting the average call times per month, with the month being pulled based on a date column.

All of the data is in another sheet called OHCA Form including the date and the calculated time difference per incident in that same OHCA Form sheet.

For the row called "Time from 911 call to CPR instructions given", I have this formula in the January cell:

=AVG(COLLECT ({OHCA Form - Time CPR instructions started}, {OHCA Form - Incident Date}, MONTH(@cell), =January$1))

Where I want the OHCA Form - Time CPR instructions started averaged if the incident date is January; however, I receive an #unparseable error

What am I doing wrong? Any ideas would be greatly appreciated!!

Thanks,

Adrienne

Mike TV

@Office of the Medical Director

Have you tried switching from AVG(COLLECT to an AVERAGEIF formula? Since you only have one range to average and one range with a condition, I think it will work for you.

Office of the Medical Director

@Miketv

Thank you for the thought, but unfortunately I still receive #unparseable error

=AVERAGEIF({OHCA Form - Time CPR instructions started}, {OHCA Form - Incident Date}, MONTH(@cell), =January$1))

Mike TV

@Office of the Medical Director

When writing formulas, make sure to always pay attention to the tooltip.

The AVERAGEIF formula only allows for a range, criteria, and average range. I'm not sure if this will work on your sheet but it should be formatted something like this:

=AVERAGEIF({OHCA Form - Incident Date}, MONTH(@cell)=January$1, {OHCA Form - Time CPR instructions started})

Office of the Medical Director

@Miketv

Disregard... the averageIF DID work. I had to change a little more of the formula:

=AVERAGEIF({OHCA Form - Incident Date}, MONTH(@cell) = January$1, {OHCA Form - Time CPR instructions started})

Thank you!

Adrienne

Mike TV

@Office of the Medical Director

Cool. You came up with the same thing I did. That means you're learning it!