these slides
Download
Report
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