PROJECT: GRADE FIGURE-OUTERER (gfoX)

OBJECTIVE

To successfully obtain, input, calculate, and derive your current overall status in the course through the use of a spreadsheet calculator.

BACKGROUND

For this project, you will be using Google Sheets to create a spreadsheet and use it to tabulate and compute your current status in the class.

You will need a Google account (a personal one is fine; if you use your school account, be mindful of any sharing restrictions that may apply). Sheets is accessed through your web browser at:

  • https://sheets.google.com

No installation is required. You will still use your lab46 account to look up your results (see LOOK UP YOUR STATUS below), but the spreadsheet itself is built in the browser.

GOOGLE SHEETS BASICS

If you have never used a spreadsheet before, here is what you need to get started. Google also has built-in help (Help menu -> Sheets Help) and a function list (Help -> Function list) you can reference at any time.

CELLS AND REFERENCES

A spreadsheet is a grid of cells. Columns are lettered (A, B, C…) and rows are numbered (1, 2, 3…), so the cell in column B, row 4 is B4. Click a cell to select it, and type to enter data.

  • A cell whose contents start with = is a formula.
  • Formulas can reference other cells: =B4+B5
  • Formulas can reference a range of cells: =SUM(B4:B9)
  • Formulas update automatically when the cells they reference change.

USEFUL FUNCTIONS

Some functions you will likely find helpful (there are others; explore!):

  • SUM(range): add up all the numbers in a range
  • MIN(a, b): the smaller of two values (useful for capping)
  • MAX(a, b): the larger of two values
  • ROUND(value, places): round to a number of decimal places
  • IF(test, if_true, if_false): choose between two results
  • IFS(test1, result1, test2, result2, ...): chain multiple tests
  • VLOOKUP(key, range, column, is_sorted): look a value up in a table

FORMATTING

Use the toolbar to make your spreadsheet readable: bold headers, borders, cell background colors, column widths, number formats (such as decimal places or percent). You can also merge cells for titles (Format -> Merge cells).

SAVING

Google Sheets saves automatically as you work. Give your spreadsheet a meaningful name by clicking the title at the top left of the page (for example: gfo0).

See the SUBMISSION section for how to get your finished work to me.

LOOK UP YOUR STATUS

Ever since the first projects came due and were evaluated, your results were accessible to you on lab46 via the status command.

The synopsis for the status command is:

status DESIG [OPTION..]

Where OPTION is any valid presence and combination of viable options (detail is one, help is another).

The most common usage will probably be (square brackets in this context denote optionality):

status DESIG [detail]

Customize per the desired request. If you do not want the detailed view, omit the detail option. If you desire it, include the detail option.

NOTE: status and the data it reads from are only accessible on lab46.

lab46:~$ status DESIG

Furthermore, if you were curious about the breakdown of points, you could also append the 'detail' argument to get an expanded view of information:

lab46:~$ status DESIG detail

NOTE: because 'detail' is an optional argument we use the square brackets to denote optionality. You do not type them, instead, you either type what is in them, or you do not, depending on desired functionality.

CALCULATING YOUR GRADE

Your grade is calculated from four distinct components, three of which you regularly encounter each week through the semester, and the final one at the end. Each is worth a certain amount of overall points, that when added up equals 104 points. With their individual point values, they are:

  • projects (52 points)
  • notes (13 points)
  • participation (13 points)
  • eoce (26 points)

Within each category, you tally up two different accumulations of points:

  • all the points YOU got
  • all the points POSSIBLE to get

Each category (and the overall grade) is evaluated as a sum of points earned out of a sum of points possible (NOT an average of averages).

For example, take this sample participation dataset:

1:wcp1:final tally of results (1/1)
2:wcp2:final tally of results (2/2)
1:wcp3:final tally of results (1/4)
5:wcp4:final tally of results (5/8)
4:moar:final tally of results (4/0)

This person got 1+2+1+5 points (and 4 bonus points), for a total of: 9 + 4 = 13 points.

The total amount of points available (at this point) was 1+2+4+8+0, or: 15 points

A note on 'bonus' points: in each category, there is the potential for the accumulation of bonus/extra/moar points. These are added into the mix, BUT for the purposes of grading, the final tally of YOUR points can NOT exceed the final tally of total points (bonus points only pad the category in which they were earned, and only up to that category's maximum).

In this case, the person amassed 13 total points, out of a total of 15 points: for purposes of grading, for their participation score, it would be 13/15. Bonus points only apply to the particular category in which they were earned (they cannot be applied to a different category).

Continuing this example, the person got 13/15ths of the 13 available points for that section (participation is worth 13 total points). We would calculate that "out of 13" result as follows:

(13/15)*13 = 11 (so at this point, they'd have 11 out of 13 participation points)

Do the same for projects (52) and notes (13). For now, the total possible grade points you can receive is 78 total points (52+13+13). So that is what your spreadsheet should reflect. (EoCE does not exist yet, so it is not part of this calculation.)

LETTER GRADES

Your letter grade is obtained by determining your score (out of 100) and selecting the letter grade pertaining to that value. Letter grades are pegged to the following numeric values (per the course syllabus):

A   98.00+
A-  92.00 - 97.99
B+  86.00 - 91.99
B   80.00 - 85.99
B-  74.00 - 79.99
C+  68.00 - 73.99
C   62.00 - 67.99
D   56.00 - 61.99
F    0.00 - 55.99

Your score out of 100 is your current points out of 78, scaled to 100: (your points / 78) * 100.

SPREADSHEET

For this project, your task is to make a spreadsheet in Google Sheets that, in clearly readable and organized fashion, plots out your various results on the different deliverables for the class, ultimately displaying your current overall grade for the course.

I want to see each project (identified), along with the points you got, along with the total points possible for a "dashboard" quick glance, calculate your project "score" out of 100 (just to give you a feel for how you did on a particular project). Be sure to:

  • have a category for bonus points you have earned
  • have a projects score tally (all your points earned)
  • have a projects total tally (all the points possible to earn)

Calculate your projects "score" out of 100 (just to give you a feel for how you did on the category overall).

Calculate your projects "points" (out of 52), and display it under an identifiable label.

Do the same for the participation and notes categories as well.

Add up your category points (52+13+13=78), and have these be near each other in your spreadsheet.

Calculate your actual current score for the class (based on a total of 78 points), and use a spreadsheet function (such as IFS or VLOOKUP) to determine from the letter grade ladder where you fall, displaying the resulting letter grade.

Do NOTE: the aim and spirit of this project is to use the SPREADSHEET as the calculator. I want to see embedded equations, NOT pre-calculated values in your spreadsheet data. I do NOT want to see you being the calculator, pre-calculating all the values, and just entering them. That is NOT effective use of the spreadsheet.

If you'd like to continue using this spreadsheet for additional points accrued in the coming weeks, you may want to plan ahead and set up your "final results" tally with some space away from the growing list of results (or on its own tab). Using ranges that have room to grow (for example, SUM(B2:B30)) can save you from having to rewrite formulas later.

TIME-SENSITIVE DATA

gfoX is issued twice during the semester, and each iteration is to capture the following snapshot of data in its reporting results:

  • gfo0: start of semester through and including week 6 deliverables
  • gfo1: start of semester through and including week 12 deliverables

There is a bit of a lag from the end of the sampling window and when that project is due:

  • gfo0 (through week 6 data) will be due alongside pct9/wcp9
  • gfo1 (through week 12 data) will be due alongside pctE/wcpE

Each iteration is a fresh snapshot: gfo1 should include everything from gfo0 plus the weeks since (weeks 1-12 in total). You are welcome to build gfo1 by copying your gfo0 spreadsheet and extending it (in Google Sheets: File -> Make a copy), as long as the final result is correct for the full week 1-12 window.

SUBMISSION

To successfully complete this project, the following criteria must be met:

  • Solution must utilize the algorithm/approach presented above
  • Output must conform to the specifications presented above
  • Submit your solution by committing it to your private semester repository (NOT via the lab46 submit tool).

Your finished spreadsheet needs to be exported from Google Sheets in a format that preserves your formulas, and then placed in the gfoX project directory of your private semester repository:

  1. In Google Sheets, choose File -> Download -> Microsoft Excel (.xlsx). Name/rename the downloaded file gfoX.xlsx (substituting the actual iteration, such as gfo0.xlsx or gfo1.xlsx).
  2. Place the file in the corresponding project directory of your private semester repository (the same place you would put other project deliverables).
  3. Add, commit, and push it.

NOTE: do NOT download as CSV. CSV files contain only the displayed values and discard your formulas, which would defeat the purpose of the project.

Be sure the push completes without errors, and verify that your file shows up in your repository. If not, check for typos and or locational mismatches (wrong directory, wrong filename, forgetting to push).

Your submission is evaluated as of the project's due date, so be sure to push before the deadline (the late policy applies as usual).

It is also a good idea to keep your Google Sheet itself as a backup.

RUBRIC

What I will be looking for:

52:gfoX:final tally of results (52/52)
*:gfoX:gfoX.xlsx data points entered as numbers, adequately labelled [13/13]
*:gfoX:gfoX.xlsx all calculations done as equations in spreadsheet [13/13]
*:gfoX:gfoX.xlsx calculations reference cells, NOT literal data [13/13]
*:gfoX:gfoX.xlsx calculations use available spreadsheet functions [13/13]

Additionally:

  • Solutions not abiding by SPIRIT of project will be subject to a 25% overall deduction
  • Solution output lacking ORGANIZATION or not easy to read (fits on a standard screen without excessive scrolling, clear labels and formatting) are subject to a 25% overall deduction