How to calculate averages using cross sheet formulas, considering criterion from multiple columns

beckesa

I set up a metric sheet and would like to calculate averages for many different categories in a sheet. I am on the struggle bus to nowhere in my attempt to get a formula that will work. Specifically, I cannot seem to extract the numerical value from a field that also contains text, and I cannot find a way to define multiple criteria (averageifs).

Here is more info:

1.     Each category in the metric sheet (i.e. Visit Check) should average values from two columns in the source sheet (i.e., Visit Check 1 and Visit Check 2).

a.      The value from each category that needs to be averaged is mixed with text (i.e. "0 N/A, "3 Meets Standard", "2 Needs Improvement - Minor", "1 Needs Improvement - Major")

b.     “0 N/A” value should not be included in the average calculation

2.     Additionally, I want to factor in the Job Title and IDA Type columns from the source sheet and create separate metrics for each category (i.e., Average score for Visit Check for an RN, Annual IDA).

Source Sheet

Metric Sheet (arrow points to what I described in 2. above)

Thank you for any advice you have!

Nick Korna

Hi @beckesa,

I'd suggest adding a field with the average in your source sheet:

=IFERROR((VALUE(LEFT([Visit Check 1]@row, 1)) + VALUE(LEFT([Visit Check 2]@row, 1))) / (COUNTIF([Visit Check 1]@row, VALUE(LEFT(@cell, 1)) > 0) + COUNTIF([Visit Check 2]@row, VALUE(LEFT(@cell, 1)) > 0)), "")

This extracts the number from the visit checks (0-3) and then divides it by the number of non-zero values in those columns (i.e. if you have 1 visit classed 0, it will show the result from the other and if both it will be blank).

You can then use an AVG & COLLECT to get the values across with the additional criteria in your Metric sheet:

=AVG(COLLECT({Average Column}, {Job Title}, [Primary Column]@row, {IDA Type}, [Column 24]@row))

You will either need to add the IDA Types to each row in your Metric sheet as it won't be able to handle the blanks on the collect or sub out the [Column 24]@row reference for the relevant text inside " ".

If required, you can add a date column reference into this as well.

Hope this helps, but if I've misunderstood anything or you've any problems/questions then let us know!

Paul Newcome

Try an AVG/COLLECT combo.

=AVG(COLLECT({Range to average}, {1st criteria range}, 1st criteria, {2nd criteria range}, 2nd criteria, ............))

Nick Korna

Hi @beckesa,

I'd suggest adding a field with the average in your source sheet:

=IFERROR((VALUE(LEFT([Visit Check 1]@row, 1)) + VALUE(LEFT([Visit Check 2]@row, 1))) / (COUNTIF([Visit Check 1]@row, VALUE(LEFT(@cell, 1)) > 0) + COUNTIF([Visit Check 2]@row, VALUE(LEFT(@cell, 1)) > 0)), "")

This extracts the number from the visit checks (0-3) and then divides it by the number of non-zero values in those columns (i.e. if you have 1 visit classed 0, it will show the result from the other and if both it will be blank).

You can then use an AVG & COLLECT to get the values across with the additional criteria in your Metric sheet:

=AVG(COLLECT({Average Column}, {Job Title}, [Primary Column]@row, {IDA Type}, [Column 24]@row))

You will either need to add the IDA Types to each row in your Metric sheet as it won't be able to handle the blanks on the collect or sub out the [Column 24]@row reference for the relevant text inside " ".

If required, you can add a date column reference into this as well.

Hope this helps, but if I've misunderstood anything or you've any problems/questions then let us know!

beckesa

@Nick Korna

Thank you so much! This is what I needed. I am back on the road. I appreciate you!