Title: New Perspectives on Microsoft Office Excel 2003 Tutorial 2
1Microsoft Office Excel 2003
- Tutorial 2 Working With Formulas and Functions
2Use Excels functions
- You can easily calculate the sum of a large
number of cells by using a function. - A function is a predefined, or built-in, formula
for a commonly used calculation. - Each Excel function has a name and syntax.
- The syntax specifies the order in which you must
enter the different parts of the function and the
location in which you must insert commas,
parentheses, and other punctuation - Arguments are numbers, text, or cell references
used by the function to calculate a value - Some arguments are optional
3Work with the Insert Function button
- Excel supplies more than 350 functions organized
into 10 categories - Database, Date and Time, Engineering, Financial,
Information, Logical, Lookup, Math, Text and
Data, and Statistical functions - You can use the Insert Function button on the
Formula bar to select from a list of functions. - A series of dialog boxes will assist you in
filling in the arguments of the function and this
process also enforces the use of proper syntax.
4Math and Statistical functions
5Define functions, and functions within functions
- The SUM function is a very commonly used math
function in Excel. - A basic formula example to add up a small number
of cells is A1A2A3A4, but that method would
be cumbersome if there were 100 cells to add up. - Use Excel's SUM function to total the values in a
range of cells like this SUM(A1A100). - You can also use functions within functions.
Consider the expression ROUND(AVERAGE(A1A100),1)
. - This expression would first compute the average
of all the values from cell A1 through A100 and
then round that result to 1 digit to the right of
the decimal point
6Copy and paste formulas and functions
- Copying and pasting a cell or range of cells is a
simple, but highly effective means for quickly
filling out a large worksheet. - To copy and paste a cell or range
- Select the cell or range to be copied and then
click the Copy button on the standard toolbar - Select the cell or range into which you want to
copy the selection and then click the Paste
button on the standard toolbar - Once you are finished pasting, press the Esc key
to deselect the selection
7Copy and paste effects on cell references
- Copied formulas or functions that have cell
references are adjusted for the target cell or
range of cells. - For example, if cell G5 contains the formula
F5B5/B7, and you copy and paste this formula to
cell G6, the formula in cell G6 will be
F6B6/B8. - This may or may not be correct for your
worksheet, depending upon what you are trying to
do. - You can control this automatic adjusting of cell
references through the use of relative and
absolute references.
8Problems using copy and paste with formulas
- When Excel does not have enough room to display
an entire value in a cell, it uses a string of
these symbols to represent that value. - For example, the formula in cell J5 is
F5-(H5I5) and this was pasted into cell J6 by
updating the cell references there to
F6-(H6I6). - Cell G5 has the formula F5B5/B7 and cell G6
contains F6B6/B8. This is where things went
wrong. Sometimes this automatic update is very
useful and other times it does not give you the
desired result for your worksheet. - In this case, cells B5 and B7 should be
referenced in the formula in column G in all 240
payment period rows, but in column J, you want
the cell references to be automatically updated.
You can control this result using relative and
absolute references.
9Use relative references
- A relative reference is a cell reference that
shifts when you copy it to a new location on a
worksheet. - A relative reference changes in relation to the
change of location. - If you copy a formula to a cell three rows down
and five columns to the right, a relative
reference to cell B5 in the source cell would
become G8 in the destination cell.
10Use absolute references
- An absolute reference is a cell reference that
does not change when you copy the formula to a
new location. - To create an absolute reference, you preface the
column and row designations with a dollar sign
(). - For example, the absolute reference for B5 would
be B5. - This cell reference would stay the same no matter
where you copied the formula.
11Use mixed references
- A mixed reference combines both relative and
absolute cell references. - You can effectively lock either the row or the
column in a mixed reference. - For example, in the case of B5, the row
reference would shift, but the column reference
would not - In the case of B5, the column reference would
shift, but the row reference would not - You can switch between absolute, relative and
mixed references in the formula easily in the
edit mode or on the formula bar by selecting the
cell reference in your formula and then pressing
the F4 key repeatedly to toggle through the
reference options.
12Open the Insert Function dialog box
- To get help from Excel to insert a function,
first click the cell in which you wish to insert
the function. - Click the Insert Function button. This action
will open the Insert Function dialog box. - If you do not see the Insert Function button, you
may need to select the appropriate toolbar or add
the button to an existing toolbar.
13Examine the Insert Function dialog box
14Insert Function helpful tips
- In the Insert Function dialog box you can type in
a description of what you would like to do in the
Search for a function text box and then click the
Go button, and Excel will come up with some
suggestions for you. - You may also select a category from the drop-down
box, or choose All, which will list every
function in Excel alphabetically. - Once you make your selection and click the OK
button, another dialog box shows you all the
arguments for the function. - The arguments shown in boldfaced type are
required - While the cursor is in an argument's text box,
you can either enter a value or click a cell and
that cell will appear in the text box, saving you
the trouble of typing it
15The Average Function
- The average function is necessary to calculate
the average of a range of cells. - Like any other formula, the average function may
be copied across cells.
16Use Excel's Auto Fill features
- When you need to copy and paste a large number of
rows or columns, you can use a technique called
Auto Fill using the fill handle. - The fill handle is a small black square located
in the lower-right corner of a selected cell or
range. - When you drag the fill handle, Excel
automatically fills in the formulas and formats
used in the selected cells. - The same rules for relative, absolute, and mixed
references apply for Auto Fill as for copy and
paste.
17Auto Fill features
- The Fill behavior depends on the date(s) with
which you start. - If you select two or more dates separated by a
month, then Excel will increment the auto filled
dates by a month as well. - If you select only one date, Excel will increment
the dates by one day only. - When you drag the fill handle, a small Auto Fill
Options button appears to the lower right of the
selected cell or range. - Common options are Copy Cells, Fill Series, Fill
Formatting Only and Fill Without Formatting - If you are using the Auto Fill technique for
dates, you have additional options to either Fill
Days, Fill Weekdays, Fill Months or Fill Years.
18Using Auto Fill
- To use the Auto Fill feature, select the cell
range that contains the values and/or formulas
you want to copy. - Click and drag the fill handle in the direction
you want to copy and then release the mouse
button. - If needed, click the Auto Fill Options button,
and then select the Auto Fill option you want to
apply to the selected range.
19An example of Auto Fill
20Auto Fill Options button menu options
- The Copy Cells option will copy all values and
formulas into the selected range as well as the
formats used to display those values and
formulas. - The Fill Formatting Only option copies only the
formats used to display values or formulas
without copying the values and formulas
themselves. - The Fill Without Formatting option copies only
the values and formulas without any of the
formats used in the source range.
21Date Functions
22Excel's date functions
- Excel stores dates as integers, where the integer
value represents the number of days since January
1, 1900. - For example, the integer value for the date
January 1, 2008 is 39448 because that date is
39,448 days after January 1, 1900 - You typically do not see these numbers, because
Excel automatically formats them to appear in a
date format. - This method of storing dates allows you to work
with dates the same way you work with numbers. - Excel's commonly used date functions are DATE,
DAY, MONTH, NOW, TODAY, WEEKDAY and YEAR.
23The TODAY and Now functions
- The TODAY and NOW functions always display the
current date and time. - You will not normally see the time portion unless
you have formatted the cell to display it. - If you use the TODAY or NOW function in a cell,
the date in the cell is updated to reflect the
current date and time of your computer each time
you open the workbook.
24Use a formula to enter the date
25Excel's financial functions
- Financial functions are very useful to calculate
information about loans. - Common functions are FV, IPMT, PMT, PPMT and PV.
- All these financial functions will use similar
arguments that differ based upon which function
you are using. - Think of the arguments as members of an equation
- The arguments represent the values of the
equation that are known and the function provides
the solution for a single variable, or unknown,
value
26Use the financial functions
- The FV function calculates the future value of an
investment based on periodic, constant payments
and a constant interest rate per period. - The IPMT function provides the interest payment
portion of the overall periodic loan payment. - The PMT function calculates the entire periodic
payment of the loan. - The PPMT function calculates just the principal
payment portion of the overall periodic payment. - The PV function calculates the present value of
an investment.
27Financial Function descriptions
28Recognize optional arguments
- In the preceding figure, note how rate and nper
are arguments for each function. - For some of the functions, the final two
arguments of each function are in brackets. These
represent optional arguments, meaning if you do
not enter anything, the default values for these
arguments will be used. - For example, note the PMT function has fv and
type as its final two arguments, which are
optional. The assumed values, if no others are
supplied, are 0 for both - Arguments without brackets do not have default
values, so you must supply values or cell
references in order for the function to be able
to return a value.
29Use the Insert Function dialog box to enter
function arguments
30Create logical functions
- A function that determines whether a condition is
true or false is called a logical function. - Excel supports several logical functions such as
AND, FALSE, IF, NOT, OR and TRUE. - A very common function is the IF function, which
uses a logical test to determine whether an
expression is true or false, and then returns one
value if true or another value if false. - The logical test is constructed using a
comparison operator that compares two expressions
to determine if they are equal, not equal, if one
is greater than the other, and so forth. - The comparison operators are , gt, gt, lt, lt, and
ltgt - You can also make comparisons with text strings.
You must enclose text strings within quotation
marks.
31Using the If function
- The arguments for the IF function are
- IF(logical_test,value_if_true,value_if_false)
- For example, the function IF(A110,20,30) tests
whether the value in cell A1 is equal to 10 - If it is, the function returns the value 20,
otherwise the function returns the value 30 - Cell A1 could be empty or contain anything else
besides the value 10 and the logical test would
be false therefore, the function returns the
value 30 - To insert an IF function, click the Insert
Function button and search for the IF function,
then click OK. - When the Function Arguments dialog box appears,
simply fill in the arguments.