1. Copy formulas.

2. Build formulas containing relative, absolute, and mixed references.

3. Review function syntax.

4. Insert a function with the insert function dialog box.

5. Search for a function.

6. Type a function directly in a cell.

7. Use Auto Fill to fill in a formula and complete a series.

8. Enter the If logical function.

9. Insert the date with the today function.

10. Calculate monthly mortgage payments with the PMT financial function.

Important Points:

1. **Functions are built in programs** with the following syntax:
Function(argument, optional argument) Example: sum(c1:c15) sum is the name of
the function: c1:c15 is the range of cells that make up the argument. The
argument is what the function works on.

2. **350 functions built into excel:** Database, Date and Time, Engineering,
Financial, Information, Logical, Lookup and reference, math and trigonometry,
statistical, and text and data functions are the categories:

Functions |
Description |

Average(cell ref or sum) | Calculates the average of a collection of number who are in cells |

Count(cell ref) | Calculates the total number of values that are numeric, text or cell ref. |

Max(cell ref or num) | Calculates the maximum number of a group. |

Median(cell or num) | Calculates the median (center number) |

Min(cell or num) | Calculates the minimum number. |

Round(number,num-digits) | Rounds a number to specified number of digits. Number represent the number and num-digits represent the number of places to be rounded to. |

Sum(cell or num) | Calculates the sum of a collection of numbers. |

3. **Copying and Pasting a cell or range.** Select cell or range, click
copy, select the cell or range you want to copy to, click paste.

4. **Relative reference i**s a cell reference that will change relative to
the cell copied to.

5. **Absolute reference** is a cell reference that will not change when
copied. indicated with $.

6. **Mixed reference **contain both in the formula when copied.

7. **F4 is your absolute reference key or you can type the $ directly into your
formula. **

8. **Other paste options:**

Option |
Description |

Formulas | Pastes the formula(s), but not the formatting, of the source cell range |

Values | Pastes the value(s), but not the formula(s) or formatting, of the source cell range |

No Borders | Pastes the formula(s) and formatting of the source cell range, but not the format f cell range borders. |

Transpose | Pastes the formula(s) and formatting of the source cell range, except changes the orientation so that row in the source cell range become columns, and columns become rows. |

Paste Link | Pastes a link to the cell(s) in the source cell range, including the formatting used |

Paste Special | Opens a dialog box displaying more paste options. Let you specify what part of cell you want to paste and specify what mathematical operation to be performed. |

9. **Changing Magnification** - click view, click zoom, click option
button for percent of magnification, click ok or click the zoom list arrow and
click the percent you want.

10. **Insert Function click **the formulas tab on the ribbon,
select a category, select function, enter the arguments, click ok.

11. **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.

12. **Auto Fill options button -** Copy Cells, Fill Formatting Only, Fill
without Formatting.

13. **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, ...... |

14. **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 |

15.** Financial 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. |

16. **Logical function** test or evaluates whether a condition in the
workbook is true or false.

17.** 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")

18. **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 |

19. Loans - **Principal** is the amount of money being loaned, i**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.