Transcript these slides

EXCEL
FUNCTIONS
MIS 2502
THE BASICS

LEFT(), RIGHT(), MID()


CONCATENATE()


Drop leading and trailing spaces
LOWER(), UPPER(), PROPER()


Join two strings together
TRIM()


Keep X characters from string
Convert string to lower, upper or proper case
IF()

IF THEN type statements
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,

As above.
 MID(cell/value,


num_chars)
start_index, num_chars)
as above, but…
start_index = first character to start at
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
MORE ADVANCED:
INDEXING & CALCULATIONS

VLOOKUP() (or HLOOKUP)


COUNTIF()


Sum values across records that meet some condition
SUMPRODUCT()


Count records meeting some condition
SUMIF()


Match values and get associated fields
Sum the product of two columns
Nested Statements

Statements within statements
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 + …)
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