Transcript kjjhghgff - Miami-Dade County Public Schools

Technology Basics
Creating Worksheet
Formulas
Understand Formulas


Equations used to calculate values in cells
are called formulas.
Formulas consist of two components:




An operand
An operator
All formulas begin with an equal sign (=).
Formulas may contain from one to several
cell references.
2
Commonly Used Formula
Operators
This table shows some of the operators typically used in
Excel formulas.
Mathematical
Operation
Operator
Addition
+ (plus sign)
Subtraction
― (minus sign)
Multiplication
* (asterisk)
Division
Percent
/ (forward slash)
% (percent sign)
3
Evaluating Complex Formulas
Formulas with more than one
operator
are called
complex
formulas. The order of evaluation
for complex formulas is shown
below. Some samples of complex
formulas are shown at right.
Multiplication and Division are
performed before addition and
subtraction.
 Calculations are performed from
left to right.
 Parentheses can be used to
change the order of evaluation.

Formula
Result
=6+4*4
6+16=22
=6*4+2
24÷2=26
=6-4/2
6-2=4
=6/2+4
3+4=7
=(6+4+*4
10*4=40
=(6*4)-(10/2)
24-5+19
4
Create a Formula

You can enter a cell address into a
formula in two different ways:

Key the cell address.
• The column reference can be uppercase or lowercase.




Point to the cell with the pointer.
As you enter cell references, Excel color
codes the cell borders and the cell
references.
The formula is displayed in the Formula
bar as you type it.
When you press Enter, the result of the
formula will display in the cell.
5
Entering a Formula
This figure shows a formula being entered into cell E3. Note the
color coding for cells B3, C3, and D3 and the same color coding
for each cell reference used in the formula in cell E3. Also note
that the formula being entered is shown in the Formula bar.
Cell borders and cell references are color coded.
6
Show the Formula Result
Once you press the Enter key, the result of the formula
calculation will display in the cell containing the formula.
If you select the cell, as shown here, the formula itself is
visible in the Formula bar.
7
Identify and Correct Formula
Errors


If Excel cannot properly perform a
calculation, an error value will display in
the cell with the formula.
The error may occur because





A cell contains text instead of a numeric value.
A cell referenced in the formula contains an
error.
A formula tries to divide by zero.
The cell is not wide enough to contain the result
value.
Excel has an AutoCorrect feature that tries
to correct errors that occur while typing the
formula.
8
Use the Help System to Find
Formula Errors
You can use the Excel Help system
to find information on formulas and
finding errors in formulas. Click the
Help menu, then choose Microsoft
Excel Help. Click the Contents tab to
see a list of topics, as shown to the
right.
Click the Plus sign for the Creating
and Correcting Formulas topic to see
additional topics on formulas.
9
Use Trace Error to Fix
Problems in Formulas
Excel has a Trace Error feature that you can use to obtain
information on formula errors. To use it, click the pointer in a
cell that has an error code displayed. Point to the Trace Error
button that will display to the left of the cell.
When you point to the
Trace Error button, a
ScreenTip
will
appear
with information about
the error. Click the down
arrow on the Trace Error
button to see the menu
shown here. Click the
Show Calculation Steps
option.
10
The Evaluate Formula
Windows
When you click the Show Calculation Steps option on the Trace
Error menu, this dialog box will appear showing you what values
are being used for the formula. You can clearly see here that
one of the fields contains a text value instead of a number.
11
Use the AutoSum Feature



AutoSum allows you to quickly identify a
range of cells to be used in a formula.
When you click the AutoSum button,
Excel determines the most logical column
or row of adjacent cells to sum.
After identifying the cell range, Excel
creates a function formula to calculate
the sum of the cell range.


Be aware that Excel does not always properly
identify the correct cell range.
If so, you can drag to identify the correct
range, then press Enter to fix the formula.
12
A Function Formula
A function formula contains three parts.
•The equal sign
•The function name
•The argument
Note that a colon is used to
indicate that this is a cell
range.
13
An AutoSum Example
In this figure, cell E6 was selected and then the AutoSum button
was clicked. Excel scanned the worksheet and then drew an
animated marquee around the cell range E3 # E5, indicating that
this is the range to be summed in cell E6.
However, the intent was to sum cells B6 through D6. To correct
this, click in cell B6, drag through to cell D6, and press the Enter
key. Excel will automatically correct the formula to read
=SUM(B6:D6).
14
Use the AutoFill Command
to Enter Formulas


AutoFill can be used to copy formulas up,
down, left, or right.
Cell references are formatted as relative
cell references by default.


That means that, when the formula is copied
to another cell, the cell references will be
adjusted relative to their new location.
Use the fill handle to drag a formula in a
selected cell to a new adjacent cell or
multiple adjacent cells.

Examine the formula for the new cells in the
Formula bar to see the updated cell
references.
15
Use Absolute Cell References


An absolute cell reference does not change when
a formula is copied or moved to a new location.
You create an absolute reference by inserting a
dollar sign ($) before the column or row
designator.

For example, =B5+$A$3
• If this formula was copied to a new cell, the B5 reference
would automatically update to a new cell number, but the
$A$3 would not change at all.

A mixed cell reference is created when the
column or the row is absolute, but not both of
them.

For example, $A3
16
Display Formulas in the
Worksheet



Data and formulas should always be
checked to ensure that they are both
accurate.
In a worksheet with many formulas,
clicking each cell with a formula to display
it in the Formula bar can be tedious and
time consuming.
It is easier to simply display all formulas.


Press and hold down the Ctrl key, then press
the single left quotation mark (usually upper
left on the keyboard).
All formulas will be displayed on the
worksheet grid.
17
Worksheet with Formulas
Displayed
This figure shows a worksheet in which the formulas have been
displayed, making it much easier to find and verify them.
18
Audit Formulas on the
Worksheet


In a worksheet with many formulas,
verifying all cell references can be very
time consuming.
The Formula Audit toolbar contains tools
that make it easy to trace cell references.


It can also display precedent and dependent
cells.
Open the Formula Auditing toolbar shown
below from the View/Toolbars menu and
option.
19
A Worksheet with Auditing
In this figure, cell C10 was selected and the Trace Precedents
button was clicked on the Formula Audit toolbar. The arrow that
runs from cell B7 # D7 marks these cells as the ones that provide
the data to the formula. When the Trace Dependents button is
clicked, the arrows are drawn from C10 to cells E3 # E7 to show
that these cells depend on the data in cell C10.
20