# Arun_Day5

Document Sample

```					Day 5:Excel Chapter 2
Announcements
• Homework # 1
– Due by 11:59 pm on 9/17/2010
http://cs101.wvu.edu/971 page of our class
website.
is an very serious issue in Cs101
• Myitlab Lesson #1(Excel Chapter 1-4) due by
tonight 11.59pm
Outline
• Functions & Cell references
– IF, SUM, AVERAGE, MEDIAN, MIN, MAX, and TODAY
– Cell References example
– VLOOKUP Function
• Worksheet Appearance
– Commenting, Background Fills, Font Colors, Format
• Payment Function (PMT)
Basic Statistical functions
Function            Purpose

Sum            To find sum of range of numbers

Average        To find average of range of
numbers
Median         To Mid point value in a range

Maximum        To find largest number in a range
of numbers
Minimum        To find smallest number in a
range of numbers
IF Function & Logical Operators
•Syntax:
=IF(logical_test, value_if_true, value_if_false)
Function:
If logical_test is true, value_if_true is returned.
Otherwise, value_if_false is returned. The return value can
either be a mathematical operation or text display
Example of valid logical_tests:
• “Equal”              A1=B1
• “Greater Than”       A1>B1
• “Less Than”          A1<B1
• “Greater or Equal”   A1>=B1
• “Less or Equal”      A1<=B1
• “Not Equal”          A1<>B1
Using IF function
• Example: we want to show under hazardous
– “yes” if the reading is above100
– “No” if below (or equal to)100
• In cell C2 type
=IF(B2>B19, “Yes”, “No”)
• Autofill cell C3 to C11.
– But we’ve got something unexpected . Why??
Concept of Cell Reference

• Check the formulas from cell C3 to cell C11.
– It refers to cell B20, B21,… but we have nothing in there!
– When the formula was copied, the references B2 (and
B20) were automatically changed to B2, B3, … (and B20,
B21, …) so that the original relative position between B2
and B19 is kept. This is called a relative reference.
• To always reference to a specific row (and/or
(absolute reference)
=IF(B2>\$B\$19, “Yes”, “No”)
Cell References
• When a formula is copied to other cell, the references
in the formula are automatically changed according to
its destination. If you don’t want this to happen, you
need to add a \$ mark.
• Column Absolute:         \$B19
– When it’s copied, column (B) cannot change but row (19)
• Row Absolute:            B\$10
– When it’s copied, column can change but row cannot
• Full Absolute:           \$B\$19
– When it’s copied, Nothing can change
Date Functions
• Efficiently handle time-consuming procedures
• Help analyze data related to the passing of time
• TODAY function places the current date in the
selected cell
– Updates when file is opened again
• NOW function displays current date and time,
side by side
VLOOKUP FUNCTION
• Used to gauge an entered value against a range of
corresponding values
• Used to convert numeric to letter grade..and has many
more applications
Syntax
=Vlookup(Cell to evaluate/Look up_value, Table range
,Column#)
• Cell to evaluate: cell that has value being Examined
• Table range: Cell range that contains the look up value
• Column#: This indicate the column number in the table
from which result /matching value is to be returned
A                    900 OR MORE
B                    800-899
C                    700-799
D                    600-699
F                    599 or Fewer
VLOOKUPTABLE

O              F

600            D

700            C         From Syllabus

800            B         Entered in a distant area in Excel Sheet
900            A
from the class website
• Auto fit the Column widths first
• In B2 enter the formula
=VLOOKUP(A2, A11:B15, 2)
• Try to enter different numbers in A2 and
observe how cell B2 changes.
Worksheet Appearance
• In B2, right click and select “Insert Comment”
and type “My grade in the class”
• Select A1:B1
– Change Font Color to White
– Change Fill Color to Black
• Copy the same settings of A1:B1 to A10
– Click in A1, then select Format Painter (tiny brush
icon), and click in A10
• Go to cell D1
• Insert tab
– Text to display: CS101 Site
• OK
• Try to click on the cell D1 once and see if the
web browser jumps to the specified link.
Payment Function - PMT
• Calculates the payment to be paid per month
at fixed amount and fixed constant interest
rate.
• Use it to determine if it’s something you can
afford (a car, a house, a boat, . . .)
• Syntax
=PMT(interest/12, payments, -financed)
Example_4(PMT function)
website
• Enter the following:
– B1 (House price)     1,00,000
– B2 (down payment) 5,000
– B3 (financed amount) =B1-B2
– B4 (% interest)      0.06
– B5 (years)           30
– B6 (number of payments) =B5*12
PMT function
• In B7 (Monthly Bill) type
=PMT(B4/12, B6, -B3)

• You should see \$569.57 a month for 360 months
• Now change period from 30 years to 15 years:
– You will see the monthly payment change by 232\$
30 years vs. 15 years
• 30-year model
569.57*360 = \$205,045.2
• 15-year model
801.28*180= \$144,299.58
• So you’re saving 60,745.68when you
go for the 15-year model.
Goal Seek
• Allows for a “what if” analysis scenario
• Allows setting a target number and can
manipulate another number to temporarily see
what its value should be to reach that specific
target
• So now let’s go back to our example and do the
following
 Let’s say that we want to get our monthly
payment down to \$675 and we need to know
how much to put down in order to clear the
loan in the same number of years…
Goal Seek
• Make cell B7 the active cell by clicking in it
• Select “Data” ribbon  Data Tools group 
What-If Analysis  Goal Seek

• Change the “To Value” 675
• “By Changing Cell:” B2 (it will be absolute
reference)
• hit OK
• Observe the new down payment in B2
Next class
• Continue with Microsoft excel chapter 3

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 4 posted: 1/18/2011 language: English pages: 21