Creating a WhatIf Spreadsheet Application

1
Creating a What-If Spreadsheet Application

• GPA Prediction Calculator

2
Becoming Operationally Attuned to GPA
Calculations (Part 1)

• Calculate current semesters 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 semesters GPA by this semesters
  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
  semesters 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 semesters 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(C7lt1,0,9999)
• IF(C7lt1,0,LOOKUP(B7,H2I12))

12
IF Statement Syntax (Visual Basic)

• IF condition THEN
• statements performed if condition is true
• ELSE
• statements performed if condition is false
• END IF