Transcript Slide 1

DAY 4:
MICROSOFT EXCEL – CHAPTER 2
Madhuri Siddula
[email protected]
January 26, 2015
FORMULA BASICS
CELL REFERENCES
• Relative
• Absolute
• Mixed
DATA
RELATIVE REFERENCE
• Amount financed = House Cost – Down Payment
ABSOLUTE REFERENCE
• Rate Per Period = Mortgage Rate / Pmts Per Year
MAKE B5 CONSTANT
THROUGHOUT COLUMN F
• B5 can be made constant by:
– Enter symbol “$” before and after B
– Go to the formula and take cursor to B5 and
press F4
MIXED REFERENCE
• # of payment periods = Years * Pmts Per Year
CIRCULAR REFERENCE
Correct the formula: % financed = Amount Financed / House Cost
FUNCTIONS
FUNCTION CATEGORIES
•
•
•
•
•
•
Date and Time
Financial
Logical
Lookup & Reference
Math & Trig
Statistical
INSERT A FUNCTION
BASIC MATH AND STAT
FUNCTIONS
•
•
•
•
•
•
SUM
MIN
MAX
COUNT
AVERAGE
MEDIAN
DATE FUNCTIONS
•
•
•
•
•
•
•
•
Today
Now
Date
Day
Edate
Eomonth
Month
Year
LOGICAL, LOOKUP AND
FINANCIAL FUNCTIONS
•
•
•
•
•
•
Equal to
Not equal to
Less than
Greater than
Less than or equal to
Greater than or equal to
USE IF CONDITION TO ANALYSE LOGICAL
OPERATIONS
VLOOKUP FUNCTION
Class Grades
Grading Lookup Table
Range
Grade
Range
Grade
90-100
A
0
F
80-89
B
60
D
70-79
C
70
C
60-69
D
80
B
Below 60
F
90
A
VLOOKUP(lookup_value, table_array, col_index_number, [range_lookup])
EXAMPLE
PMT FUNCTION
• Financial function to calculate monthly
payment for the loan taken based on
several attributes:
– Interest rate per period
– Total # of payments for the loan
– Present value
– Future value
– Type
LOGICAL FUNCTION - IF
• If %down >= downpayment rate
– True - Monthly PMI should display “Greater”
– False - Monthly PMI should display “Less”
LOGICAL FUNCTION - IF
RANGE NAMES
CREATE A RANGE NAME
USE A RANGE NAME IN A FORMULA