Transcript slides
EXCEL
FUNCTIONS
MIS 2502
THE BASICS
LEFT(), RIGHT(), MID()
Find()
Convert string to lower, upper or proper case
IF()
Drop leading and trailing spaces
LOWER(), UPPER(), PROPER()
Join two strings together
TRIM()
Find the position of certain characters
CONCATENATE()
Keep X characters from string
IF THEN type statements
Nested Statement
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
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.
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
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
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
IF()
=IF(condition, met_result, else_result)
condition = conditional statement
(e.g., A2 > 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 > 5, “red”, “yellow”) would give you a result of
yellow
NESTED STATEMENTS()
Replace cell or val with some other statement
Example…
IF(10 > 9, IF(5 = 4, 1000, 999), 0) would return a value
of…?
NESTED STATEMENTS()
Replace cell or val with some other statement
Example…
IF(10 > 9, IF(5 = 4, 1000, 999), 0) would return a
value of…
999
MORE ADVANCED:
INDEXING & CALCULATIONS
VLOOKUP() (or HLOOKUP)
COUNTIF()
Count records meeting some condition
SUMIF()
Match values and get associated fields
Sum values across records that meet some condition
SUMPRODUCT()
Sum the product of two columns
VLOOKUP()
=VLOOKUP(cell/val, range, index [, approximate])
cell = cell reference
val = value
range = range of cells (e.g., A2:E5)
index = what column value to return from
matched record
approximate = is an approximate match okay?
Example…
VLOOKUP(“abc”, A1:B10, 2) will find the record
in A1:B10 that has an A value matching abc,
and it will return the second field value
COUNTIF()
=COUNTIF(range, condition)
range = range of cells (e.g., A2:E5)
condition = only count if condition is met
Example…
COUNTIF(A1:A10,”=“&2) will count all the records in
column A, from row 1 to 10, where the value is 2
SUMIF()
=SUMIF(range1, condition, range2)
range1 = range of cells to examine (e.g., A2:E5)
condition = only include record if condition
met
range2 = range of values to sum
Example…
SUMIF(A1:A10,”=“&2, B1:B10) 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
SUMPRODUCT()
=SUMPRODUCT(range1, range2, …)
range1 = first column of values
range2 = second column of values
Example…
SUMPRODUCT(A1:A10, B1:B10) would return the
summed products
(i.e., A1*B1 + A2*B2 + A3*B3 + …)