Technology Basics

1
Technology Basics

• Creating Worksheet Formulas

2
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.

3
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 / (forward slash)
Percent (percent sign)
4
Evaluating Complex Formulas
Formula Result
644 61622
642 24226
6-4/2 6-24
6/24 347
(644 10440
(64)-(10/2) 24-519

• 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.

5
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 lower-
  case.
• 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.

6
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.
7
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.
8
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.

9
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.
10
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.
11
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.
12
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.

13
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.
14
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(B6D6).
15
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.

16
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, B5A3
• If this formula was copied to a new cell, the B5
  reference would automatically update to a new
  cell number, but the A3 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

17
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.

18
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.
19
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.

20
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.