EXCEL FUNCTIONS

1
EXCEL FUNCTIONS

• MIS 2502

2
THE BASICS

• LEFT(), RIGHT(), MID()
• Keep X characters from string
• Find()
• Find the position of certain characters
• CONCATENATE()
• Join two strings together
• TRIM()
• Drop leading and trailing spaces
• LOWER(), UPPER(), PROPER()
• Convert string to lower, upper or proper case
• IF()
• IF THEN type statements
• Nested Statement

3
LEFT(), RIGHT() or MID()

• LEFT(cell/value, num_chars)
• cell cell reference (e.g., A2)
• num_chars number of characters to keep
• RIGHT(cell/value, num_chars)
• As above.
• MID(cell/value, start_index, num_chars)
• as above, but
• start_index first character to start at

4
FIND()

• FIND(cell/value, cell/value, start_num)
• cell1, cell2, etc. cell reference
• val1, val2, etc. a string value (e.g., text)
• Start_number specifies the character at which
  to start the search.

5
CONCATENATE()

• CONCATENATE(cell1/val1, cell2/val2, )
• cell1, cell2, etc. cell reference
• val1, val2, etc. a string value (e.g., text)
• Example
• CONCATENATE(A2, A3, , text)would result in
  a value combining the contents of cells A2 and
  A3, a space, and the word text

6
TRIM()

• TRIM(cell/val)
• cell cell reference
• val text value
• Example
• TRIM( text value ) spit out a cell
  contents of just text value, with no leading or
  trailing spaces

7
LOWER(), UPPER(), PROPER()

• LOWER(cell/val)
• cell cell reference
• val text value
• Example
• LOWER(ABCDEFG) would result in a value of
  abcdefg
• UPPER(abcdefg) would result in a value of
  ABCDEFG
• PROPER(ABCDEFG) gives you Abcdefg

8
IF()

• IF(condition, met_result, else_result)
• condition conditional statement (e.g., A2 gt 3,
  C5 text)
• met_result value to spit out if condition is
  met
• else_result value to spit out if condition is
  not met
• Example
• IF(4 gt 5, red, yellow) would give you a
  result of yellow

9
NESTED STATEMENTS()

• Replace cell or val with some other statement
• Example
• IF(10 gt 9, IF(5 4, 1000, 999), 0) would return
  a value of?

10
NESTED STATEMENTS()

• Replace cell or val with some other statement
• Example
• IF(10 gt 9, IF(5 4, 1000, 999), 0) would return
  a value of
• 999

11
MORE ADVANCED INDEXING CALCULATIONS

• VLOOKUP() (or HLOOKUP)
• Match values and get associated fields
• COUNTIF()
• Count records meeting some condition
• SUMIF()
• Sum values across records that meet some
  condition
• SUMPRODUCT()
• Sum the product of two columns

12
VLOOKUP()

• VLOOKUP(cell/val, range, index , approximate)
• cell cell reference
• val value
• range range of cells (e.g., A2E5)
• index what column value to return from matched
  record
• approximate is an approximate match okay?
• Example
• VLOOKUP(abc, A1B10, 2) will find the record in
  A1B10 that has an A value matching abc, and it
  will return the second field value

13
COUNTIF()

• COUNTIF(range, condition)
• range range of cells (e.g., A2E5)
• condition only count if condition is met
• Example
• COUNTIF(A1A10,2) will count all the records
  in column A, from row 1 to 10, where the value is
  2

14
SUMIF()

• SUMIF(range1, condition, range2)
• range1 range of cells to examine (e.g., A2E5)
• condition only include record if condition met
• range2 range of values to sum
• Example
• SUMIF(A1A10,2, B1B10) will identify all the
  records in column A, from row 1 to 10, where the
  value is 2, and will add up the corresponding
  values in column B

15
SUMPRODUCT()

• SUMPRODUCT(range1, range2, )
• range1 first column of values
• range2 second column of values
• Example
• SUMPRODUCT(A1A10, B1B10) would return the
  summed products (i.e., A1B1 A2B2 A3B3
  )