Transcript Spreadsheet 2

Spreadsheet 2
• Averages and other Formulas
• Calculating Interest
Assignment 1- Comparing With Formulas
• You will create some hockey stats for a team
• You can make them up or find some actual stats, it’s up to you. Here
is my example from the last spreadsheet module
• Find at least 8 different stats
• You will need two different teams
Freeze Da Panes
• Click on the top left corner of your spreadsheet
on the line below your headings. In the Tab
section, go to View and select Freeze Panes. This
will lock certain portions in place
Now Look!
• Your info stays up at the top, even when you
scroll down
Glued to the top
Now we will compare this data
• We will be using these formulas. The cells used in the formula
are just examples.
Formula
What it Does
=Average(A1:A5)
Finds the average
=Min(A1:A5)
Smallest number
=Max(A1:A5)
Largest number
=Sum(A1:A5)
Adds everything up
=Count(A1:A5)
Count
How to Insert Formulas
• Here is an example with some data. I want to add it all together, so I will
use the Sum formula. I decide where I want the sum to go, and press the
Equals sign on the keyboard.
Notice that there is an = sign up at
The top. When we put in our formula,
It will also display here when you click
on that cell
Putting in the Formula
• I simply find which cells (the range) I need to
include and put them in the Sum formula. In
this case, the cells are C8 and C13.
=Sum(C8:C13)
The colon (:) means we are adding from C8 to
C13 and everything in between the two.
Voila!. Press Enter to see the result.
Comparing the Teams
• You should now have your two hockey teams in your spreadsheet
• Down below your statistics, put this for each team:
Put in some borders
Average, Minimum and Maximum
• So now we will calculate these 3 things
• We want to find out the Average for each category. To do this,
we will do it to only one category first
• First, click in the Average row where you want your answer
and formula to go.
• Find the range of cells you need to put in the formula
• I am using cells D8 to D16. My formula would be
Average=(D8:D16)
Cells used
Formula
Finding the Rest of the Averages
• Put your cursor in the bottom corner of the cell where you
entered you formula. Click and drag it across the Average row.
The formula will automatically copy and adjust to the other
columns.
Click, hold and drag across
Too many decimal places
• Highlight all the cells in the Average row. Right
click and do this: Decimals set to Zero.
Continuing On
• Now finish the last two row, Minimum and
Maximum and the other Average column for
both teams.
• Use the formulas that were listed back a few
slides.
Down Below The Other Stats…
• Include a sum of one of the categories for
each team. Use a Sum formula. We will do this
to compare the stats of both teams
Total Number of Players
• Underneath the Total Points section, include a
section of the number of players for each
team. Use the Count formula. You will need to
select the names of the players to add up how
many there are.
Main Assignment and Pre- Assignment
• In this assignment you will learn how to
calculate the interest and amortization of a
mortgage or loan
• We will learn how long it would take to pay off
a mortgage, what your payments would be,
and how it is affected by interest rate.
Calculating Interest and Loans
We will: find a car you would like to buy on
Honda.ca’s website.
• Look for how much it costs and what the
payments for it would be monthly
• But FIRST, we will do this exercise together as
a minor assignment…….
Recreate This Table
Notice That…
• When speaking about terms for this
assignment, Rate is the per period interest
rate and NPer is the total number of periods
(12 payments a year). In this case, as shown in
the picture, we calculate the Rate with B4/B5
(0.5625% per month), and NPer is B3*B5 (360
months). Principal is entered as -B2 (-200,000,
negative because we want the answer to be a
positive number). You can see that the
monthly payment is $1,297.20.
. So, we now need to separate that
payment into its interest and principal
components.
• When you take out a loan, you are paying two
things: Interest and Principle interest.
Principle Interest is the actual amount of the
loan that you borrowed. Interest is the
interest added onto the loan that you also pay
each payment. Unfortunately, you will mostly
pay regular Interest first before anything goes
into your Principle. Bummer.
Deep stuff, dude
• Using these formulas, we can see that the interest component of
the first payment would be:
• Interest in 1st Payment = 200,000 x 0.005625 = $1,125
• and the principal payment is:
• Principal in 1st Payment = 1,297.20 - 1,125 = $172.20
• Note that the sum of the interest and principal is the amount of the
total payment:
• 1,125 + 172.20 = $1,297.20
• That is the case for every single payment over the life of the loan.
However, as payments are made the principal balance will decline.
This, in turn, means that the interest payment will be lower, and the
principal payment will be higher (because the total payment
amount is constant), for each successive payment. See, over time
the interest goes down and you actually put more money into it
Using Built-In Functions
• We've now seen how the principal and interest
components of each payment are calculated. However,
you can use a couple of built-in functions to do the
math for you. These functions also make it easier to
calculate the principal and/or interest for any arbitrary
payment.
• The two functions from the Finance menu that we are
going to use are the IPMT (interest payment) and
thePPMT (principal payment) functions. These
functions calculate the amount of interest or principal
paid for any given payment.
Know the Lingo
• So, using our data from above, we can calculate the amount of interest in
the first payment with:
• =IPMT(B4/B5,1,B3*B5,-B2)
• and we get $1,125. The amount of the principal in the first payment is:
• =PPMT(B4/B5,1,B3*B5,-B2)
• which gives $172.20. Those answers match exactly the ones that we
calculated manually above. Note that in both functions, we specified that
Per (the payment period) is 1 for the first payment. We would specify 2 for
the second payment, and so on. Obviously, we will use a cell reference in
our amortization table.
• Excel does not have a built-in function to calculate the remaining balance
after a payment, but we can do that easily enough with a simple formula.
Simply take the beginning balance minus the principal paid in the first
payment and you will find that the remaining balance after one payment
is $199,827.80:
• Principal Balance After 1st Payment = 200,000 - 172.20 = $199,827.80
Creating an Amortization Schedule
• We will create a chart to keep track of our
Mortgage. Amortization means the growth of
the money put into the loan. Recreate this on
the next slide. You already have the top:
Next…
• The first thing that we want to do is to set up the table starting with
the labels in A8:E8.
• Now, in column A we want a series of numbers from 0 to 360 (the
maximum number of payments that we are going to allow).
• To create this series, select A9 and then choose Edit » Fill » Series
from the menus. This will launch the Series dialog box. Fill it in
exactly as shown, and then click the Ok button.
More Formulas
• At this point, we are ready to fill in the formulas. Start with
the beginning principal in E9 with the formula: =B2. That
will link it to the principal balance as given in the input
area. Now, select B10 and enter the formula:
=PMT(B$4/B$5,B$3*B$5,-B$2), and you will see that the
monthly payment is $1,297.20 as shown above.
• In C10 we will calculate the interest portion of the first
payment with the formula: =IPMT(B$4/B$5,A10,B$3*B$5,B$2). The principal portion of the payment can be
calculated, in D10 with: =PPMT(B$4/B$5,A10,B$3*B$5,B$2). Finally, we calculate the remaining balance in E10
with the formula: =E9-D10.
All the Way
• Check your results against those shown above, being very careful to
type the formulas exactly as shown (the $ are important because
they freeze the cell references so that they don't change when we
copy the formulas down).
• Once your results in row 10 match the picture, copy the formulas all
the way down to the end of the table in row 369.
• (Note: The easiest way to do this is to select B10:E10 and then
double-click the Auto Fill handle in the lower right corner of the
selection. This will copy the formulas to the end of the current
range, which is defined by the last data point in column A.)
Graphs!!!!!
• The final enhancement that I have made is to
create a chart that shows the remaining
balance declining over time. Basically, all you
need to do is to select A8:A369 and E8:E369
and then create an XY Scatter chart. I've
fancied it up a little bit with a live chart title
and a scroll bar, but I'll leave those features to
another tutorial. The final result is shown
below.
Here it is…
Assignment
• Using the same template you have just made,
find a new car to buy. Find out how much it
costs to finance it and what its payments are.
Plug this info into a new version of your
previous assignment. We will make our own
amortization schedule for the car. Honda has a
great website for this.
• Also create a graph for this one as well.
NEW MINOR ASSIGNMENT
Using a “Lookup” formula
• This formula will look things up that we tell it to!
• To start, recreate the table on the next page that
EXACTLY as shown
Copied and
Pasted from
table
Typed in.
Press Alt+
Enter for
space
Table
Syntax
• After understanding your objective, it’s
important to learn the syntax, or inputs
required, to write out the VLOOKUP formula.
• the syntax for a VLOOKUP automatically
appears once you type in the formula and an
open parenthesis. We’ll look at each
component individually.
• It looks like this:
=VLOOKUP ( lookup value , table_array ,
col_index_num , [range_lookup] )
Now we do this
Why?
• The lookup value is the value we need more
information on. In the example below, this is
the ID number “5″. We will start by typing our
formula in the blank cell where we want to
return the missing information. Then we
select the lookup value for the first
component of the syntax and follow it with a
comma.
Next Step- Table Array
• Table Array
• The table array is larger data set where your return
value exists. In most cases this dataset will be large
enough that you’ll want to use a formula to pull the
value, rather than looking it up manually. Once you
become proficient at VLOOKUP, it also reduces the
possibility of making a mistake.
• To select your table array, simply click and drag around
the entire data set that is relevant to your lookup
formula. And again, complete the entry of this
component by inputting a comma.
What it looks like
Column Index Number
• This component simply references which column
you want to pull back your data from. It’s
important to note that the minimum number for
this input is 2 – you cannot look up and return
the same value (as doing so probably doesn’t
make much sense).
• The maximum value depends on how large your
table array is. In the example below, we only have
5 total columns. Therefore, our maximum input
for column index number is 5. Inputting any
number higher than 5 will return an error value.
• In the example below, since we want to return
the State related to our ID, and City is the fourth
column in the table array we selected, we input
the number “4″.
Range Lookup
• Range Lookup
• Few people actually know what a range lookup does, because range
lookup is a functionality that few people ever use. The range lookup
simply tells your lookup formula to look for values that are close to
your lookup value, not ones that are exactly the same. The rules on
what’s “close to” your lookup value are vague and if you’re working
on a project where you need some level of precision, you can see
why this functionality can be problematic. You basically have three
options to deal with the range lookup portion of the syntax:
• 1. Choose not to use range lookup
To choose this option, input either a “0″ or the word “FALSE”. Many of
us who’ve used this formula for years are used to typing in FALSE; the
option to put in a 0 is relatively new.
• 2. Choose to use a range lookup
To turn on the range lookup functionality, simply do the reverse of the
prior inputs, and enter either “1″ or “TRUE”.
Range Lookup Cont.
• For our particular example, because we want an exact
match, we will input “0″ so the VLOOKUP does not
perform a range lookup. After you’ve written your
formula with your desired inputs, close the formula
with a “)” and press ENTER to finish writing it.
And…
• What Excel Does
• If you’ve written the formula properly, the value you
were looking for will be returned in the lookup result
cell. After you’ve written all of your parameters, the
formula performs a vertical lookup:
• Starting from the top of the leftmost column of your
table array, it searches for your lookup value
• Once it finds your lookup value, it stops moving down
and begins moving over to the right based on the
column index number you specified
• After moving right by the specified number of columns,
it returns whatever value it lands on
If It Worked Properly
• You should see this! That’s the end of this part!
Conditional Formatting!
• Conditional Formatting in Excel lets you create
your own custom formulas to find and solve
data.
• By applying conditional formatting to your
data, you can quickly identify variances in a
range of values with a quick glance.
• Let’s learn how this works!
There are 7 basic steps
•
•
•
•
•
•
•
•
Select the cells you want to format.
Click Home > Conditional Formatting > New Rule.
Click Use a formula to determine which cells to format.
Under Format values where this formula is true, enter your
formula.
Click Format.
Use the controls on the Number, Font, Border, and Fill tabs to
change your data, or the cells around your data.
For example, you can change negative values to bold and red, add a
light yellow fill to cell backgrounds, or add a dashed border.
Click OK to close all open dialog boxes.
• See the next pages for some examples we will do together
Example 1
• Here's an example:
• Select all the cells in the table from the last assignment. To
do that, click the selector above row 1 and to the left of
column A.
• Repeat steps 1 through 3 in the steps above.
• When you get to step 4, enter =MOD(ROW(),2)=1. You can
copy and paste the formula if you want to.
• Click Format, then click the Fill tab and select a shade of
blue from the color palette.
• Click OK to complete the rule, and now every other row in
your worksheet is shaded in the color you chose.
• That's a little of what conditional formulas can do in a
formatting rule.
What it looks like
Example 2
• Highlighting just the “State” column of our
previous data, we will use a formula to find
duplicates
Enter…
• =COUNTIF($A$1:$D$11,D2)>1
• Just like last time, select your colour and
format it.
It shows which
Data is repetitive
Example 3: True and False Equations
• Recreate this formula:
• Find values that meet one condition: In this one, Excel only
displays TRUE if the value in A2 meets one of the conditions—it's
greater than the value in A3 or less than the value in A4.
Example 4- OR and IF
• Try the same formula from #3, only this time,
replace AND with OR and IF
• What happens?
• Depending on the data you enter, it will give you
an answer of OK/ Not OK or True/False
Let’s take this a step further
• We are going to make a report card. We will
set up some data like this:
The Formula
• =IF(B2>=80,"A", IF(B2>=75, "B+", IF(B2>=70,
"B", IF(B2>=70,"B", IF(B2>=65,"C+",
IF(B2>=60, "C","D"))))))
•
Simple put it into cell B2, then drag and drop the formula down to complete the
grades:
Let’s change it up a bit
• Replace the letter grades with "Pass" and
"Fail":
• =IF(B2>59,"Pass","Fail")
• Can you get it to work?
The Last Thing: Pivot Tables
• Pivot tables take your data and you can control with the
table which info it displays of excludes into a fancy report.
Select your grade data and create a pivot table. A window
will pop up. Press OK.
Which Data?
• Click on each set of data and add it in. Now we
want to add a Splicer, which will filter some
information
Select “Grade”, press OK
• As you can see, it filtered out everything but
the different grades that are available
Second Main Assignment:
School Report Card Database
• You will create a database for 6 different courses
for Mr. Rattray’s Grade 4 class.
• You will create data for 10 imaginary students.
Each student is enrolled in all of the 6 subjects.
Include a mark for each student in each of the
subjects.
• The database will look professional and
organized. Resize cells, avoid clutter, use space
well. It is up to you to decide how it will look.
What to Do
• Include a Range Lookup to display the marks of just one
student
• Use a colour-coding formula as explained on Page 49
• Use a formula to find the average mark in each class:
=A1>AVERAGE(A1:A14)
• Use a formula to show duplicate marks
• Use conditional formatting to give students a letter grade
• Use conditional formatting to give students a note of Pass
or Fail
• Use different styles of text and colour
• Create a Pivot Table with a Slicer
• When you save your work, save a copy of it as a website.
Print out your work, including the Pivot Table.
Great Success!!!