Excel Chapters 57

1
Excel Chapters 5/7

• Sean Banerjee
• sean.banerjee_at_mail.wvu.edu

1
1
2
Upcoming Deadlines

• MyITLab Lesson 2 is due on February 9
• Homework 2 is due on February 13
• Homework 3 is due on February 20

3
Cell References

• Absolute A1
• Relative A1
• Column Absolute A1
• Row Absolute A1

4
Grade Analysis Homework 1
Overall all of you did very well, but please do
not get complacent!
5
Common problems on Homework 1

• Rank function
• General formatting
• Conditional formatting
• Analysis questions
• Please read the instructions carefully, most
  errors were caused by students not reading the
  instructions.

6
A reminder on cheating

• DONT CHEAT
• If you cant read/understand English, feel free
  to translate that into a language of your choice
  by using Google Translate http//translate.google
  .com/translate_t?hlen

7
Agenda for today

• Pivot Tables
• Pivot Charts
• Trendlines revisited
• Introduction to Goal Seek

8
Pivot Chart/Table

• A pivot table is a data summarization tool.
• Among other functions, they can automatically
  sort, count, and total the data stored in one
  table or spreadsheet and create a second table
  displaying the summarized data.
• In general a pivot chart and associated pivot
  table enables us to visualize data without
  reverting to complex functions.
• Source Wikipedia/Microsofts website

9
Pivot Chart/Table - Example

• The goal
• For each student rank find the average GPA by
  each major for each semester.
• Method 1 calculate the averages by using the
  average function.
• Method 2 use a Pivot Table to create the
  averages for us.
• To create the pivot table click on cell A1 in the
  Grades Data sheet
• Next click on Insert Pivot Table
• Verify it is selecting the right cell range and
  hit OK
• Rename the newly created sheet to Grades Pivot
  Table

10
Pivot Chart/Table - Example

• There are 4 critical elements in a Pivot table
• Report Filter generally enables you to
  visualize based on multiple criteria. Our report
  Filter is the Rank.
• Row Labels defines what you want in your . Our
  row label is the Semester.
• Column Labels defines what you want in your
  columns. Our column label is Major.
• Values your data. Our data is the GPA
• NOTE sometimes column and report can be
  interchanged depending on what you are looking
  for.

11
Pivot Chart/Table - Example

• Drag and drop Rank into the Report Filter area
• Drag and drop Semester into the Row Label area
• Drag and drop Major into the Column Label area
• Drag and drop GPA into the Values area
• Now click on the down arrow where it says Sum of
  GPA() in the Values area and select Value Field
  Settings. Change the Value Field Setting from
  Sum to Average. This will enable us to see the
  values not as a sum but as an average.

12
Pivot Chart/Table - Example

• Now we can create a PivotChart to better
  visualize the results.
• NOTE Always create a Pivot Table first then
  create the chart from it.
• Click on Options then Pivot Chart
• Select the first Clustered Column Chart
• Make sure you move the chart to a new sheet
  called Grades Pivot Chart
• Notice how easy it is to see which majors had the
  highest GPA in each semester.

13
Trendlines

• The task
• Using data presented from 1910 2005 predict the
  US birth rates in 2008. You are told the 2008 US
  Birth Rate is 14.2 as provided by the US Census.
• Select cells C1 to C63 in the Birth Rates sheet
  and insert a 2d line chart
• Move the chart to a new sheet called Birth Rate
  Chart
• Format the lower axis so it shows the years. You
  can do this by right clicking on the chart and
  hitting Select Data. Then click Edit on
  Horizontal (Category) Axis Labels and selecting
  cells A2 to A63 from the Birth Rates sheet

14
Trendlines

• Right click on the chart and select Add
  Trendline. We will add a trendline and project
  it forward 10 periods.
• Determine which trendline works best based on the
  R-squared value
• Exponential 0.7807
• Linear - 0.7431
• Logarithmic 0.7556
• Polynomial 0.7995
• Power 0.7407
• It looks like polynomial has the best R squared
  value, but if we were to project the data forward
  another 10 units we can see that it will start to
  increase pretty fast. In this case Logarithmic
  or Power may be the best choice as it plateaus
  even if we project forward more units.

15
Trendlines

• Remember trendlines must fit the existing data,
  but also project plausible future values.
• We cant have negative gas prices
• We cant have negative accident rates
• We cant have negative birth rates (theoretically
  we can, but practically no)
• The R-squared value is a handy indicator of what
  is the best trendline, but in the end you have to
  determine what works best based on your knowledge
  of the data provided

16
Goal Seek

• Goal seek enables us to answer the perennial Can
  I afford this question.
• Can you afford a 200,000 house on a 30 year
  mortgage if you can only make 500 a month in
  payments
• Can you afford a 50,000 car on a 5 year loan if
  you make 400 in payments at a 4 interest rate

17
Goal Seek

• Finding the initial monthly payments
• In the Goal Seek sheet use the PMT function to
  find the initial monthly payments
• You can do this by typing in
• PMT(B4/12,B5,-B3)
• B4/12 gives us the interest rate per month
• B5 gives us the loan period
• -B3 ensures we return a positive monthly payment
  value

18
Goal Seek

• Task 1
• If you made 500 in payments each month, what
  car could you afford?
• Click on Cell B7
• Go to Data What if analysis Goal Seek
• Set cell B7 to 500 by changing cell B1
• It looks like you can afford a 32,826 car

19
Goal Seek

• Task 2
• How quickly could you pay off the loan if you
  made 500 in payments for your 30,000 car.
• Click on Cell B1 make sure it says 30000
• Click on Cell B7
• Go to Data What if analysis Goal Seek
• Set cell B7 to 500 by changing cell B5
• It looks like you can pay the loan off in 53
  months

20
Goal Seek

• Task 3
• What is the max rate you could afford if you
  made 500 in payments for 60 months for your
  30,000 car.
• Click on Cell B1 make sure it says 30000
• Click on Cell B7
• Go to Data What if analysis Goal Seek
• Set cell B7 to 500 by changing cell B4
• It looks like you can afford a 7 interest rate

21
Next Class

• Nested conditional functions
• Data tables
• Goal Seek
• Scenarios