ADVANCED EXCEL FUNCTIONS

1
ADVANCED EXCEL FUNCTIONS
2
I. Conditional Functions

• Conditional functions allow the software to
  perform conditional tests and evaluate a
  condition in your worksheet. Depending on
  whether the condition is true or false, different
  values will be returned to the cells.
• B. IF is the most important conditional function

3
C. IF FUNCTION

• 1. IF(condition, action if true, action if
  false)
• This tests the condition to determine if
  specific results or cell contents are true or
  false.
• 2. If the result of the test is true, the action
  if true is executed. If the result is false,
  the action if false portion contains another
  set of instructions to execute.
• 3. The instructions to be executed can return
  cell contents that are labels as well as values.

4
D. LOGICAL OPERATORS

• 1. To perform conditional tests, logical
  operators are required.
• Equal lt Less than or Equal to
• lt Less than gt Greater than or Equal to
• gt Greater than ltgt Not Equal

And(logical1, logical2) Returns true if each
condition is true Or(logical1,
logical2) Returns true if either condition
is true Not(logical) Returns true if the
condition is false True() Always returns
true False() Always returns false
5
E. EXAMPLES

• IF(A5gt20, B5, 0) means that if the value in A5
  is greater than 20, use the value in B5.
  Otherwise assign the number 0.
• IF(AND(B11ltgt0,G111),10,0) means that if the
  value in B11 is not equal to 0 and the value in
  G11 is equal to 1, assign the number 10.
  Otherwise, assign the number 0.
• IF(OR(E13Profit,F15gtG15),Surplus,Deficit)
  means that if either E13 contains the word
  Profit or the contents of F15 are greater than
  or equal to the contents of G15, assign the label
  Surplus. Otherwise, assign the label Deficit.

6
II. DATE FUNCTIONS

• Date coverts a date into a date number. Excel
  can represent any given date as a serial number
  equal to the number of days from Dec. 31, 1899 to
  the date in question.
• DATE(year number, month number, day number)
• Enter the date into a cell (F5) with the Date
  function. Then enter the Report date with the
  Date function into another cell (L6). The
  difference between these dates (in number of
  days) can be calculated with the formula L6-F5.

7
Function Wizard

• The function wizard may be used to make
  retrieving and entering functions easier.
• Select Insert/Function from the menu or select
  the fx button on the Standard Toolbar.
• First select the function and then enter the
  parameters. If a parameter must be entered, the
  word required appears next to it.
• You may enter functions inside of functions
  (nesting).
• Excel allows up to 7 levels of nesting.