Writing Formulas and Cell Addressing in Excel

1
Writing Formulas and Cell Addressing in Excel

• Objectives
• To understand Excel formulas are written
• Data precision vs. Data display.
• Excel operators
• Order of precedence of operations
• To understand how formulas are copied
• Relative Absolute Cell Referencing

2
The Power of using Spreadsheet Applications
B2C2
Copy formula from D2 to D3

• Each entry can be related to other values by
  including cell referencing in formulas. A formula
  is a sequence of values and/or cell references.
• Formula values are automatically updated when a
  referenced value changes
• Formulas can be copied to perform the same
  operations on a corresponding set of data
• Charts can be easily generated

3
Formulas

• A formula is a sequence of values, cell
  references and operators that produce a new
  value.
• E8 3(E10 - E11)
• Formulas always start with an equal sign
• In addition a formula can also contain built-in
  functions like SUM, AVERAGE, IF, COUNTIF, etc.
  Sum(A2A8)2

4
Things you need to know when writing formulas in
Excel

• Data precision vs. cell display
• Types of operators that can be used
• Order of precedence of operators

/ -
B2B3B1/B82
5
Precision number of decimal places used in
calculations and stored in the computer.
Formatted Display the number that appears in a
cell

• Type in a cell 1/8 display in cell
• What value results for each - if multiplied by
  1000?

• Column A and B are adding the same numbers - with
  different displays.
• Does the addition appear to be correct in col B?

A
B
1
21.4
21
2
51.3
51
3
98.1
98
4
170.8
171
5
6
Formatting affects display not the precise value
Formatting Number Group

• Percent Decimal Display
• Currency Commas

7
Values can also be used to display dates

• Dates are values that can be entered in several
  formats January 27, 2006 or 1/27/2006
• Excel converts these dates to a numerical
  representation (1/22/2006 ? 38739)
• Thus dates may be used in formulas A1B1 will
  result in the value 5

Note To do arithmetic calculations with dates if
you type 1/27/2006-1/22/2006 directly in a cell
it does not interpret it a date cell references
must be used.
8
In order to write Excel formulas we also need to
use the correct Operator Symbols

• Formulas contain two types of components
• Operators Operations to be performed
• Arithmetic operators / -
• Relational operators gt, lt, lt, gt,lt gt,
• Operands Values to be operated on

Addition Operator
B2 5
Operands
9

• Precedence of Operators
• ( ) Parenthesis is a special operator that forces
  evaluation of the expression inside it first
• Exponentiation (23 ?8)
• Arithmetic operators Multiplication Division
• Multiplication Division have equal precedence
  and are evaluated from left to right
• Arithmetic operators Addition Subtraction
• Addition Subtraction have equal precedence and
  are evaluated from left to right
• Relational operators have a lower precedence than
  arithmetic operators

10
Copying Formulas Another Advantage of an
Electronic Spreadsheet in the ability to Copy
Formulas and re-use them

• Copy a cell or a range of cells down columns,
  across rows or both - Use the Copy Paste
  features or by using the Fill handle feature.

11
What happens when you copy a formula?Does it
copy the exact formula ?

• When you copy cell A3 into cell B3 - Do you want
  to add A1 and A2 again or do you now want to add
  B1 to B2?
• Excel can copy the formula relatively or
  absolutely

12
Relative Addressing
In Relative Addressing the cell references in a
formula are automatically adjusted relatively
when you copy the formula into another cell. The
resulting formula to depending on the
displacement from the original position.
13
Relative Addressing- Example

• Copy the formula in cell A3 into cells B3, A4
  and C6. What new formulas will result?

B3col shift 1 row shift 0 A4col shift 0
row shift 1 C6col shift 2 row shift 3
B1B2 A2A3 C4C5
14
How can you write a formula in cell D6 to add 5
to the corresponding base price cost?

• In D6 you want to multiply the value 575 by 1.05
• Write the formula B6(1B1)
• What happens when you copy it down and across?

15
Absolute Addressing

• To keep a reference Absolute with respect to
  both Row and Column use a in front of the
  column and row B1
• When copying a term in a formula that has
  absolute cell referencing, the displacement has
  no effect on the resulting formula.

So you could write the formula B6 (1B1)
to solve the previous problem.
16
What formula could you write in cell D5 to
calculate total cost so that it can be copied
both down the column and across the row? (Link)

• In D5 you want to multiply 1 - trips to Site A
  by 125 /trip
• If you write the formula B5D2 what happens
  when you copy it down and across? What should
  change in the formula what should stay the same?

17
Mixed Cell Referencing

• To reference a cell Absolutely with respect to
  Row and Relatively with respect to Column
• Use this notation D2
• Entry in D5 is B5D2. Copy to the following
• D6 is B5 D2 column shift 1 row shift
  0
• E5 is ??
• E7 is ??

18
Mixed Cell Referencing

• To reference a cell Absolutely with respect to
  Column and Relatively with respect to Row Use
  this notation B3

Write a formula in cell E3, which can be copied
across the row and down the column, to calculate
the total trip expenses for the Sales Group to
Site A.
19
D6 B1 B6
D5 D2 B5
20
E2 B2 C2
21
Points to note ..2

• Formula at E4 to be copied down the column
• E4 B4C4D4 ? GOOD
• E4 B4C4D4 ? WRONG
• E4 B4C4D4 ? WRONG
• E4 B4C4D4 ? Not wrong, but NOT GOOD,
  considered as you dont understand when to use
  at all
• If you dont have to copy the formula ? No
  needed

22
Another convenience in Excel is the ability to
Name cells

• Names can be assigned to cells.
• Name of a cell is a mapping to its absolute
  address.
• This name can now be used in formulas to refer to
  that cell. It will be considered an absolute
  address.
• Select the cell you want to name, click on the
  Name Box, enter name and then press Enter or use
  the Name Manager tool located on the Formulas
  Ribbon.

23
EXCEL ERROR VALUES (Link)

• - Numeric value too wide to display
• DIV/0! - Divide by 0 occurs
• N/A - Data being referenced is not available
• NAME? - Text in formula is not recognized
• NUM! - Problem with a number in a formula or
  function
• REF! - Cell reference is not valid
• VALUE! - Wrong type of argument or operand in a
  formula

24
Summary

• In this lecture we discussed how to write and
  copy formulas specifically addressing the
  following
• Data precision vs. display
• Excel operators and order of precedence
• Relative Absolute cell referencing
• Error Messages

25
Example Write a formula in F4 that can be copied
down to calculate the percentage of Blues score
to the total possible score.

• Divide Blues score by the total possible points
  E4/E2
• When you copy this down you want the persons
  score to change to the new persons score but the
  total possible points to remain the same
• The final formula should be E4/E2.

26
A Multiplication Table (Link)
What formula would you write in cell B2 to
calculate the product. This formula must work
when copied down the column and across the row to
form the 5s Multiplication table.