More Excel Functions

1
More Excel Functions
2
Excel Formulas Provide the Power

• Look up formulas in Help and select Examples
  of commonly used formulas

3
Text or String Functions

• Excel has numerous useful functions for
  manipulating text in worksheets
• Text functions are also known as string
  functions
• string refers to a string of characters
• Text functions can help you perform tasks that
  would otherwise require a tremendous amount of
  manual work

4
Some Commonly Used Text Functions

• CONCATENATE   Joins several text items into one
  text item
• also can use the concatenation operator,
• FIND   Finds one text value within another
  (case-sensitive)
• SEARCH   Finds one text value within another (not
  case-sensitive)
• LEFT   Returns the leftmost characters from a
  text value
• MID   Returns a specific number of characters
  from a text string starting at the position you
  specify
• RIGHT   Returns the rightmost characters from a
  text value
• REPLACE   Replaces characters within text based
  on position
• SUBSTITUTE   Substitutes new text for old text in
  a text string
• TEXT   Formats a number and converts it to text
• TRIM   Removes spaces from text
• UPPER   Converts text to uppercase
• VALUE   Converts a text argument to a number

5
Text Function Examples
6
Lookup Functions

• Used to return values from ranges by looking up
  a specific value within the range heres a few
• HLOOKUP   Looks in the top row of an array and
  returns the value of the indicated cell
• VLOOKUP   Looks in the first column of an array
  and moves across the row to return the value of a
  cell
• MATCH   Looks up values in a reference or array
• INDEX   Uses an index to choose a value from a
  reference or array
• ADDRESS   Returns a reference as text to a single
  cell in a worksheet

7
Logical Functions

• AND   Returns TRUE if all its arguments are
  TRUEAnd(A1gt0,C15) will only be true if there
  is a 5 in C1 and the value in A1 is positive
• IF   Specifies a logical test to perform
• NOT   Reverses the logic of its
  argumentNot(A1) is True if A1 is currently
  False and vice versa
• OR   Returns TRUE if any argument is TRUE
  Or(A1gt0,C15) will be true if there is a 5 in C1
  OR the value in A1 is positive

IF(logical condition, result if true, result if
false) IF(A1gt5,Go for it,Nope)
8
The IF() FunctionLearn to read the Help files to
decipher how to use functions
9
Information Functions
Information functions are useful for finding out
things about cells. For example, if some cell is
blank perhaps you want to ignore it in a
calculation. You can use the ISBLANK() function
to check if a cell is blank.
10
Math and Trig Functions

• Many, many mathematical functions, heres a few
• Sum(), Product(), Fact()
• Floor(), Ceiling(), Int(), Round()
• Sin(), Cos(), Tan()
• Rand()
• Exp(), LN(), Log(), Log10()

11
Descriptive Statistics in Excel

• Data Analysis Tool-Pak

• AVERAGE(), STDEV(), MEDIAN()
• FREQUENCY()
• PERCENTILE()
• RANK(), PERCENTRANK()
• MIN(), MAX()

12
Nesting Functions Inside Other Functions

• Often you need to use multiple functions inside
  the same formula to accomplish some task
• If(Len(A1)gt12,Invalid phone ,OK)
• Assume A1 Report Date 1/25/2003To get the
  date into B1Datevalue(Mid(A1,13,11))