Creating a “What-If” Spreadsheet Application

1. Creating a “What-If” Spreadsheet Application GPA Prediction Calculator

2. Becoming Operationally Attuned to GPA Calculations (Part 1) Calculate current semester’s weighted GPA: • For each course, calculate the product of credits and grade points • Add products from a) together for all courses this semester • Add credits together for all courses • Divide sum of the products in b) by total credits in c)

3. Becoming Operationally Attuned to GPA Calculations (Part 2) Calculate cumulative GPA: • Multiply past GPA by past total credits • Multiply this semester’s GPA by this semester’s total credits • Add a) and b) together and divide by the sum of total credits from the past and current semesters

4. Data Needs

5. What-If Scenarios User will change the grade assumptions for this semester’s courses, and maybe even consider dropping courses, to see the effect on cumulative GPA.

6. User Interface Issues: • Users express grades by letter (A, A-, B+, …), not grade points. • Resulting GPA should be rounded to two decimal places. • User takes an unknown number of courses, but we can reasonably cap it at 7. • Multiple students, with similar general needs, will use this spreadsheet. • Do we want to show any interim calculations, such as this semester’s GPA?

7. Spreadsheet Development Guidelines – General • Remember that spreadsheets, like Word documents, are made up of “objects” that have properties that you can format. Object types include cells, sheets, drawing objects, and individual characters too. • What is displayed (because of formatting) is sometimes different than what is stored, and used in calculations. • Expect that there is a function available to perform standard calculations, and learn how to find them. • Understand absolute and relative addressing, including which component of an address to make absolute: column, row, or both (sheet?). • Name cells that contain constants. Named cells are always absolute addresses.

8. Spreadsheet Development Guidelines – Reliability (intrinsic) • When building formulas, it is often quicker and less error prone to click on the cell that you refer to (letting Excel enter the address) rather than typing in its address. • Keep it simple: don't do too much in any one step. • Check for errors as you go (by entering test values in the data entry areas as you go). • Test at the extremes of possible user input in addition to expected values. • Just because you don't get an error does not mean the result is correct. • Do not bury values in formulas. • Work on keeping formulas readable: use parentheses liberally; name the cells that contain constants; break complex tasks into multiple steps; use lookup tables.

9. Spreadsheet Development Guidelines – Reliability (extrinsic) • Always present information to the user in the form the user is most familiar with (for ease of use and to avoid user conversion errors). • Do not ask user to enter the same information in more than one place (redundancy). • Always make sure units are clearly labeled. • Work hard to anticipate user errors or misinterpretations. • Formatting is not just to make the spreadsheet look pretty, but to organize and communicate clearly with the user. • Don't assume the user will enter information as you would: label and constrain user input cells. • Clearly mark (and or protect) cells that should and should not be changed by the user.

10. Getting Started

11. IF Function Syntax (Excel) =FunctionName(parameters) =IF(param1,param2,param3) =IF(Condition,DisplayIfTrue,DisplayIfFalse) =IF(C7<1,0,9999) =IF(C7<1,0,LOOKUP(B7,$H$2:$I$12))

12. IF Statement Syntax (Visual Basic) IF condition THEN statements performed if condition is true ELSE statements performed if condition is false END IF