Excel 2007, working with formulas and functions

 

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 is 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, iinterest 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.