Instructions

Save the Canvas data as an excel sheet (comes as .csv naturally)

Separate out the program Canvas outcomes by program data if necessary

Set the first row in the data sheet as filtered

Create a Condensed Sheet for the individual course data (which goes in to the faculty reflection forms and the appendices for the annual assessment reports)

Create a Student Sheet for the student counts per PLO (which goes into the assessment reports)

For the Condensed Sheet, the relevant columns are: Assessment Name, Learning Outcome Name, Section Name, Llearning Outcome Rating Points, and Enrollment State

For the Student Sheet, the relevant columns are: Student SIS ID, Learning Outcome Name), Section Name, Learning Outcome Rating Points), and Enrollment State

Note: For the following sheet instructions, the columns are based on the Mock Data. Always ensure that your columns match the names listed above, not the column letters in the examples

Condensed Sheet

The format for these tables can be found in the example 'Mock Condensed' sheet of this excel workbook - it is recommended to copy/paste that format for consistency.

At the top of the table, replace 'PLO 01' with the first PLO being analyzed as it appears in the Data sheet

For each assignment (listed below the appropriate PLO), list each section name (column P) applicable - use the filters in the Data sheet to ensure all sections are listed

For the table, there is an example formula included for each cell in the Mock Condensed Sheet, but to break them down --

Section-Specific Rows [Formula: =COUNTIFS('VARIABLE Data'!$W:$W,3,'VARIABLE Data'!$B:$B,'VARIABLE Condensed'!$A$[VARIABLE - appropriate assessment name],'VARIABLE Data'!$G:$G,'VARIABLE Condensed'!$A$1,'VARIABLE Data'!$P:$P,'VARIABLE Condensed'!$B[VARIABLE - appropriate section name],'Mock Data'!$Z:$Z,"<>inactive")]

Operation: COUNTIFS

Assessment Name (column B) to limit the count to only that specific assessment

Learning Outcome Name (column G) to limit the count further to only that learning outcome - some assessments may cover multiple PLOs

Section Name (column P) to separate the data by sections

Learning Outcome Rating Points (column W) to reflect the appropriate column (3 for exceeds, 2 for meets, 1 for below, 0 for does not meet)

Enrollment State (column Z) to ensure that the data only looks at students who are enrolled properly

Compiled n columns

Operation: SUM

Each section-specific n column

% columns

TOTAL Row

Operation: SUM

Each n column cell

% columns

For each assessment, create a new table under the appropriate PLO heading

Student Sheet

The format for these tables can be found in the example 'Mock Student' sheet of this excel workbook - it is recommended to copy/paste that format for consistency.

In Cell A1, insert the formula (replacing the variables): =UNIQUE('VARIABLE Data'!G:G) - this will populate a list of all used PLOs in the data set

Below this list, insert the formula (replacing the variables): =UNIQUE('VARIABLE Data'!A:A) - this will populate a list of each unique Student SIS ID #

Create a Table based on PLOs extending from the Student UNIQUE list (example available in 'Mock Student' sheet)

Table Criteria

Create a column for each PLO listed in the PLO UNIQUE list

PLO Criteria

Create a column for each criteria listed in the PLO UNIQUE list and create a PLO column for each overarching PLO associated

Some programs use criteria while others do not. Refer to the PLO rubrics/learning outcomes documentation to confirm whether or not criteria are present

When creating the table, highlight the first row (with the PLO numbers) and down to the final student row - select 'my table has headings', and once the table is created, remove the filters

In the first PLO column for the first student ID #, insert the following formula (replacing the variables): =AVERAGEIFS('VARIABLE Data'!$W:$W,'VARIABLE Data'!$A:$A,'VARIABLE Student'!$A[VARIABLE - first student number],'VARIABLE Data'!$G:$G,'VARIABLE Student'!$A$[VARIABLE - PLO matching the column],'VARIABLE Data'!$Z:$Z,"<>inactive")

The breakdown for the pieces in this formula:

Student SIS ID (column A) to ensure each student is counted only once while averaging each score

Learning Outcome Name (column G) to ensure that only that learning outcome is included - some assessments may cover multiple PLOs

Learning Outcome Rating Points (column W) to average the numbers applied to the appropriate PLO

Enrollment State (column Z) to ensure that the data only looks at students who are enrolled properly

Repeat this formula, replacing variables as appropriate, for each PLO for each student

If there are criteria present: Use the above formula for each criteria cell and use the following formula for the overarching PLO columns: =AVERAGEIF(Table[VARIABLE][@[VARIABLE]:[VARIABLE]],"<>#DIV/0!")

Essentially, this formula averages the scores for each criteria - currently, we do not include weighted scoring, so this is a flat average.

An example of a completed table (without criteria) is present on the 'Mock Student' sheet

Finally, insert a copy of the results table (located in the Mock Student sheet) and adjust the table and formulas to reflect the PLOs and assessment scores.

For averages, .5 and higher rounds up; ex, 2.5 counts as a 3

If this table was copied, the percentages and totals should calculate automatically. If not, follow the same pattern as the course table from Mock Condensed instructions