Some History...

Charles Babbage was an Englishman born in December of 1791 and died in 1871. He is known to some as the "Father of Computing" for his contributions to the basic design of the computer through his Analytical Engine. In 1819 Babbage designed a machine, called the "Difference Engine," to construct computational tables using the method of differences by mechanical means. He had completed it by 1822, and his machine was able to carry out complex operations using only the mechanism for addition. His work on the Difference Engine led him to a much more sophisticated idea. By 1834 Babbage had completed the first drawings of a far more powerful machine, the "Analytical Engine", the forerunner of the modern electronic computer. Although the Analytic Engine never progressed beyond detailed drawings, it is remarkably similar in logical components to a present day computer.

Augusta Ada Byron, Lady Lovelace was born in December of 1815 and died of cancer at only 36 years old. She is remembered as the "World's First Computer Programmer" and is credited for inventing several crucially important programming constructs including subroutines, the loop which allowed the computer to repeat a set of instructions, and the conditional jump which allowed the computer to make decisions. In an article, published in 1843, Lady Lovelace's comments included her predictions that a machine such as the Analytical Engine might be used to compose complex music, to produce graphics, and would be used for both practical and scientific use. She was correct. In particular, Ada wrote a plan for how Babbage's Analytical Engine might calculate Bernoulli numbers. This plan, is now widely regarded as the first "computer program."

Read about their quirky history at Tools for Thought by Howard Rheingold, http://www.rheingold.com/texts/tft/2.html

In this assignment, we will see these "programming constructs" at work. The SUM and AVERAGE functions are examples of subroutines and the IF statement is an example of the conditional jump. Fill-down (which we say briefly in the last assignment and which you might use in this assignment) in a spreadsheet is a type of loop.

Setting Up Your Grade Sheet:

The assignment is to design a worksheet so that you can keep track of your grades in this course for the term. Open a new Excel worksheet and save it as YourUserNameA4 (there will be an "XLS" extension by default). You will be including all the information necessary to compute your final grade, including each homework grade, quizzes, test grade, etc. You will find the information you need listed on the syllabus.

There are several things to keep in mind while you create your spreadsheet.

• The aesthetic design is up to you, but make sure your gradesheet looks legible and good. Remember, this worksheet will be with you this whole semester, so leave yourself opportunities for change and expansion.
• You will be putting in formulas in each of the cells that perform calculations (such as averages and so forth) automatically by Excel. Do not do these calculations yourself and put the numbers in the cell instead! Let Excel do the work.
• Depending on which cells you have for your homework and test grades, you may have to use a combination of relative and absolute cell referencing.
• To make sure that Excel knows you have a formula in a cell, you should start the contents with an equal sign. Otherwise, it may think you want to simply have the formula "as is" typed in the cell.
  For example, to calculate the total of four cells C4, C6, D3 and D5, into cell F30, you must type in either =SUM(C4,C6,D3,D5) or =C4+C6+D3+D5 into cell F30.
• When you want to apply an operation on a range of cells, the format is [firstcell]:[lastcell], so to find the sum of cells C1 through C5, you can type =SUM(C1:C5).
• You can use the mouse to click and select regions (remember, this is what Excel was unique in introducing) instead of you typing it in.
• The undo command (using Ctrl and Z keys) is your friend.
• You can always press the Ctrl and 1 keys to format a cell if you want to change the border and other features

Do the following to create your gradesheet

1.

Input the formulas that will calculate your homework averages, your test averages, your quiz averages, and a final grade in the class. Feel free to put in "guesses" for your scores while you build the spreadsheet. Several Requirements and Pointers... Be sure to leave enough space for an unspecified number of homework sets. This will mean that you should sum the a mount you got for your homework, and sum up the total possible on homework, divide and then scale your score to 100 points. Use the SUM function for these. Note: Do NOT use the average function for your homeworks because each homework assignment has a different total possible based upon the perceived difficulty. Each quiz in this course will be worth 10 points. Use the AVERAGE function to find your average score on quizzes. Then scale this average to 100 points. Include a set of cells that list the percentages as given in the syllabus for each of these categories (test 1, test 2, test 3, quizzes, homework, etc.) and then your formulas should appropriately reference these cells with absolute or relative referencing appropriately. In addition, you should have two cells that calculate several possible final grades for the class. In a cell (it does not matter which one), type in a formula that will compute what your grade would be without taking the final. This will allow you to determine whether or not you need to take the final exam. In another cell, insert a formula that will calculate your grade if you you got a 90 for the final exam. Above both of these cells, make sure you include a label showing what these cells are supposed to calculate Remember to use appropriate references in your formulas, so that at a later date you can simply experiment with what you need on the final, say, to get a given grade in the class.

2.

Next, add formulas to a few cells to your Excel grade sheet that will calculate the LETTER GRADE for the class based on your final average in the class. This will be based on the final grade originally calculated (one that includes all the grades). To do this, Excel will have to answer questions such as: "If I got a 90.2 in the class, what grade should I receive?" The syllabus explains what grades guarantee an 'A' for the class (90 to 100), what is needed for a 'B', etc. You will use these and a conditional statement called an IF function to tell Excel how to answer this question. (Note: You may neglect the +/-'s on the grades for simplicity... OR not... It is up to you.) In this example, the grade should be an 'A' because the final average is a 90.2, which is between 90 and 100 points. You can use Excel's Office Assistant to read more about the IF function, but the three basic parts of the function are 1) the condition, 2) what to do if the condition is true, and 3) what to do otherwise. The function looks like: =IF(condition, what do to if condition is true, what to do if the condition is false) The "condition" is what you need to check. For the example above it would be '=IF(grade>=90,"A","Not an A").' Here the condition is checking whether the grade (which is in some cell you are checking) is bigger than or equal to 90. If that is the case (if the condition is TRUE), then the letter A will appear in the cell that you put the "IF" formula. Otherwise, it will put the words "Not an A" in the cell. Make sure you can get this much before you go any further. If you've got this, then, of course, realize we're not done, because "Not an A" isn't very specific. So, now instead of 'Not an A' in the last part of the IF statement, put something that will answer this question: "If I get a grade that is greater than or equal to 80 (but smaller than 90), what grade should I get?" Notice the first word in that question. Once you get that "B" grade to work, you should be able to figure out what to do to calculate any possible grade.

Submit your assignment (the YourUserNameA4 spreadsheet) in the dropbox "A4 Grades".

Back to Introduction to Computer Science