Help with Calculating Curriculum Average considering multiple criteria

Zach C

Goal = calculate the total Curriculum Average % (for respective Regions 1, 3, 4 & 6) by combining the available quiz scores (in green) from all COURSES 2002-2008 (in the "Course" column). I want to filter out any rows that have blanks, 0% and Registered, aren't green (all non-green rows basically show the teammate hasn't started the respective quiz).

In other words, I need to account for each of the following criteria:

1. Region (I want to see what the Curriculum Average % is by each Region)
2. Each "COURSE" 2002-2008 (all 7 must be included in the rollup)
3. 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!

AravindGP

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.

Zach C

@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?

AravindGP

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 06-COURSE2000, Region 06-COURSE2002, etc.

You can then use the Average If formula to get the Quiz scores.

=AVERAGEIF({Helper Column reference}, "Region 06-COURSE2000", {Quiz Scores column reference})

You will need replace the criteria to get averages for different regions and courses.