Help with Calculating Curriculum Average considering multiple criteria
Goal = calculate the total Curriculum Average % (for respective Regions 1, 3, 4 & 6) by combining the available quiz scores (in green) from all COURSES 20022008 (in the "Course" column). I want to filter out any rows that have blanks, 0% and Registered, aren't green (all nongreen rows basically show the teammate hasn't started the respective quiz).
In other words, I need to account for each of the following criteria:
 Region (I want to see what the Curriculum Average % is by each Region)
 Each "COURSE" 20022008 (all 7 must be included in the rollup)
 Has "Completed" & Quiz Score % (I don't want the blanks to count as 0% or the formula to include any "Registered"/0% because that will bring down the Average)
I've tried AVERAGEIF and AVERAGE(COUNT) but not getting any luck, just "UNPARSEABLE".
Asking for help on 1) which formula to use and 2) how to group these conditions/criteria together.
Thanks!
Answers

Hi @Zach C
Since you're trying to get the quiz scores, which is represented as a percentage. The best formula would be AVERAGEIF. The only issue though is that you can have only one criterion. You can create a helper column with formulas to get the different combinations of Region, Course, and Completion. Once you do, you can then use the AVERAGEIF formula to get the average results across the different parameters.
Thanks,
Aravind GP Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com

@AravindGP I added a Curriculum Average row (blue row in the screenshot) to help roll the data up before I run an AVG(COLLECT formula from my master data sheet. I've covered up teammate information for privacy.
Currently, I am referencing the respective Region & COURSE2000 Curriculum Average in the formula below. There are COURSE2000 Curriculum Average rows with blanks and zeros, and I'm trying to exclude zeros from the formula with the following equation:
=AVG(COLLECT({Data Range 4}, {Data Range 4}, >0, {Data Range 1}, "Region 06", {Data Range 2}, "COURSE2000 Curriculum Average"))
Can you help me correct this formula?

Hi @Zach C
I believe you've added the blue line manually. Instead, try adding a column in your sheet with a formula like the one below to get the parameters for your calculation.
=IF([Completion Status]@row = "Completed", [Region]@row + "" + [Course]@row, "")
This formula will give you a value as Region 06COURSE2000, Region 06COURSE2002, etc.
You can then use the Average If formula to get the Quiz scores.
=AVERAGEIF({Helper Column reference}, "Region 06COURSE2000", {Quiz Scores column reference})
You will need replace the criteria to get averages for different regions and courses.
Thanks,
Aravind GP Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 218 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!