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 + …)