Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101
Home > Jan's CompLit 101 > Working with Numbers > Design > WhatIf
Icon: Arrow - Previous pagePrevious    NextIcon: Arrow - Next page

Jan's Working with Numbers

    Design: WhatIf

One of the advantages of using a computer spreadsheet over using a paper one is the ability to play the "What If" game. It is a very easy game to play. Just change a spreadsheet's data to see what the effect is. (For the safety of your data, use a copy of the original data!)

A What-If sheet is especially useful in complex situations where a change in one value affects several other values. Another situation is when you would like to experiment with the numbers to find out what they need to be for you to achieve a certain goal, Goal Seek.

A Sample What If sheet: Grade Calculator

Background Info: You would like to figure out if you can still get a A in a course, which requires an average of 90 or greater. Suppose your grade is made up of 50% test average, 25% homework average, and 25% quiz average. Sometimes there are extra assignments which let you can earn extra points that are added to your test grades. Below is an example of a spreadsheet for calculating your grade using these rules.

You will have 2 more tests, 2 more graded homework sets, and 2 more quizzes. Can you raise your grade to 90?? By using the sheet as a What-If sheet, you can plug in various combinations of grades to see if it is possible to get your A.

Sample What-If sheet: Grade CalculatorClick on illustration Click on the illustration to see three sets of additional grades:

a.  All remaining grades = 100
     Wouldn't that be cool! Not very likely to happen though.
     This shows that your maximum possible grade is 91.5

b.  All remaining grades = 90
    (Not bad but since the current grade is less than 90, it won't average ABOVE 90, will it?

c. All remaining grades = 90 plus two 10 point bonuses.

The last one rounds up to 90 - an A! So it seems possible to get that A. But, will you actually do the work?? Ah - that's always the real question.
 

Experiment with Grade Calculator

In the experiment below you will download the grade calculator spreadsheet. If the Grade Calculator opens in your browser instead of in Excel, you can still work with it. Then, when you are done, you can use the BACK button on the browser to return to this page. You do not have to save your results!

Icon: Experiment Experiment: Using a What-If Calculator

  • From the resource files, open
     c:\My Documents\complit101\numbers\grades.xls 
    or download grades.xls now. Click on Enable Editing if you downloaded.

  • Experiment by entering various combinations of new scores to see what the final grade would be.

    What additional grades do you need in order to get the average you want?

  • Goal Seeking: What is the worst you can do on the remaining grades and still keep the B (Average of 80 to 89) that you currently have. Remember, you must add at least 2 more scores in each category, except the bonus points.
  • Formula: Click in cell E13 and look at the formula in the formula bar.
    Wow! An ordinary process (averaging grades) can make for a complicated formula! You may need to scroll the Formula Bar to see it all. Did you know you could do that??

    While the formula is showing, click in it in the Formula Bar.
    Color coding appears so you can see which the ranges on the sheet match up with the ranges in the formula. Cool feature!

    The formula uses normal algebra rules for calculating. Calculations inside parentheses are done separately. Multiplication and division are done BEFORE adding and subtracting, moving from left to right.

  • Using named ranges:
    Download     or open grades-namedranges.xls
    You may have to click on Enable Editing if you downloaded.
    This is the same grades calculator but with ranges named and the percentages put into named cells. The formula now uses the names instead of the cell references. Is it easier to tell what the formula is doing now?

This kind of What-If calculator can really help you see how the various parts affect the outcome.