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:
click on sum, you will see:
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.