Transcript CGS 2060/2100 Recitation - Florida State University

CGS2060/2100 Recitation
Spreadsheets
Spreadsheet Software
 Software designed to
perform complicated numeric
calculations rapidly and
accurately.
 Provides tools that assist in
understanding the
relationship that exists
between numeric data such
as cost and profit.
 Ideal for examining “What If”
scenarios.
A Spreadsheet
 A spreadsheet consists of a grid of “cells” that can
contain text, number (values), equations, or
functions.
3. Values are
used in
equations and
functions
2. Text is
used for
labels
4. Equations
and functions
operate on
values
1. Cells are identified by
column letter and row
number. This is cell E3.
A Spreadsheet
 Column width, row height, font and cell format
can all be adjusted using commands in the
format menu.
2. Cell fill
color and
border color
and weight
can be
specified.
1. Font type,
color, size
and weight
can be
changed
3. Numbers
can be
formatted as
currency,
percentages,
dates, etc.
Formulas (equations)
 Calculations always begin with an equal sign followed
by the formula or function.
 After typing the formula, pressing Enter displays the
results of the formula in the cell – the formula
remains displayed in the formula bar.
2. Cell E3
shows the
results of
equation
1. The
formula bar
shows the
equation
typed in cell
E3
Formulas
 What formula would you type in cell B6 to add up the
January profits for the three divisions?
It’s common for
new users to
forget to type the
equal sign for
formulas.
= B3+B4+B5
= B3+B4+B5
Functions
 What if you have a long list to
add up?
 Rather than having to type
lengthy formulas, Excel
provides functions.
 In this case you could use
=sum(B3:B17)
The colon is used to
specify a range of
cells, B3 through B17
Functions provide shorthand
methods for specifying
lengthy or complicated
formulas and calculations
Functions
 Fortunately, you don’t need to
memorize all the functions to
use them. All you need to so is
type the equal sign in a cell to
access the functions menu.

Click the Dropdown Arrow to
access the list of functions.
Functions
 Fortunately, you don’t need to
memorize functions to use
them. All you need to so is type
the equal sign in a cell to
access the functions menu.


Click the Dropdown Arrow to
access the list of functions.
Click the desired function.
Functions
 A dialog box is displayed to assist you
in setting up the function.
Enter cells,
or cell range
here (or
select them
with your
mouse)
Click OK to
insert the
function in
the
spreadsheet
Functions
 Excel provides
hundreds of functions
for many categories of
problems



Date and Time
now()
Average
=average(A1:A12)

Minimum
=min(A1:A12)

Max
=max(A1:A12)
Logic
if()
Sum
=sum(A1:A12)
Math and Trig
sqrt()


Statistical
growth()

we’ll use in this class
include
Financial
rate()

 Common functions that

If
=if(A1>12, “okay”)
Copying Functions and Equations
 If you copy a function from one cell to another, the
cells referenced in the function will automatically
change to accommodate the new location. This is
called a relative reference. For instance, below
=SUM(B3:B5) becomes =SUM(C3:C5) when copied.
Copying Functions and Equations
 By placing a dollar sign before the column letter or
row number in a formula you can prevent the cell
reference from changing when copied. This is called
an absolute reference. Below, B3, the interest
amount, has been made an absolute reference.
Copying Functions and Equations
 You can copy and paste a function using commands
in the edit menu.
 If you wish to paste a function several times in
sequence, you can use the cell’s fill handle to drag
and copy a formula or equation down, up, right, or
left.
Fill
handle
What if…?
 The purpose of spreadsheets is to provide users with
insight and an understanding of complicated
numerical data and the relationships between that
data. What if the SouthEast division earned $20,000
in profits in January?
A change here.
What if…?
 The purpose of spreadsheets is to provide users with
insight and an understanding of complicated
numerical data and the relationships between that
data. What if the SouthEast division earned $20,000
in profits in January?
Effects the data
here, here
& here
What if…?
 Excel provides a number of “what if” tools. The “solver”
tool for example allows you to work backwards to find
out what needs to be done to meet a stated goal.
Here the solver
is asked: What
monthly profits
must each of the
divisions earn for
us to meet a goal
of $200,000 in
total profits.
Another “What If” Scenario
Sharon's Weekly Budget
Budgeted for this week: $ 100.00
Mon
Breakfast
Lunch
Snack
Dinner
Entertainment
Daily Total
$ 2.75
$ 3.00
$ 0.75
$ 7.00
$
$ 13.50
Tue
$
$
$
$
$
$
3.50
4.50
8.50
16.50
Wed
Thu
Fri
$
$ 2.00
$ 5.00 $ 3.25
$ 2.00 $ 1.50
$ 15.00 $ 4.50
$ 4.00 $ 8.00
$ 26.00 $ 19.25
Weekly Total
Left Over
$ 2.75
$ 3.75
$
$ 12.00
$ 16.00
$ 34.50
$ 109.75
$ (9.75)
 In this example, Sharon needs $40 on Saturday to be able to
join her friends for a weekend trip. Her current budget leaves
her with -$9.75. She could use this sheet to tweak her spending
during the week so that she would have money left over for the
weekend, or use a tool like the Solver to assist in finding areas
to reduce her spending.
Other Cool Stuff: Charts
 Select a range of cells and click the Chart
Wizard button to create a bar, line, or pie
chart.
Other Cool Stuff: Pivot Tables
 Using the Pivot Table Wizard you can study
the data in a spreadsheet from different
organizational views.
Summary
 This short presentation has introduced you to
basic Excel concepts and features:








Cells, labels, and values
Formatting text and cells
Changing row height and column width
Formulas and functions
Relative and absolute cell references
Copying functions
“What if” tools
Charts and Pivot Tables