How to calculate averages using cross sheet formulas, considering criterion from multiple columns
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!
Best Answer
-
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!
Answers
-
Try an AVG/COLLECT combo.
=AVG(COLLECT({Range to average}, {1st criteria range}, 1st criteria, {2nd criteria range}, 2nd criteria, ............))
-
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!
-
@Nick Korna
Thank you so much! This is what I needed. I am back on the road. I appreciate you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!