Transcript Function
Exploring Microsoft Excel
2000 – 2003
Part II
Natalia Mosina-2005
BOOKS
Robert Grauer and Maryann Barber
Using Excel Effectively
Enter cell addresses in formulas and
functions by pointing
Use the mouse to select the cell(s)
More accurate than typing cell references
Use the fill handle to copy
Select the cell(s) and drag to copy to a
destination range
Shape of the cursor must be a crosshair to
use the fill handle to copy cells.
Insert comments
Pointing
Use the mouse to select the cells to
be included in the formula
Notice the color coding between the
borders around the selected cells and
the formula in the formula bar
Using the Fill Handle (instead of
copy/paste) to copy formulas
Select cells E2:H2. Dragging
the fill handle will copy all four
cells to lower rows.
Border around selected
area. Release the mouse
and formulas are copied
Practice with fill handle to copy
To master any technique it is important to
practice.
Let’s see how this fill handle works using
simple sample data.
Use pointing instead of typing to enter the formula
Select C1. Point to the low right corner and notice how the pointer
Changed to the crosshair. Click and drag down till C5. Release mouse.
Copy by dragging. Watch out for crosshair.
Release mouse to finish copying.
Inserting Comments
Comments provide
explanation for values
and/or descriptions of
formulas
Office and Internet
The Internet and World Wide Web are
thoroughly integrated into Office through two
basic capabilities.
You can insert a hyperlink into any Office
document, then view the associated Web
page from within the document.
You can also save any Office document as a
Web page, which in turn can be displayed
through a Web browser.
Excel and the Internet
Insert a hyperlink into a worksheet
Hyperlink: a reference to another document
Save a workbook as a Web page
A “web page” is another name for an HTML
document
A Web Page
Page is viewed
through a Web
browser
Clicking the hyperlink
will take you to the
designated Web site
Inserting a Hyperlink
If the cell is blank, type the
hyperlink text in the Text to
Display box
Select the cell that
will contain the
hyperlink #1
Enter the Web address
(URL) of the site you wish to
open
The Save As Web Page Command
Enter the name for
your Web page
By default, the Single
File Web Page format
is selected.
Exercise: Internet and Excel.
This exercise requires that you have an Internet
connection to test the hyperlink.
You will hide first column in your Better Grade
Book file and will insert Student ID column.
You will insert a hyperlink into an Excel
workbook, then follow the link.
Next, you will save a workbook as an HTML
document (under the new name Better Grade
Book as Web Page) and view it using your
browser.
Use a Handout as a guide in this exercise.
http://acc6.its.brooklyn.cuny.edu/~nmosina
Enter the above URL
Round-Trip HTML
Edit with Microsoft Excel
button lets you start
Excel and edit the
worksheet
Selecting a Non-Contiguous Range
Drag through cells to
select destination
range
Hold the Ctrl key, then
select additional cells
Conditional Formatting
Use this dialog to set
criteria, in this case <0
Click Format
button to open
Format Cells
dialog
Select cells to apply
conditional formatting
Spreadsheets in decision making.
Excel can help you in decision making.
We will continue working with predefined
functions.
We will consider financial functions such as
PMT – function that determines monthly
payments on a loan, and FV – future value.
We will introduce statistical functions and
conditional functions.
We will introduce the Goal Seek command.
Using Functions
Function – a predefined computational task;
a predefined formula.
Requires (0 or more) arguments
(separated by commas) as input.
Arguments - values the function uses to
calculate answers
Returns a value as output.
Excel has more that 100 different functions in
various categories. Financial functions are
very important in business.
The PMT Function
Calculates a periodic payment, such as a car or
mortgage payment
Based on:
Amount financed
(amount of loan)
Interest rate
per period
(annual rate divided by 12)
Number of periods
(n years*12 months/year)
Using the PMT function
Amount financed
expressed as a negative
number
(Bank’s view):
The money is lent to you
and represent an outflow
of cash from the bank.
Number of (monthly)
payments
(monthly)
Interest rate
Amount financed, (yearly) interest
rate, and the term (in years), are all
isolated as assumptions. One or
more assumptions can be changed
The FV function
Returns the future value of a series of payments
For example, contributions to your 401K or IRA (under
either plan, an individual saves for his or her retirement
by making a fixed contribution each year. The money is
allowed to accumulate tax-free until retirement).
FV based on:
Number of periods
Expected rate of return per period
Amount invested each period
FV deals with constant periodic payments and a
constant interest rate.
Using the FV Function
Amount of contribution, rate of return,
and years contributing are all expressed
as assumptions
Inserting a Function
Use the Insert Function command from the
Insert menu
Use the list box to select the name of the
function
Functions are categorized
Let the Wizard help you enter the arguments
Point to enter cell references
Use the Collapse button to collapse the dialog
box
The Function Wizard
Enter arguments into
text boxes
Collapse button
shrinks dialog box
if necessary
Value returned by the
function (answer) is
displayed
The Goal Seek Command
Allows you to set an end result and vary an
input (assumption) to produce that result
Only one input can be varied at a time
All other assumptions remain constant
For example, set a desired monthly car
payment
Vary the amount financed
Interest rate and number of months remain the
same
Using the Goal Seek Command
Enter the cell
containing the
desired result
Enter the
desired value
Enter the cell
containing an
input to change
Hands-on Exercise 1
Title of Exercise: Basic Financial Functions
Objective: To illustrate the PMT and FV
functions; to illustrate the Goal Seek
command.
Input file: None
Output file: Basic Financial Functions
Use Handout for step by step instructions
Statistical Functions
MAX, MIN, and AVERAGE functions
Return highest, lowest, and average values
from an argument list
Argument list may include cell references, cell
ranges, values, functions, or formulas
Cells that are empty or contain text are not
included
COUNT and COUNTA functions
COUNT returns number of cells containing
numeric entries or formulas that return a
number
COUNTA also includes cells with text
Using Functions versus Formulas
In general, use functions instead of formulas
Functions can use ranges that are adjusted as
rows or columns are deleted or added within
the range referenced by the function
With formulas
Adding a row adjusts the cell references in the
formula, but does not include the new row in the
formula
Deleting a row may cause a #REF error message
(it means that a referenced cell has been deleted)
The IF Function
Enables decision making in a worksheet
Requires three arguments:
A condition
A value if the condition is true
A value if the condition is false
Condition must be able to be evaluated as
true or false
Uses relational operators (=, <, etc.)
Using the IF Function
Value_if_true entered as a
value. Value_if_false entered
as a cell reference
The VLOOKUP function
Allows Excel to look up a value in a table and
return a related value
Requires three arguments:
the numeric value (or cell) to look up
the range of the table
the column number containing the value you
want to return
Using the VLOOKUP Function
Look up the value found
in cell I4, in this case,
the semester average
This argument tells the
function where to look.
Absolute references
used for the table
Look in the second
column of the table,
NOT in column J
Working With Large Worksheets
Scrolling causes the screen to move horizontally or
vertically as you change the active cell
Drag the horizontal or vertical scroll bars
Click above or below vertical scroll bars
Click to the left or right of horizontal scroll bars
Freezing Panes allows row and column headings to
remain visible while scrolling
Hiding rows and columns makes rows and columns
invisible on the monitor or when printed
Freezing Panes
As you scroll
back up, rows 48 will become
visible again
Printing Large Worksheets
Page Preview command (View menu) lets
you see where the page breaks are
Page Setup command (File menu) lets you
change how the sheet prints
Change from portrait (8 ½ x 11) to landscape
(11 x 8 ½)
Change margins
Scale the worksheet to print on one sheet
The AutoFilter Command
Allows you to display a selected set of rows
within a worksheet
Displays rows that meet selected criteria
Other rows are hidden, not deleted
Select Filter then AutoFilter from the Data
menu
Select criteria from the dropdown
Using the AutoFilter Command
Click the dropdown on
the Homework column,
then select Poor as the
criteria