Cell Reference Review and Functions

1
Cell Reference Review and Functions

• Relative Address
• Absolute Address
• Functions
• Decision Making in Excel
• Using the If function
• Examples

2
Relative Address

• Relative addressing changes cell addresses as you
  copy a formula to a new cell
• Cell Address Wrap
• Example copy the formula sum(A1A4) found in
  A5 to D1
• Result sum(D16381D16384)
• Cell row references wrap

3
Relative Address

• Example - copy the formula sum(A1A4) found in
  D6 to A6
• Result sum(IT1IT5)
• Cell column references wrap

4
Absolute Address

• When you want a cell address always to refer to a
  specific cell or range of cells, you must use an
  absolute address
• Works uses a dollar sign in front of the row or
  column portion of a cell address to indicate the
  portion that will not change

5
The Special Chars and ,

• When entering numbers in a cell, never add a
  dollar sign or comma to the number. These are
  reserved for use in formulas.
• If you want them, use the formatting option!

6
Mixed Address

• With a mixed cell address, the row or column can
  change without the other part changing

7
Functions

• Functions are pre-built formulas provided by
  Excel (over 300 functions).
• Are grouped into the following categories
• Database Date and Time
  Financial
• Information Logical
  
• Lookup and Reference Math
  and Trig
• Statistical Text

8
Functions

• Enable you to speed up your calculations compared
  to writing a formula
• For example, you could create
• (A1A2A3A4A5A6A7A8)
• or
• use the function SUM(A1A8)

9
Understanding Functions

• Functions act on data
• Functions accept information, referred to as
  arguments and return a result
• the general syntax of a function is
• FunctionName(arguments)
• each function takes on specific types of
  arguments, such as numbers, references, text or
  logical values

10
The Function SUM

• The SUM function, uses the numbers in selected
  cells.
• The selected cells make up the argument portion
  of the function.
• The argument of a function can be a single cell,
  a range of cells, a named range, or a number

11
Using the Wizard Function

• Creating functions can seem difficult
• especially with potentially different ways of
  spelling a function name (AVG, AVE, AVERAGE) and
  the potential number of arguments possible
• That is why Excel created the Function Wizard

12
Using the Wizard Function

• Select a cell where you want to enter the
  function
• Choose Insert-gtFunction (or you can click the
  Wizard Function Button - the fx button
• Section the type of function you want from the
  categories
• Choose the specific function
• The Wizard provides you with a series of windows
  to guide your completion of the function
  arguments

13
(No Transcript)
14
(No Transcript)
15
(No Transcript)
16
Decision Making in Excel

• When you will be using a spreadsheet, you will
  undoubtedly find situations where the result
  depends on different conditions
• Excel provides a number of features to facilitate
  analysis and lookups
• IF() and LOOKUP()

17
Using the If Function

• The IF function checks for certain conditions and
  then takes actions based on the results of that
  check
• Conditions are the result of a comparison that
  uses relational operators

18
Relational Operators

• Operator
• lt
• gt
• ltgt
• lt
• gt

• Meaning
• Equal to
• Less than
• Greater than
• Less than or equal to
• Greater than or equal to

19
Condition

• The condition lets you set up an equation using
  the relational operators to check for specific
  results or cell contents
• Examples
• A5 gt 10, H14 0, D19 lt 1985
• The answers to these equations are
• True (Yes) or False (No)

20
Actions

• Since there are only two outcomes to a condition,
  there can be only two actions to take
• 1. The action to perform if the condition is true
• 2. The action to perform if the condition is false

21
The If Function Format

• The format
• IF(condition, true action, false action)
• What happens, Excel evaluates the condition, if
  the condition is true, Excel performs the true
  action and thats it! But if the condition is
  false, Excel performs the false action and thats
  it!

22
If Example

• Assume that you have a spreadsheet that
  calculates the commissions for the sales
  representatives of a company.
• Suppose that the dollar amount that a rep has
  sold is in cell H5. If the sales rep sells more
  than 1000, he/she gets a commission of 10 else
  the commission is 0.

23
If Example

• Notice that we have two actions to perform
• 1. Sales .10
• 2. 0
• Which one we do is based on selling over 1000.
• Sales gt 1000
• Now we can put the IF function together

24
If Example

• We know that Sales is stored in H5 and we want
  the commission to be printed out in cell I5
• So in cell I5 we enter the following IF function
• IF(H5gt1000,H5 .1,0)

25
Text Actions in If Functions

• The IF function also lets you perform text
  actions
• Example
• You have a grade book spreadsheet. Your final
  grade is PASS or FAIL. A PASS is assigned
  for a grade greater than or equal to 70.
• IF(D27lt70,FAIL,PASS)

26
Text Actions

• You could have also said
• IF(D27gt70,PASS,FAIL)
• Note that any text to be entered in the cell must
  be included in quotes
• Text can also be used in the condition part of
  the If function

27
Text Comparison in an If Function

• Suppose you ran a business that collects sales
  taxes on mail orders received only from the state
  of Indiana.
• To check if the value of a cell (D7) contains a
  state code and calculate 5 sales tax only if the
  value in D7 is IN
• IF(D7IN,D15.05,No Tax)

28
More Complex Decisions

• You can use the logical operators And, Or, and
  Not to form more complex conditions
• You can use nested If functions to make more
  complex decisions. (using an If function in a
  true or false action)

29
AND Function

• AND() function produces a TRUE response only when
  all the elements within the parentheses meet the
  conditions gt otherwise FALSE
• When you want to check for a number within a
  range gt use AND
• IF(AND(D7gt30,D7lt61),D7,0)

30
OR Function

• OR()function is another logical test. It
  produces a TRUE response when any one of its
  arguments is TRUE.
• When you want to match one value against multiple
  values
• IF(OR(B12red,B12blue),OK,)

31
Evaluating Complex Decisions

• To make sure that your function is evaluated
  correctly, you must know the order of operations !

32
Evaluation Order

• We have looked at the order in which arithmetic
  operations are performed.
• Arithmetic order of operations
• negative or positive (negation)
• exponentiation
• multiplication or division
• addition or subtraction

33
Order

• But where do these new operator/functions fit in?
• First in line are the logical functions - (Not,
  And, OR)
• Second are arithmetic operations
• Finally are the relational operators
• Use () when in doubt

34
Complex examples

• IF(H9gt.894,A,if(H9gt.794,B,if(H9gt.694,C,if(H
  9gt.594,D,F))))
• What do you think this IF function does?
• IF(OR(D7IN,D7In),D15.05,NO TAX)

35
Other Counting Techniques

• The function Countif
• example
• countif(range,criteria) gt
• countif(A1A14,12)
• counts the number of of nonempty cells in the
  range (A1A14) that meet a specified critieria
  (12)

36
Other Countif Examples

• countif(A1A12,1)countif(A1A12,12)
• tallies the number of cells containing 1 or 12
• countif(A1A12,gt1)-countif(A1A12,lt10)
• count the number of cells that contain a value
  from 1 to 10
• countif(A1A12,yes)
• counts the number of cells containing the word yes

37
Questions