Tutorial 3
Working with Formulas and Functions.

Objectives

a. Copy formulas
b. build formulas containing relative, absolute, and mixed references
c. Review function syntax
d. Insert a function with the Insert Function dialog box
e. Search for a function
f. Type a function directly in a cell
g. Use AutoFill to fill in a formula and complete a series.
h. Enter the If logical functin
i. Insert the date with the today function
j. Use the PMT financial function to calculate monthly mortage payments

1. Understanding cell references
a. Relative reference
- wheh copying formulas the formula will change it reference to the cell copied to. For example if you copy the formula =Sum(b1:b10) to column C and D the cell reference will change relative to the cell copied to.  In column C the formula will now be =Sum(c1:c10) and in column D to =sum(d1:d10)
b.Absolute reference- there are times when you do not want the formula copied or part of it copied to change releative to the celll copied to.  This is done by placing the $ in front of  the column and or row reference.  For example =sum($b$1:$b$10) would keep the formula absolute and it would not change when copied.
c. Mixed reference - is one in which part of the formula is  absolute and part relative. for example =$b2 would keep the column absolute but the row would change.
d. Function key 4 is the absolute key.  Use the f4 key to cycle through the references.

2. Working with functions- every function has syntax rules, which specifies how the fucntion should be written.
a. Function(argument 1, argument 2, argument...) where functions is the name of the funciton and arguments are the numbers, text, or cell references used by the function to return a value. Not all functions have arguments and some functions have optional arguments when not used that revert to the default.

Excel Function Categories
Cube Retrieve data from multidimensional database involving online analytical process
Database Retrieve and analyze data stored in databases
Date & Time Analyze or create data and time values and time intervals
Engineering Analyze engineering problems
Financial Have financial applications
Information Return information about the format,  location, or contents of worksheets cells
Logical Return logical (true/false) values
Lookup & Reference Look up and return data matching a set of specified condiitions from a range
Math & Trig Have math and trigonometry applications
Statistical Provide statistical analyses of a set of data
Text Return text values or evaluate text.
   
 

a.. Date and Time Functions: Remember all functions begin with =.

Function Description
Date(year, month, day) Creates a date value for the date represented by the year, month, and day arguments.
Day(date) Extract the moth number from the date value, where 1= Jan, 2=Feb
Month(date) Extracts the month number from the date value, where 1=January
Year(date) Extracts the year number from the date value
Weekday(date,[return_type]) Calculates the day of the week from the date value, where 1=Sunday
Now() Displays the current date and time
Today() Displays the current date


b. functions are widely used in the world of business.

Function Description
PMT(rate,nper,pv,[fv=0],[type=0]) rate is interest rate per payment period- nper is total number of payments - pv is the present value of the loan and must be entered as a negative number -fv indicates the future value of the loan with default of 0 - type specifies when payment is made.
PV(rate,nper,pmt,[fv=0],[type=0]) Calculates the present value of a loan or investment based on periodic, constant payments.
NPER(rate,pmt,pv,[fv=0],[type=0]) Calculates the number of periods required to pay off a loan or investment
RATE9nper,pmt,pv,[fv=0],[type=0]) Calculates the interest rate of a loan or investment based on periodic, constant payments.
Average(agruments) Calcualtes the average of a collection of numbers.
Count(arguments) Counts how many cells in a range contains numbers.
CountA(agruments) Counts how many cells are not empty in ranges.
Int(number) Displays  the integer protion of a number.
Max(arguments) Calculates the maximum value of a collection of numbers.
Median(agruments) calculates themedian, or  middle value of a set ofnumbers.
Min(agruments) Calculates the minimum value of a set of numbers.
Rand() Returns a random  nuber between 0 and 1
Round(number, num_digits) Rounds annumber to specified number  of idgits.
Sum(arg) Adds a collection of numbers.

c. Logical function test or evaluates whether a condition in the workbook is true or false.
d. The if function =if(logical_test, value_if_ true, value_if_false) is a selection control structure in programming.
Example: =if(a1>60,"You passed","You failed")

e. Comparison operators

> greater than A > B
< less than A < B
>= greater than or equal to A >= B
<= less that or equal to A <= B
= equal to A = B
<> not <> A


e. Functions can be nested inside of each other. A common error in nesting is not including all of the parentheses.
f. Insert of fucntion into a cell -  select the cell where you want the function inserted, click formulas tab on ribbon.  Click Matrh & Trig Button, scroll down and select sum, you will see:
Function dialog box
click on sum, you will see:
Sum function dialog box

Click in worksheet and select the range of cells you want.  You can have up to 255 ranges of cells in the function. You can also type the functions directly into the cells that you want.

3.. Fill handle-Auto Fill - the small black in the lower-right corner of a selected cell or range is called the fill handle which enables you to copy the contents of selected cells simply by dragging the fill handle.
. Auto Fill options button - Copy Cells, Fill Formatting Only, Fill without Formatting.

 Auto Fill Series

Type Initial Selection Extended Series
Values 123
2,4,6
4,5,6
8,10,12
Dates and Times Jan
January
Jan, Apr
15-Jan, 15-Feb
12/30/2005
12/31/2005,1/31/2006
Mon
Monday
11:00 AM
Feb, Mar, Apr,...
February, March, April
Jul, Oct, Jan, ...
15-Mar, 15-Apr,15-May, ...
12/31/2005,1/1/2006,1/2/2006
2/28/2006, 3/31/2006, 4/30/2006
Tue, Wed, Monday, ....
Tuesday, Wednesday, Thursday, ...
12:00 PM, 1:00 PM, 2:00 PM
Patterned Text 1st Period
Region 1
Quarter 3
Qtr3
22222sd Period, 3rd Period, 4th Period
Region2, Region 3, Region 4, .....
Quarter 4, Quarter 1, Quarter 2, ....
Qtr4, Qtr1, Qtr2, ......

.

 Loans - Principal is the amount of money being loaned, interest is the amount charge for lending the monty. Simple interest is the interest paid is equal to a percentage of principal for each period that the money has been lent. Compound interest in which the interest paid is calculated on the principal and any previous interest payments that have been added to the principal.