EXCEL FUNCTIONS

SUM(_)

ADDITION of numbers

AVERAGE(_)

Finds MEAN / AVERAGE of numbers

COUNT(_)

Counts amount of cells that contain NUMBERS

MAX(_)

Finds LARGEST value

MIN(_)

Finds SMALLEST value

SUMIF(range, criteria, [sum_range])

ADDS values together under a CONDITION

EX. SUMIF(D2:D36, “>=18”, E2:E36)

• Add together pulse rate of only those who are equal and older than 18

AVERAGEIF(range, criteria, [sum_range])

Calculates MEAN / AVERAGE of values under a CONDITION

EX. AVERAGEIF(D2:D36, “>=18”, E2:E36)

COUNTIF(range, criteria)

COUNTS number of cells under ONE condition

EX. COUNTIF(Data, “>=70”) - COUNTIF(Data, “<=109”)

• counts number of cells between 70-109

COUNTIFS(range1, criteria1, range2, criteria2, …)

Count cells that meet MULTIPLE conditions

EX. COUNTIFS(Data, “>=70”, Data, “<=109”)

MAXIF(range, criteria)

Calculates MAX of values under a CONDITION

MINIF(range, criteria)

Calculates MIN of values under a CONDITION

LOOKUP(lookup_value, lookup_range, [result_range])

A function that searches for a value in a table and returns corresponding data

• LOOKUP_VALUE: what you are searching for (e.g., student ID)

• LOOKUP_RANGE: single row or column with data in ASCENDING order that the function searches

• RESULT_RANGE: if value matches with one of the ranges in lookup_range

  • the function returns matching data stored in result_range

VLOOKUP(lookup_range, table_array, col_index_number, range_lookup)

a VERTICAL lookup that searches the FIRST / LEFT column for a value and returns a matching value from a specified column in the same row

• LOOKUP_VALUE: what you are searching for (e.g., student ID)

• TABLE_ARRAY: table containing all the data

• COL_INDEX_NUMBER: column that contains answer / information

• RANGE_LOOKUP: true or false

  • TRUE = approx | FALSE = exact

IF(logical_test, value_if_true, value_if_false)

Checks a condition, returns one result if TRUE and another if FALSE

EX. IF(E3<76, “Good”, IF(E3<91, “Average”, IF(E3>90, “Poor”)))