Data Analysis: identifying Gaps
Perform a gap analysis of your Custom Report open your report in Excel, based on the data from your Custom Report, determine students overall performance level by utilizing the Conditional Formatting feature within Excel. For this exercise 70% is the basis of proficiency.
1. Conduct the calculation to identify the bottom 30% (Calculation example: Total points possible x(times) 30% (.30) = (equals) total we identify in the
Conditional Format formula {100 x .30 = 30}. In this example it is 30.
2. Open your Custom Report in Excel.
3. Conditional Format your data. Use the following condition to establish your formatting.
-Condition 1: Cell Value is -Less than or Equal to ____ (use calculation as described above)
-Format: Choose a color to highlight the bottom 30%. (Advanced, Proficient, Partially Proficient, Not Proficient).
4. Conduct the calculation to identify the top 30% (Calculation example: Total points possible x(times) 70% (.70) = (equals) total we identify in the
Conditional Format formula {100 x .70 = 70}. In this example it is 70.
3. Repeat step 3, but substitute the the calculation identifying the top 30%.
Conditional Format your data. Use the following condition to establish your formatting.
-Condition 1: Cell Value is
-Greater than or Equal to ____ (use calculation as described above)
-Format: Choose a color to highlight the top 30%. (Advanced, Proficient, Partially Proficient, Not Proficient).
NOTE: The video below "Excel 2010: Conditional Formatting" developed by GCF Learfree.org will guide you through Conditional Formatting.
1. Conduct the calculation to identify the bottom 30% (Calculation example: Total points possible x(times) 30% (.30) = (equals) total we identify in the
Conditional Format formula {100 x .30 = 30}. In this example it is 30.
2. Open your Custom Report in Excel.
3. Conditional Format your data. Use the following condition to establish your formatting.
-Condition 1: Cell Value is -Less than or Equal to ____ (use calculation as described above)
-Format: Choose a color to highlight the bottom 30%. (Advanced, Proficient, Partially Proficient, Not Proficient).
4. Conduct the calculation to identify the top 30% (Calculation example: Total points possible x(times) 70% (.70) = (equals) total we identify in the
Conditional Format formula {100 x .70 = 70}. In this example it is 70.
3. Repeat step 3, but substitute the the calculation identifying the top 30%.
Conditional Format your data. Use the following condition to establish your formatting.
-Condition 1: Cell Value is
-Greater than or Equal to ____ (use calculation as described above)
-Format: Choose a color to highlight the top 30%. (Advanced, Proficient, Partially Proficient, Not Proficient).
NOTE: The video below "Excel 2010: Conditional Formatting" developed by GCF Learfree.org will guide you through Conditional Formatting.
4. Sort your data from Largest to Smallest to visually represent the top 30% of your students and the bottom 30% of your students, identifying the gap.
NOTE: The video below "Excel 2010: Sorting" developed by GCF Learfree.org will guide you through Sorting.
NOTE: The video below "Excel 2010: Sorting" developed by GCF Learfree.org will guide you through Sorting.
Your page should look similar to the image below. Notice you can sort each cell one by one in order to identify gaps in each column of data to further analyze your data.