Source: Business Analytics Foundations, 5th Ed., Eric C. Larson, Ch. 1
Tags: Excel functions, SUM, COUNT, COUNTA, MAX, MIN, ABS, AVERAGE, MEDIAN, ROUND, IF, COUNTIF, SUMIF, VLOOKUP, INDEX, MATCH, nested functions, BADM 210, business analytics
Difficulty: Beginner to Intermediate Prerequisites: Part 1 of these notes (cell references, absolute references, arrays). You need to be comfortable with referencing cells and selecting ranges before working with functions.
Knowing how to navigate Excel and organise data (Part 1) is only half the picture. The real value of Excel for business analytics is its ability to calculate, summarise, and conditionally evaluate data through built-in functions. This section covers the core functions you will use throughout the course: arithmetic operations, aggregation functions (SUM, COUNT, AVERAGE, MEDIAN, MAX, MIN), rounding, conditional logic (IF, COUNTIF, SUMIF), and lookup functions (VLOOKUP, INDEX/MATCH). These are the building blocks for every analytical task that follows.
Excel functions start with "=" and perform calculations on cell values or ranges. The essentials are: arithmetic operators (+, -, *, /), aggregation (SUM, COUNT, COUNTA, MAX, MIN, AVERAGE, MEDIAN), rounding (ROUND), conditional logic (IF, COUNTIF, SUMIF), and data lookup (VLOOKUP, INDEX/MATCH). Most functions accept individual cells, numbers, or arrays as inputs.
Function
A predefined formula in Excel that performs a specific calculation. Every function begins with "=", followed by the function name, and then parentheses containing the required and optional inputs (arguments).
In simple terms, it is a shortcut for a calculation that would otherwise take many manual steps.
Argument (input)
A value, cell reference, or range that you pass into a function's parentheses. Some arguments are required; others are optional.
Nesting
Placing one function inside another as an argument. For example, =MAX(ABS(A1:Z101)) nests ABS inside MAX.
In simple terms, it is functions within functions, like Russian dolls.
SUM
Adds all numerical values in the specified cells or range. Syntax: =SUM(range).
COUNT
Returns the number of cells in a range that contain numerical values. Does not count text or blank cells.
COUNTA
Returns the number of cells in a range that contain any data at all (text or numbers). Only blank cells are excluded.
MAX
Returns the largest value in a range.
MIN
Returns the smallest value in a range.
ABS
Returns the absolute value of a number (its distance from zero, always positive). Syntax: =ABS(value).
AVERAGE
Returns the arithmetic mean of a set of numbers.
In simple terms, it adds everything up and divides by how many values there are.
MEDIAN
Returns the middle value of a set of numbers when arranged in order. If the set has an even number of values, it returns the average of the two middle values.
ROUND
Rounds a number to a specified number of decimal places. Syntax: =ROUND(number, num_digits).
IF
Evaluates a condition and returns one value if true, another if false. Syntax: =IF(condition, value_if_true, value_if_false). Follows the IF-THEN-ELSE pattern from programming.
Nested IF
An IF function where one or both of the return values is itself another IF function. Used for decision-tree style logic with more than two outcomes.
COUNTIF
Counts the number of cells in a range that meet a specified condition. Syntax: =COUNTIF(range, criteria).
SUMIF
Adds the values in a range that meet a specified condition. Can optionally sum from a different column than the one being evaluated. Syntax: =SUMIF(criteria_range, criteria, [sum_range]).
VLOOKUP
Searches the leftmost column of a range for a value and returns a value from a specified column in the same row. Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
In simple terms, it is Excel's way of saying "find this item in column A and tell me what is in column C on the same row."
INDEX
Returns the value of a cell in a specified array at a given row number. Syntax: =INDEX(array, row_num).
MATCH
Returns the row position of a value within a single column or range. Syntax: =MATCH(lookup_value, lookup_array, [match_type]).
INDEX/MATCH
A combination of INDEX and MATCH that achieves the same result as VLOOKUP but with more flexibility. The MATCH function finds the row; the INDEX function retrieves the value.
All formulas begin with "=".
Addition: =A1+A2
Subtraction: =A1-A2
Multiplication: =A1*A2
Division: =A1/A2
You can combine cell references and hard-coded numbers (e.g. =A1*1.08 to add 8% tax).
Excel follows standard order of operations (PEMDAS/BODMAS). Use parentheses to control evaluation order.
Adds all numerical values in its inputs.
Accepts individual cells, comma-separated values, or ranges.
Example: =SUM(A1:A100) adds every numerical value from A1 to A100.
More efficient than chaining addition operators when working with large datasets.
=COUNT(range) returns how many cells in the range contain numbers. Text cells and blanks are ignored.
=COUNTA(range) returns how many cells contain any data at all (numbers or text). Only truly empty cells are excluded.
Useful for quickly checking how many data points you are working with, or spotting gaps in your dataset.
=MAX(range) returns the largest value. =MIN(range) returns the smallest.
=ABS(value) strips the sign from a number and returns its distance from zero. ABS(-7) returns 7.
To find the value furthest from zero in a dataset (regardless of sign), nest the functions: =MAX(ABS(A1:Z101)). This is an array formula: ABS converts every value to its absolute form, then MAX picks the largest.
=AVERAGE(range) returns the arithmetic mean.
=MEDIAN(range) returns the middle value when the data is ordered.
Both accept individual cells, numbers, or arrays.
For skewed data, the median is often more representative of a "typical" value than the mean. This distinction becomes important in later statistics chapters.
=ROUND(number, num_digits) rounds a number to the specified decimal places.
Example: if A1 contains 3.14159265, then =ROUND(A1,2) returns 3.14.
Setting num_digits to 0 rounds to the nearest whole number. Negative values round to the left of the decimal (e.g. =ROUND(1234,-2) returns 1200).
Syntax: =IF(condition, value_if_true, value_if_false)
The condition can test equality (A1=A2), greater than (A1>100), less than (A1<50), and so on.
Return values can be text strings (wrapped in quotation marks), numbers, or other functions.
Example: =IF(A1>=90,"Pass","Fail") returns "Pass" when A1 is 90 or above, "Fail" otherwise.
You can place an IF function inside the true or false argument of another IF.
Example: =IF(A1>=90,"A",IF(A1>=80,"B",IF(A1>=70,"C","F")))
This creates a decision tree: check for A first, then B, then C, with F as the default.
Nested IFs can become difficult to read past three or four levels. For complex branching, later courses may introduce IFS or SWITCH as alternatives.
Syntax: =COUNTIF(range, criteria)
Counts how many cells in the range meet the criteria.
If the criteria is a simple value, no operator is needed: =COUNTIF(A1:A100,"Manager") counts cells containing the text "Manager."
For conditions other than equality, wrap the operator and value in quotation marks: =COUNTIF(A1:A100,">100") counts cells with values greater than 100.
Syntax: =SUMIF(criteria_range, criteria, [sum_range])
Adds values that meet a condition. The optional third argument (sum_range) lets you evaluate one column but sum a different one.
Example: =SUMIF(A1:A101,"Manager",B1:B101) checks column A for "Manager" and sums the corresponding values in column B. This could answer the question "what is our total spend on manager salaries?"
When sum_range is omitted, Excel sums the values in the criteria_range itself.
Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
lookup_value: the value to search for (or a cell reference containing it).
table_array: the range containing both the lookup column and the return column. The lookup column must be the leftmost column of this range.
col_index_num: the column number (counting from the left of the table_array) from which to return a value.
range_lookup: FALSE (or 0) for an exact match; TRUE (or 1) for an approximate match. Almost always use FALSE.
Example: a table has order numbers in column A, order dates in column B, and total costs in column C. To find the cost of order 12298: =VLOOKUP(12298,A1:C10000,3,FALSE).
Key constraint: the lookup value must be in the leftmost column of the table_array. If it is not, VLOOKUP will not work, and you should use INDEX/MATCH instead.
INDEX returns a value from a specified position in an array. MATCH finds that position.
Syntax: =INDEX(return_array, MATCH(lookup_value, lookup_array, match_type))
return_array: the column containing the data you want returned.
lookup_value: the value you are searching for.
lookup_array: the column containing the lookup values.
match_type: 0 (or FALSE) for an exact match. Use this almost always.
Example (same scenario as VLOOKUP above): =INDEX(C1:C10000,MATCH(12298,A1:A10000,FALSE)). MATCH finds the row where 12298 sits in column A, and INDEX returns the corresponding value from column C.
INDEX/MATCH is more flexible than VLOOKUP because the lookup column does not need to be to the left of the return column, and you can change the return column without re-writing the range.
Function | Syntax | Purpose |
|---|---|---|
SUM | =SUM(range) | Add all numbers in range |
COUNT | =COUNT(range) | Count cells with numbers |
COUNTA | =COUNTA(range) | Count cells with any data |
MAX | =MAX(range) | Largest value |
MIN | =MIN(range) | Smallest value |
ABS | =ABS(value) | Absolute value |
AVERAGE | =AVERAGE(range) | Arithmetic mean |
MEDIAN | =MEDIAN(range) | Middle value |
ROUND | =ROUND(value, digits) | Round to n decimal places |
IF | =IF(test, true_val, false_val) | Conditional logic |
COUNTIF | =COUNTIF(range, criteria) | Count cells meeting a condition |
SUMIF | =SUMIF(range, criteria, [sum_range]) | Sum values meeting a condition |
VLOOKUP | =VLOOKUP(val, range, col, FALSE) | Look up a value by row |
INDEX/MATCH | =INDEX(arr, MATCH(val, arr, 0)) | Flexible row lookup |
SUMIF is how a payroll analyst quickly totals compensation by job title, department, or location without manually separating the data first. VLOOKUP (or INDEX/MATCH) is what an operations analyst uses to pull a specific order's details from a database export containing tens of thousands of rows. IF statements drive conditional formatting in financial models: flagging accounts that exceed a budget threshold, for instance.
Students often confuse COUNT and COUNTA. COUNT only counts cells with numbers. If your column contains text labels, COUNT returns 0. Use COUNTA for any non-empty cell.
Students sometimes omit the fourth argument in VLOOKUP, assuming it defaults to an exact match. It does not. Omitting it, or setting it to TRUE, gives an approximate match, which almost never produces the intended result. Always include FALSE (or 0) unless you specifically need approximate matching on sorted data.
Students assume VLOOKUP can search any column. It cannot. The lookup value must be in the leftmost column of the table_array. If your data is not arranged this way, use INDEX/MATCH instead.
Students forget that COUNTIF and SUMIF require quotation marks around conditional operators. =COUNTIF(A1:A100,>100) will throw an error. The correct form is =COUNTIF(A1:A100,">100").
⚠️ Know the syntax and purpose of every function listed in the quick reference table. Exam questions frequently present a scenario and ask which function to use.
⚠️ Be able to write a complete VLOOKUP from scratch given a data layout. Know what each of the four arguments does.
⚠️ Understand when INDEX/MATCH is preferable to VLOOKUP (lookup column is not the leftmost column, or you need to change the return column easily).
⚠️ Expect questions that test whether you can nest functions, particularly IF within IF, or ABS within MAX.
⚠️ COUNTIF and SUMIF criteria syntax is a common exam trap. Remember: simple equality needs no operator, but greater-than/less-than conditions need quotation marks around the full expression.
True or False: =COUNT(A1:A100) counts cells containing text. False (use COUNTA for that)
Fill in the blank: =ROUND(7.856, 1) returns ____. 7.9
True or False: =VLOOKUP(100, A1:D500, 3, FALSE) searches column A for the value 100 and returns the value from column C of the matching row. True (column C is the third column of the range A:D)
True or False: In INDEX/MATCH, the lookup_array in MATCH must be the same column as the return_array in INDEX. False (they are typically different columns; MATCH searches one column, INDEX returns from another)
Fill in the blank: =IF(B1>50, "High", "Low") returns "High" when B1 is ____. Greater than 50
Q: An analyst has employee names in column A and salaries in column B (rows 1 to 500). Write a function to find the total salary expense.
A: =SUM(B1:B500)
Q: Using the same dataset, write a function that counts how many employees earn more than $75,000.
A: =COUNTIF(B1:B500,">75000")
Q: An analyst has department names in column A and salaries in column B. Write a function to find the total salary spend for the Marketing department only.
A: =SUMIF(A1:A500,"Marketing",B1:B500)
Q: A table has product IDs in column A, product names in column B, and prices in column C (rows 1 to 1000). Write a VLOOKUP to find the price of product ID 4455.
A: =VLOOKUP(4455,A1:C1000,3,FALSE)
Q: Rewrite the previous lookup using INDEX and MATCH.
A: =INDEX(C1:C1000,MATCH(4455,A1:A1000,0))
Q: What is the key limitation of VLOOKUP that INDEX/MATCH overcomes?
A: VLOOKUP requires the lookup value to be in the leftmost column of the table array. INDEX/MATCH has no such restriction; the lookup column and return column can be in any order.
Q: An analyst wants to display "Above Average" if a student's score (in A1) is above the class average (stored in B1), and "Below Average" otherwise. Write the IF function.
A: =IF(A1>B1,"Above Average","Below Average")
These functions are the toolkit for every analytical chapter that follows. Descriptive statistics (mean, median, max, min) feed directly into the next chapters on data summarisation. Conditional functions (IF, COUNTIF, SUMIF) are the foundation for building more complex business logic and will reappear when you work with pivot tables, conditional formatting, and scenario analysis. VLOOKUP and INDEX/MATCH are essential whenever you combine data from multiple sources, a task that comes up repeatedly in real analytics work and in later assignments.
Excel functions, SUM function, COUNT function, COUNTA, MAX MIN Excel, ABS absolute value, AVERAGE function, MEDIAN function, ROUND function, IF function, nested IF, COUNTIF, SUMIF, VLOOKUP, INDEX MATCH, lookup functions, Excel formulas, business analytics, BADM 210, BADM210, data analysis, spreadsheet formulas, conditional functions, cell calculations, Eric Larson, University of Illinois, Gies Business