Some of our students are arriving on campus with Microsoft's new operating system, Vista, and Office 2007 installed. Our campus is not going to upgrade our faculty/staff/classroom computers to these until next summer. Until that time, we will have some overlap when our computers use the older version of Office and some students will have the newer version.
The problem is that files created by Word, Excel, PowerPoint, etc. in 2007 can NOT be opened by those programs in Office 2000 or 2003.
Please take the following two steps to address this problem with your students:
1. Install the Microsoft Office Compatibility Pack on your computer. This is an update for Microsoft Office 2000 and 2003 that allows those programs to open Office 2007 files. On Microsoft's website you can find instructions to install this update and the file you need. I highly recommend you do this for any computers you may use to access student work. If you are not comfortable doing this with a WCU computer, feel free to contact Colby or me. NOTE: This fix only works for Windows users - there is no fix for Mac users of Office.
2. Ask your students using Office 2007 to save their work as an Office 2003 file. To ease the transition, students using Office 2007 can save files in the Office 2003 format and submit these electronically. Feel free to send students using Office 2007 to this page that describes how to set their copy of Word to save to Office 2003 format by default. This will also work in Excel and PowerPoint.
As always, feel free to contact me if you have any questions.
Showing posts with label Excel. Show all posts
Showing posts with label Excel. Show all posts
Wednesday, August 22, 2007
Monday, May 28, 2007
Tech Tip #9 - 10 Excel Skills You Need
I discovered this page recently: Become an Excel ninja
It's not for the very beginner in Excel, but if you have successfully gone through the Gradebook Tech Tips you are likely ready for it. The page has an excellent list of features in Excel that can either save you time or accomplish tasks you didn't know you could accomplish (in Excel).
The author also has the page in an easy to download PDF version.
It's not for the very beginner in Excel, but if you have successfully gone through the Gradebook Tech Tips you are likely ready for it. The page has an excellent list of features in Excel that can either save you time or accomplish tasks you didn't know you could accomplish (in Excel).
The author also has the page in an easy to download PDF version.
Compilation of the Gradebook Tech Tips
This post is intended to list all the Tech Tips in the series on the Gradebook so you have a convenient place to reference them, bookmark them, and share them with others.
Part 1 - Formatting a Gradebook
Part 2 - Some Grade Formulas (e.g., drop the lowest grade)
Part 2a - More Detail on Grade Formulas
Part 3 - Calculating Final Grades and Assigning Letter Grades
Part 4 - Calculating Statistics on Assignments
Part 5 - Printing the Gradebook
Part 1 - Formatting a Gradebook
Part 2 - Some Grade Formulas (e.g., drop the lowest grade)
Part 2a - More Detail on Grade Formulas
Part 3 - Calculating Final Grades and Assigning Letter Grades
Part 4 - Calculating Statistics on Assignments
Part 5 - Printing the Gradebook
Thursday, May 24, 2007
Tech Tip #8 - Making a Gradebook in Excel, Part 5
In our fifth and final Tech Tip on the Gradebook, we'll cover some tips on how to print out a spreadsheet.
While there are many good reasons to go to a paperless system, sometimes you just need to print out a spreadsheet. Let's look at a few aspects of printing a spreadsheet and how to format them to come out in a decent way.
Let's assume we would like to print out the whole gradebook. To get a quick idea of what it will look like on paper if we do nothing to the spreadsheet, you can use "Print Preview." Click on the Print Preview button in the toolbar (or find the Print Preview item under the File menu).
When you do this, you will get a view of your gradebook that very likely will not work for you. Typically with a gradebook, you will have too many columns to fit the gradebook on a single page. Also, the helpful grid lines that let you line up grades and students are gone.
Let's do a few things to try to get this spreadsheet to fit on a single page. First, let's make the rows and columns as small as possible. This will shrink the horizontal width of what you're trying to print to make it fit on a page better. If you're in Print Preview mode, click the "Close" button to go back to the spreadsheet. Select one or more columns that you would like to change in width.
Then, as covered in Tech Tip #3, shrink the width of these columns.
You can see in the above screen shots how much we were able to shrink the horizontal width. Shrink all the columns as much as you can and still be able to read the numbers.
Once you've done that, we can quickly tell if we've shrunk the columns enough to fit on a page by looking at the page boundary lines Excel puts in the spreadsheet. You may notice in the picture below the dashed line between columns N and O (the ones for "Project" and "Final Grade"). That is the page boundary - basically anything to the left of it will appear on one page and anything to the right will appear on another. If you can shrink your columns so everything fits to the left of the page boundary line, it should all fit on a single page. NOTE: the page boundary line only appears AFTER you have looked at Print Preview at least once.
In our case, we did not get all the grades on a single page, so let's try the next tactic: switch to Landscape mode. This will print the gradebook sideways on a page, giving us more horizontal width at the expense of vertical length. To switch between portrait (default) and landscape, go to the menu File-Page Setup. Also, if you are in Print Preview mode, you can click the "Setup" button. On this new window, click the circle beside Landscape and click "OK."
If you are in Print Preview mode, you will see the page change and how your spreadsheet will look. If you are just looking at the spreadsheet normally, you will see the page boundary lines move to their new locations with the change in the size of the page.
Typically, the combination of these two will get your spreadsheet to fit. If it still does not, there are two more options. Go to Print Preview mode and click the "Setup" button. You see (as in the screen shot above) a header "Scaling." You can manually select a smaller percentage after "Adjust to:" to make the spreadsheet smaller (like zooming). Or, you can have Excel do the work in the next option and tell it to fit the spreadsheet however it can into the number of pages you select high and tall. Try a few combinations to see what makes sense for you.
With these tools you should be able to fit your gradebook on a small number of pages. However, it may look like the below example, with no grid lines to help guide your eye as you look at student grades.
Let's make this easier to read by putting some grid lines on here. Select the cells where you want to put in grid lines. You likely do not want to select all the cells in the gradebook, but only those that contain headers and grades. One example is below.
Notice that I did not select the cell with the word "Homework" in it. Since that's a major heading for several columns, I won't want it to appear like it only applies to a single cell or two. There are multiple ways to put gridlines around cells - I will show one here. Once you have selected the cells you want, put the mouse cursor somewhere in the selection and right click to bring up the menu. Select the "Format Cells" item.
There will be several tabs on the window that comes up. Select the "Border" tab.
The simplest thing to do is just click the "Outline" and "Inside" buttons to add a simple border around all the selected cells. You can get fancy and add multiple line thicknesses, colors, styles, or only put borders on some of the sides of a cell. You can experiment on your own. When you're satisfied, click OK. You will now see a black border around the cells you selected.
If you switch to Print Preview, you will see that this makes a significant difference in readability for those cells.
In this example, I only gave a border to the homework grades for space reasons. You can select cells for all your grades and give them a border.
Well, that wraps up the Tech Tips on creating a Gradebook in Excel. I hope that was useful AND helped you learn some things about Excel you may not have known. If you have any questions, don't hesitate to contact me or leave a comment here.
While there are many good reasons to go to a paperless system, sometimes you just need to print out a spreadsheet. Let's look at a few aspects of printing a spreadsheet and how to format them to come out in a decent way.
Let's assume we would like to print out the whole gradebook. To get a quick idea of what it will look like on paper if we do nothing to the spreadsheet, you can use "Print Preview." Click on the Print Preview button in the toolbar (or find the Print Preview item under the File menu).
When you do this, you will get a view of your gradebook that very likely will not work for you. Typically with a gradebook, you will have too many columns to fit the gradebook on a single page. Also, the helpful grid lines that let you line up grades and students are gone.
Let's do a few things to try to get this spreadsheet to fit on a single page. First, let's make the rows and columns as small as possible. This will shrink the horizontal width of what you're trying to print to make it fit on a page better. If you're in Print Preview mode, click the "Close" button to go back to the spreadsheet. Select one or more columns that you would like to change in width.
Then, as covered in Tech Tip #3, shrink the width of these columns.
You can see in the above screen shots how much we were able to shrink the horizontal width. Shrink all the columns as much as you can and still be able to read the numbers.
Once you've done that, we can quickly tell if we've shrunk the columns enough to fit on a page by looking at the page boundary lines Excel puts in the spreadsheet. You may notice in the picture below the dashed line between columns N and O (the ones for "Project" and "Final Grade"). That is the page boundary - basically anything to the left of it will appear on one page and anything to the right will appear on another. If you can shrink your columns so everything fits to the left of the page boundary line, it should all fit on a single page. NOTE: the page boundary line only appears AFTER you have looked at Print Preview at least once.
In our case, we did not get all the grades on a single page, so let's try the next tactic: switch to Landscape mode. This will print the gradebook sideways on a page, giving us more horizontal width at the expense of vertical length. To switch between portrait (default) and landscape, go to the menu File-Page Setup. Also, if you are in Print Preview mode, you can click the "Setup" button. On this new window, click the circle beside Landscape and click "OK."
If you are in Print Preview mode, you will see the page change and how your spreadsheet will look. If you are just looking at the spreadsheet normally, you will see the page boundary lines move to their new locations with the change in the size of the page.
Typically, the combination of these two will get your spreadsheet to fit. If it still does not, there are two more options. Go to Print Preview mode and click the "Setup" button. You see (as in the screen shot above) a header "Scaling." You can manually select a smaller percentage after "Adjust to:" to make the spreadsheet smaller (like zooming). Or, you can have Excel do the work in the next option and tell it to fit the spreadsheet however it can into the number of pages you select high and tall. Try a few combinations to see what makes sense for you.
With these tools you should be able to fit your gradebook on a small number of pages. However, it may look like the below example, with no grid lines to help guide your eye as you look at student grades.
Let's make this easier to read by putting some grid lines on here. Select the cells where you want to put in grid lines. You likely do not want to select all the cells in the gradebook, but only those that contain headers and grades. One example is below.
Notice that I did not select the cell with the word "Homework" in it. Since that's a major heading for several columns, I won't want it to appear like it only applies to a single cell or two. There are multiple ways to put gridlines around cells - I will show one here. Once you have selected the cells you want, put the mouse cursor somewhere in the selection and right click to bring up the menu. Select the "Format Cells" item.
There will be several tabs on the window that comes up. Select the "Border" tab.
The simplest thing to do is just click the "Outline" and "Inside" buttons to add a simple border around all the selected cells. You can get fancy and add multiple line thicknesses, colors, styles, or only put borders on some of the sides of a cell. You can experiment on your own. When you're satisfied, click OK. You will now see a black border around the cells you selected.
If you switch to Print Preview, you will see that this makes a significant difference in readability for those cells.
In this example, I only gave a border to the homework grades for space reasons. You can select cells for all your grades and give them a border.
Well, that wraps up the Tech Tips on creating a Gradebook in Excel. I hope that was useful AND helped you learn some things about Excel you may not have known. If you have any questions, don't hesitate to contact me or leave a comment here.
Tuesday, May 22, 2007
Tech Tip #7 - Making a Gradebook in Excel, Part 4
We've looked at the main things you can do to get a working gradebook for your course. In this Tech Tip, we'll cover how to calculate some interesting statistics on the grades for your information and/or to share with your class. While 45.7% of all statistics are made up and not useful (Yes, I just made that up), they can help you see trends in grades to help guide your teaching.
In the gradebook we've made so far, each row has grades for a single student, while each column holds the grade for an individual assignment. Let's calculate a few statistics on the homework grades. We'll start with averages, lowest scores, and highest scores. We've covered the first two before, so this should be review.
Let's first indicate where we are going to calculate these statistics. I typically go down to the last student in my list, skip two rows, then put in what statistic I am going to calculate.
The space is to separate the statistics from the students' grades so I don't confuse the numbers. I also make the text bold to make them stand out as different. Let's calculate the average, lowest, and highest values for Homework 1. First, select the cell in the row for "Average" and column for Homework 1. Then type
=average(
and select the homework 1 grades for all the students. Then type the close parenthesis and hit the "Enter" key. Before you hit Enter, you should see something like this:
Now, we can do the same thing for the Lowest value, except putting it in the row by "Lowest" and using the "Min" function rather than "Average." Your formula will look like this:
We can repeat the procedure again, this time in the "Highest" row using the "Max" function. Your formula will look like this:
You may notice the green triangle in the top left corner of each cell. This indicates that Excel thinks there is a potential error in what you did. To see what Excel thinks is wrong, click on one of the cells with the green triangle. You will see a square with an exclamation point appear to the left of the cell.
Slowly move your cursor over this box. It will turn an orange-ish color and another box with text may appear with the error message.
Click on the downward arrow that appears to see your options.
Basically, Excel doesn't like the extra rows of spacing we put in between the last student and where we're calculating the statistics. It thinks we probably wanted to select all cells down to right above where we are calculating the average. In this case, we want to "Ignore Error" since we don't want Excel to change anything. This is typically the safest option, but Excel may have a good suggestion every now and then so it is worth looking at the messages. You will notice that if we select "Ignore Error" then the green triangle disappears.
We can copy and paste these statistics to the other columns of the homework grades. Select the three cells with our statistics for Homework 1.
Then, Copy the cells, either by using the menu (Edit-Copy), or right clicking on the selected cells and selecting "Copy," or by using the Copy button in the toolbar.
At this point, there are a few different ways to copy this group of cells to the right places. I'm going to show you the one I tend to use. Select all the cells in the Average row for Homework grades 2 through 6.
You only need to select that first row of cells. Excel will realize that it should copy the cells for Lowest and Highest grades beneath. Then, Paste the cells, either by using the menu (Edit-Paste), or right clicking on the selected cells and selecting Paste, or by using the Paste button in the toolbar.
I'll go over a few more functions that might be useful for statistical analysis of grades. First, you can get the standard deviation using the "Stdev" function.
The first and third Quartiles can be calculated with the "Quartile" function. The Quartile function takes two inputs: the range of numbers you want to examine (as we've used with the functions above such as Average) and the number of the quartile. The following values can be used for this second input to the function:
0 = Lowest Value
1 = Quartile 1
2 = Median
3 = Quartile 3
4 = Maximum Value
As you can see, you could have used the Quartile function to calculate the minimum and maximum values and the median, but the functions specifically for those values are simpler to use and understand. To find the first quartile, use the formula as in the screenshot below:
Notice, most importantly, the comma and number 1 after the range of cells to examine. The formula for the third quartile is shown here, and you can see the use of the three as input to the formula to find that quartile.
As with the Average, Lowest, and Highest, we can copy the cells with these formulas to all the other grades to see their values.
Here is how our spreadsheet looks for the Homework grades:
Those statistics should get you started on examining your grades, and will conclude this Tech Tip. If there's a statistic you're interested in that is not covered here, see Tech Tip #2 for ways to look up functions in Excel - it may have it built-in for you.
In the gradebook we've made so far, each row has grades for a single student, while each column holds the grade for an individual assignment. Let's calculate a few statistics on the homework grades. We'll start with averages, lowest scores, and highest scores. We've covered the first two before, so this should be review.
Let's first indicate where we are going to calculate these statistics. I typically go down to the last student in my list, skip two rows, then put in what statistic I am going to calculate.
The space is to separate the statistics from the students' grades so I don't confuse the numbers. I also make the text bold to make them stand out as different. Let's calculate the average, lowest, and highest values for Homework 1. First, select the cell in the row for "Average" and column for Homework 1. Then type
=average(
and select the homework 1 grades for all the students. Then type the close parenthesis and hit the "Enter" key. Before you hit Enter, you should see something like this:
Now, we can do the same thing for the Lowest value, except putting it in the row by "Lowest" and using the "Min" function rather than "Average." Your formula will look like this:
We can repeat the procedure again, this time in the "Highest" row using the "Max" function. Your formula will look like this:
You may notice the green triangle in the top left corner of each cell. This indicates that Excel thinks there is a potential error in what you did. To see what Excel thinks is wrong, click on one of the cells with the green triangle. You will see a square with an exclamation point appear to the left of the cell.
Slowly move your cursor over this box. It will turn an orange-ish color and another box with text may appear with the error message.
Click on the downward arrow that appears to see your options.
Basically, Excel doesn't like the extra rows of spacing we put in between the last student and where we're calculating the statistics. It thinks we probably wanted to select all cells down to right above where we are calculating the average. In this case, we want to "Ignore Error" since we don't want Excel to change anything. This is typically the safest option, but Excel may have a good suggestion every now and then so it is worth looking at the messages. You will notice that if we select "Ignore Error" then the green triangle disappears.
We can copy and paste these statistics to the other columns of the homework grades. Select the three cells with our statistics for Homework 1.
Then, Copy the cells, either by using the menu (Edit-Copy), or right clicking on the selected cells and selecting "Copy," or by using the Copy button in the toolbar.
At this point, there are a few different ways to copy this group of cells to the right places. I'm going to show you the one I tend to use. Select all the cells in the Average row for Homework grades 2 through 6.
You only need to select that first row of cells. Excel will realize that it should copy the cells for Lowest and Highest grades beneath. Then, Paste the cells, either by using the menu (Edit-Paste), or right clicking on the selected cells and selecting Paste, or by using the Paste button in the toolbar.
I'll go over a few more functions that might be useful for statistical analysis of grades. First, you can get the standard deviation using the "Stdev" function.
The first and third Quartiles can be calculated with the "Quartile" function. The Quartile function takes two inputs: the range of numbers you want to examine (as we've used with the functions above such as Average) and the number of the quartile. The following values can be used for this second input to the function:
0 = Lowest Value
1 = Quartile 1
2 = Median
3 = Quartile 3
4 = Maximum Value
As you can see, you could have used the Quartile function to calculate the minimum and maximum values and the median, but the functions specifically for those values are simpler to use and understand. To find the first quartile, use the formula as in the screenshot below:
Notice, most importantly, the comma and number 1 after the range of cells to examine. The formula for the third quartile is shown here, and you can see the use of the three as input to the formula to find that quartile.
As with the Average, Lowest, and Highest, we can copy the cells with these formulas to all the other grades to see their values.
Here is how our spreadsheet looks for the Homework grades:
Those statistics should get you started on examining your grades, and will conclude this Tech Tip. If there's a statistic you're interested in that is not covered here, see Tech Tip #2 for ways to look up functions in Excel - it may have it built-in for you.
Tuesday, May 15, 2007
Tech Tip #6 – Making a Gradebook in Excel, Part 3
We continue creating a gradebook with this Tech Tip. This time, we will cover:
Now, let's assume that the final grade for the course follows this formula:
25% = Homework average
40% = Tests average
35% = Project grade
Since these percentages are not all equal, we can't just average these three numbers for each student. We also have the complication that the Homework average is a score out of 10 possible points, while the Test average and Project grade are scores out of 100 possible points. As with many problems like this, it may be best to write out a formula and THEN put it into Excel. We will use the following formula:
Final Grade = (Homework Avg. * 10 * 0.25) + (Tests average * 0.4) + (Project grade * 0.35)
As you see, we take each term and multiply it by its respective percentage, then add them together. Also, in the case of the Homework average, we multiply by 10 to convert it from a 10 point scale to a 100 point scale in the formula.
To do this in Excel, select the cell in the "Final Grade" column for the first student. Next, type
=(
Then click on the cell with the Homework Average for the first student. Then type
*10*0.25)+(
Next, click on the cell with the Text Average for the first student. Then type
*0.4)+(
then click on the cell with the Project grade for the first student. Then type
*0.35)
Your formula should look something like this
Now, press the "Enter" key. The final grade will appear for this student. You can copy and paste this formula for the other students to calculate their final grades. Our gradebook with final grades is below:
We'll cover one more topic: how to automatically assign letter grades in Excel. This involves the "Lookup" function and a few other features of Excel. First, I will Hide the columns with the Homework, Test, and Project grades to focus on the final grade. To do this, take the mouse and hold down the left mouse button over the column header - that is the grey/tan colored cell with the letter identifying the column. It will look something like the below picture - and the entire column will appear selected.
Then, drag the mouse over all the columns you wish to hide. In this case, we will hide all the grades except the Final Grade column.
Then, while the columns are selected, right-click on any of the selected cells to bring up a menu - select Hide from this menu.
This will Hide the selected cells. We'll look at how to Unhide them later in this Tech Tip.
Now, we need to set up the criteria to select between grades. I suggest you use a different worksheet for this. You see the tabs near the bottom of the screen. Right now we're working in "Sheet 1" but we have the other two available.
You can add or delete sheets from your spreadsheet as you wish, and reference information between them. You can also rename these sheets to something more logical. Right-click on the sheet name (e.g., "Sheet 1") and select Rename. Then, you can type what you want. I have renamed two tabs as seen below.
Click on a tab to switch to it. In the Letter Grades tab I've set up the following.
In the first column, I have typed a list of letter grades in ASCENDING order. The order is very important as this is how the LOOKUP function works. In the second column, I have typed the LOWEST grade that will qualify for the corresponding letter grade. This is also important as this is how the LOOKUP function works. These are only examples - type the numbers that you have selected for your course. The table above corresponds to the following scale:
A+: 97-infinity
A: 94-96.99
A-: 90-93.99
B+: 87-89.99
B: 84-86.99
B-: 80-83.99
etc.
To emphasize, there are two essential aspects of creating this table of values: they must be ASCENDING order and you must use the LOWEST value for each grade level.
With this table, we can use the Lookup function to assign a letter grade. The Lookup function takes three inputs. First, it looks at a particular value we want examined. In this case, it will be the "Final Grade." Then, it "looks up" that value from a range - in this case, the "Score" column in the "Letter Grades" sheet. Finally, we tell the Lookup function a corresponding range of values to the first range, from which to draw the value to place in the cell. In this case, that will be the "Letter Grade" column.
To implement the Lookup function here, first select the cell under the "Letter Grade" column on the sheet with the Student's Grades.
Then type
=Lookup(
Then select the cell under "Final Grade" for this student. Then, type a comma. Next, switch to the "Letter Grades" sheet by clicking on its tab at the bottom. Select the cells that represent the Lowest scores for each letter grade.
Type another comma. Now select the cells with the letter grades. When you have those selected, type the close parenthesis: ) and then hit the Enter key. You should now see the letter grade in the "Letter Grade" column for the student. If you need to double-check the formula, it should look something like this, though your cell references may be different.
You CAN NOT copy and paste this immediately for the other students. First, you have to "freeze" the references to the the columns in the "Letter Grades" sheet. If you do not do this, they will not be properly referenced. Hopefully, I will cover the reasons for this in a future Tech Tip - trust me for now.
To freeze these references, put "Dollar signs" in front of the column and row references in the formula. To edit a formula, click on the cell with the formula you want to edit, and it will appear in the Formula bar for you to edit (seen below) which is typically right above the spreadsheet.
Notice how there are now dollar signs before the letters and numbers of all cells referenced in the "Letter Grades" sheet. Do NOT freeze the cell of the Final Grade for the student.
Once you have done this (and double checked that it is similar to the formula above), you can copy and paste the cell in the Letter Grade column for all students.
To Unhide the hidden cells, select the columns on either side of the hidden cells, right-click to bring up the menu, and select Unhide.
And now, this post has become quite long enough. In our next "episode" we will look at calculating useful statistics on students' grades.
- Using a formula to calculate the final grade
- Automatically calculating and assigning a letter grade
Now, let's assume that the final grade for the course follows this formula:
25% = Homework average
40% = Tests average
35% = Project grade
Since these percentages are not all equal, we can't just average these three numbers for each student. We also have the complication that the Homework average is a score out of 10 possible points, while the Test average and Project grade are scores out of 100 possible points. As with many problems like this, it may be best to write out a formula and THEN put it into Excel. We will use the following formula:
Final Grade = (Homework Avg. * 10 * 0.25) + (Tests average * 0.4) + (Project grade * 0.35)
As you see, we take each term and multiply it by its respective percentage, then add them together. Also, in the case of the Homework average, we multiply by 10 to convert it from a 10 point scale to a 100 point scale in the formula.
To do this in Excel, select the cell in the "Final Grade" column for the first student. Next, type
=(
Then click on the cell with the Homework Average for the first student. Then type
*10*0.25)+(
Next, click on the cell with the Text Average for the first student. Then type
*0.4)+(
then click on the cell with the Project grade for the first student. Then type
*0.35)
Your formula should look something like this
Now, press the "Enter" key. The final grade will appear for this student. You can copy and paste this formula for the other students to calculate their final grades. Our gradebook with final grades is below:
We'll cover one more topic: how to automatically assign letter grades in Excel. This involves the "Lookup" function and a few other features of Excel. First, I will Hide the columns with the Homework, Test, and Project grades to focus on the final grade. To do this, take the mouse and hold down the left mouse button over the column header - that is the grey/tan colored cell with the letter identifying the column. It will look something like the below picture - and the entire column will appear selected.
Then, drag the mouse over all the columns you wish to hide. In this case, we will hide all the grades except the Final Grade column.
Then, while the columns are selected, right-click on any of the selected cells to bring up a menu - select Hide from this menu.
This will Hide the selected cells. We'll look at how to Unhide them later in this Tech Tip.
Now, we need to set up the criteria to select between grades. I suggest you use a different worksheet for this. You see the tabs near the bottom of the screen. Right now we're working in "Sheet 1" but we have the other two available.
You can add or delete sheets from your spreadsheet as you wish, and reference information between them. You can also rename these sheets to something more logical. Right-click on the sheet name (e.g., "Sheet 1") and select Rename. Then, you can type what you want. I have renamed two tabs as seen below.
Click on a tab to switch to it. In the Letter Grades tab I've set up the following.
In the first column, I have typed a list of letter grades in ASCENDING order. The order is very important as this is how the LOOKUP function works. In the second column, I have typed the LOWEST grade that will qualify for the corresponding letter grade. This is also important as this is how the LOOKUP function works. These are only examples - type the numbers that you have selected for your course. The table above corresponds to the following scale:
A+: 97-infinity
A: 94-96.99
A-: 90-93.99
B+: 87-89.99
B: 84-86.99
B-: 80-83.99
etc.
To emphasize, there are two essential aspects of creating this table of values: they must be ASCENDING order and you must use the LOWEST value for each grade level.
With this table, we can use the Lookup function to assign a letter grade. The Lookup function takes three inputs. First, it looks at a particular value we want examined. In this case, it will be the "Final Grade." Then, it "looks up" that value from a range - in this case, the "Score" column in the "Letter Grades" sheet. Finally, we tell the Lookup function a corresponding range of values to the first range, from which to draw the value to place in the cell. In this case, that will be the "Letter Grade" column.
To implement the Lookup function here, first select the cell under the "Letter Grade" column on the sheet with the Student's Grades.
Then type
=Lookup(
Then select the cell under "Final Grade" for this student. Then, type a comma. Next, switch to the "Letter Grades" sheet by clicking on its tab at the bottom. Select the cells that represent the Lowest scores for each letter grade.
Type another comma. Now select the cells with the letter grades. When you have those selected, type the close parenthesis: ) and then hit the Enter key. You should now see the letter grade in the "Letter Grade" column for the student. If you need to double-check the formula, it should look something like this, though your cell references may be different.
You CAN NOT copy and paste this immediately for the other students. First, you have to "freeze" the references to the the columns in the "Letter Grades" sheet. If you do not do this, they will not be properly referenced. Hopefully, I will cover the reasons for this in a future Tech Tip - trust me for now.
To freeze these references, put "Dollar signs" in front of the column and row references in the formula. To edit a formula, click on the cell with the formula you want to edit, and it will appear in the Formula bar for you to edit (seen below) which is typically right above the spreadsheet.
Notice how there are now dollar signs before the letters and numbers of all cells referenced in the "Letter Grades" sheet. Do NOT freeze the cell of the Final Grade for the student.
Once you have done this (and double checked that it is similar to the formula above), you can copy and paste the cell in the Letter Grade column for all students.
To Unhide the hidden cells, select the columns on either side of the hidden cells, right-click to bring up the menu, and select Unhide.
And now, this post has become quite long enough. In our next "episode" we will look at calculating useful statistics on students' grades.
Monday, May 14, 2007
Tech Tip #5 – Making a Gradebook in Excel, Part 2a
Let me revisit the very last thing I covered in the previous Tech Tip - dropping the lowest score. I worry that the explanation was spotty. Let me show the concept in a different way. I labeled some more columns as you can see in the screenshot below. Basically, I'm going to calculate each component of the "Average all but lowest score" formula in separate columns.
To calculate the sum of the homework, select the cell under the Sum column (as seen above), and type
=sum(
Next, take the mouse, move the cursor over the cell with the grade for Homework 1 for the first student and hold down the left button (rather than just click). Then, still holding down the left button, drag the mouse so the selected area (the cells inside the box with the flashing line) encloses all the homework grade cells for the first student, whether or not there is a grade there.
Then, type
)
to close the formula and press the "Enter" key. You now have the Sum of all the homework assignments for this student. You can use copy and paste to copy this formula for the other students (discussed in previous Tech Tips).
Now, we go through a similar procedure for the Lowest column to find the lowest homework score. Select the cell in the Lowest column for the first student. Type
=min(
This is the "Minimum Value" function. It identifies the minimum value of a set of numbers. As with the sum, select the cells containing the homework grades, then type ")" and then press "Enter."
You can then copy and paste that cell for the other students in that column. You can see the result here, where the "Lowest" column has the lowest score for the homework assignment.
As you might guess, the function to find the maximum value of a set of numbers is "max."
The next column, "Num of HW" is an abbreviation for Number of Homework Assignments." In this column, we follow the same procedure, except we use the Count function. The Count function simply gives you the number of cells that have a number in them. In this case, we would expect it to be six. Type
=count(
then again select the cells with the homework grades.
And, like the ones above, you can copy and paste this cell for the other students.
Now, the final column involves constructing a formula from the other cells. Again, we want the formula
(Sum of all grades - Lowest grade) / (Number of grades - 1)
So, select the cell under HW Average (that is, the homework average with the lowest grade dropped) and type
=(
then select the cell under the Sum column for the first student, then type the "minus" sign, then select the cell under the Lowest column for the first student. Then close parenthesis: ")" and your formula should look like this.
Notice that Excel puts a colored box around each cell, and that those colors match the cell references in the formula. The cell reference is the combination of the letter of the column and the number of the row. So, the cell with the lowest homework grade for the first student is in cell J4.
Next, type
/(
since we are dividing and moving to the term in the denominator. Now, select the cell under "Num of HW" for the first student, then type
-1)
to finish the formula. It should look like the one below.
Press "Enter" to finish. You can copy and paste this cell to the other students.
The main point of going through this was to demonstrate the use of multiple functions built in to Excel (such as Min, Count, and Sum) and how to create a formula (to calculate the final column's value from other columns). We will proceed with our gradebook example in the next installment.
To calculate the sum of the homework, select the cell under the Sum column (as seen above), and type
=sum(
Next, take the mouse, move the cursor over the cell with the grade for Homework 1 for the first student and hold down the left button (rather than just click). Then, still holding down the left button, drag the mouse so the selected area (the cells inside the box with the flashing line) encloses all the homework grade cells for the first student, whether or not there is a grade there.
Then, type
)
to close the formula and press the "Enter" key. You now have the Sum of all the homework assignments for this student. You can use copy and paste to copy this formula for the other students (discussed in previous Tech Tips).
Now, we go through a similar procedure for the Lowest column to find the lowest homework score. Select the cell in the Lowest column for the first student. Type
=min(
This is the "Minimum Value" function. It identifies the minimum value of a set of numbers. As with the sum, select the cells containing the homework grades, then type ")" and then press "Enter."
You can then copy and paste that cell for the other students in that column. You can see the result here, where the "Lowest" column has the lowest score for the homework assignment.
As you might guess, the function to find the maximum value of a set of numbers is "max."
The next column, "Num of HW" is an abbreviation for Number of Homework Assignments." In this column, we follow the same procedure, except we use the Count function. The Count function simply gives you the number of cells that have a number in them. In this case, we would expect it to be six. Type
=count(
then again select the cells with the homework grades.
And, like the ones above, you can copy and paste this cell for the other students.
Now, the final column involves constructing a formula from the other cells. Again, we want the formula
(Sum of all grades - Lowest grade) / (Number of grades - 1)
So, select the cell under HW Average (that is, the homework average with the lowest grade dropped) and type
=(
then select the cell under the Sum column for the first student, then type the "minus" sign, then select the cell under the Lowest column for the first student. Then close parenthesis: ")" and your formula should look like this.
Notice that Excel puts a colored box around each cell, and that those colors match the cell references in the formula. The cell reference is the combination of the letter of the column and the number of the row. So, the cell with the lowest homework grade for the first student is in cell J4.
Next, type
/(
since we are dividing and moving to the term in the denominator. Now, select the cell under "Num of HW" for the first student, then type
-1)
to finish the formula. It should look like the one below.
Press "Enter" to finish. You can copy and paste this cell to the other students.
The main point of going through this was to demonstrate the use of multiple functions built in to Excel (such as Min, Count, and Sum) and how to create a formula (to calculate the final column's value from other columns). We will proceed with our gradebook example in the next installment.
Subscribe to:
Posts (Atom)
