Formulas & Functions | Data Analytics Course

Mathematical & Statistical Functions

Master the fundamental functions that power data analysis. These workhorses help you calculate totals, averages, minimums, maximums, and more complex statistical measures essential for business intelligence.

Core Aggregate Functions

Aggregate functions operate on ranges of cells to produce a single result. They are the foundation of data analysis in Excel, allowing you to summarize thousands of data points into meaningful insights.

Core Functions

SUM, AVERAGE, COUNT

SUM adds all numbers in a range. Think of it as your digital adding machine, tallying up sales, expenses, inventory counts, or any numeric data you need to total.

AVERAGE calculates the mean by adding all values and dividing by the count. Use it to find typical performance levels, average temperatures, mean test scores, or central tendencies in your data.

COUNT counts how many cells contain numbers. COUNTA counts non-empty cells of any type (text, numbers, dates). Use COUNT for numeric fields and COUNTA when you need to count any populated cells.

Basic Aggregate Functions

=SUM(A1:A10)              Add all values in range A1 to A10
=SUM(A1,A3,A5)            Add specific cells (non-contiguous)
=SUM(A:A)                 Add entire column A

=AVERAGE(B1:B50)          Calculate mean of B1 to B50
=AVERAGE(B1:B10,D1:D10)   Average across multiple ranges

=COUNT(C1:C100)           Count numeric values in C1:C100
=COUNTA(C1:C100)          Count non-empty cells (any type)
=COUNTBLANK(C1:C100)      Count empty cells

MIN, MAX, and Range Analysis

Finding minimum and maximum values helps identify outliers, establish boundaries, and understand the spread of your data. These functions are critical in quality control, performance tracking, and identifying exceptional cases.

Min/Max Functions

=MIN(A1:A100)             Find smallest value in range
=MAX(A1:A100)             Find largest value in range
=MAX(A1:A100)-MIN(A1:A100)  Calculate range (spread)

=LARGE(A1:A100, 2)        Find 2nd largest value
=SMALL(A1:A100, 3)        Find 3rd smallest value

Pro Tip: Use LARGE and SMALL to find the top N or bottom N performers without sorting. For example, =LARGE(sales_range, 1) gives you the highest sale, =LARGE(sales_range, 2) gives the second highest, and so on.

Conditional Counting and Summing

Standard SUM and COUNT work on entire ranges, but what if you only want to count or sum cells that meet specific criteria? That is where SUMIF, COUNTIF, and their multi-criteria cousins come in.

Conditional Functions

=COUNTIF(A1:A100, ">50")     Count cells greater than 50
=COUNTIF(B1:B100, "Pass")    Count cells containing "Pass"
=COUNTIF(C1:C100, ">=100")   Count cells >= 100

=SUMIF(A1:A10, ">50", B1:B10)  Sum B1:B10 where A1:A10 > 50
=SUMIF(C1:C10, "West", D1:D10) Sum sales (D) for "West" region (C)

=AVERAGEIF(A1:A50, ">0", B1:B50)  Average B where A is positive

Multiple Criteria with SUMIFS and COUNTIFS

When you need to apply multiple conditions simultaneously, use the plural forms: SUMIFS, COUNTIFS, AVERAGEIFS. Note the syntax change: the sum range comes first in SUMIFS, unlike SUMIF.

Multiple Criteria Functions

=COUNTIFS(A1:A100,">50", B1:B100,"Pass")
  Count rows where A>50 AND B="Pass"

=SUMIFS(D1:D100, A1:A100,"West", B1:B100,">1000")
  Sum D where region="West" AND sales>1000

=AVERAGEIFS(E1:E100, C1:C100,"Q1", D1:D100,">=50")
  Average E where quarter="Q1" AND score>=50

Syntax Difference: SUMIF and COUNTIF put criteria range first, then criteria, then sum range. But SUMIFS and COUNTIFS put sum range first, then pairs of criteria range and criteria. Watch out for this!

ROUND and Precision Control

Rounding controls how many decimal places are displayed and can affect calculations. Excel offers several rounding functions depending on whether you want to round normally, always round up, or always round down.

Rounding Functions

=ROUND(3.14159, 2)        Result: 3.14 (2 decimal places)
=ROUND(1234.56, 0)        Result: 1235 (whole number)
=ROUND(1234.56, -2)       Result: 1200 (nearest hundred)

=ROUNDUP(3.14, 0)         Result: 4 (always round up)
=ROUNDDOWN(3.99, 0)       Result: 3 (always round down)

=CEILING(7.2, 1)          Result: 8 (round up to nearest 1)
=FLOOR(7.8, 1)            Result: 7 (round down to nearest 1)

Practice Questions

Scenario: You have quarterly sales data for 4 regions and need to calculate summary statistics for a board presentation.

Task: Using the following Q1 sales data: North=$150k, South=$120k, East=$180k, West=$95k

Calculate total Q1 sales across all regions
Find the average regional sales
Identify the best-performing region
Identify the worst-performing region
Calculate the range (difference between best and worst)

Show Solution

A1: North    B1: 150000
A2: South    B2: 120000
A3: East     B3: 180000
A4: West     B4: 95000

Total:    =SUM(B1:B4)           Result: $545,000
Average:  =AVERAGE(B1:B4)       Result: $136,250
Best:     =MAX(B1:B4)           Result: $180,000 (East)
Worst:    =MIN(B1:B4)           Result: $95,000 (West)
Range:    =MAX(B1:B4)-MIN(B1:B4) Result: $85,000

Scenario: Your sales team has data for 50 transactions. You need to analyze performance by region and threshold.

Task: Create formulas to answer these questions:

How many sales exceeded $1,000?
What is the total revenue from the "West" region?
Count how many "East" region sales were over $500
Calculate average sale amount for transactions over $750

Show Solution

Assuming: Column A=Region, Column B=Sale Amount

1. Count sales > $1,000:
   =COUNTIF(B2:B51, ">1000")

2. Total revenue from West:
   =SUMIF(A2:A51, "West", B2:B51)

3. Count East sales > $500:
   =COUNTIFS(A2:A51, "East", B2:B51, ">500")

4. Average of sales > $750:
   =AVERAGEIF(B2:B51, ">750")

Logical Functions

Logical functions bring decision-making power to your spreadsheets. They evaluate conditions and return different results based on whether those conditions are true or false, enabling dynamic calculations and automated categorization.

The IF Function

IF is one of the most powerful and frequently used functions in Excel. It evaluates a condition and returns one value if true, another if false. Think of it as teaching Excel to make decisions: "If sales exceed target, display 'Bonus', otherwise display 'No Bonus'."

Core Function

IF Function

Syntax: =IF(condition, value_if_true, value_if_false)

The condition is any expression that evaluates to TRUE or FALSE. If TRUE, Excel returns value_if_true. If FALSE, it returns value_if_false. You can nest up to 64 IF functions (though that gets messy fast).

Use cases: Pass/Fail determination, bonus calculations, status labels, conditional pricing, tier assignments, alert flags, and any scenario requiring binary decision-making.

IF Function Examples

=IF(A1>100, "High", "Low")
  If A1>100, return "High", else "Low"

=IF(B2="Pass", 1, 0)
  Convert Pass/Fail to 1/0

=IF(C3>=60, "Pass", "Fail")
  Grade determination

=IF(D4="", "Missing", D4)
  Handle empty cells

Nested IF Statements

When you have more than two possible outcomes, you can nest IF functions inside each other. Each IF handles one decision point. However, deeply nested IFs become hard to read and maintain. Consider IFS or other alternatives when you have many conditions.

Nested IF

=IF(A1>=90, "A", IF(A1>=80, "B", IF(A1>=70, "C", "F")))
  Letter grade: A for 90+, B for 80-89, C for 70-79, F below 70

=IF(B1="High", 100, IF(B1="Medium", 50, IF(B1="Low", 25, 0)))
  Priority scoring based on text labels

AND, OR, NOT Functions

These logical operators help you test multiple conditions at once. AND requires all conditions to be true. OR requires at least one condition to be true. NOT reverses a condition.

Logical Operators

=AND(A1>50, B1<100)
  TRUE only if both conditions are true

=OR(A1="Yes", B1="Yes")
  TRUE if either condition is true

=NOT(A1="Closed")
  TRUE if A1 is not "Closed"

=IF(AND(A1>50, B1="Active"), "Qualified", "Not Qualified")
  Combine with IF for complex logic

IFS Function (Modern Alternative)

Excel 2016 and later include the IFS function, which handles multiple conditions without nesting. It tests conditions in order and returns the value for the first TRUE condition. Much cleaner than nested IFs!

IFS Function

=IFS(A1>=90,"A", A1>=80,"B", A1>=70,"C", A1>=60,"D", TRUE,"F")
  Cleaner than nested IF for multiple conditions

=IFS(B1="High",3, B1="Medium",2, B1="Low",1, TRUE,0)
  Priority scoring without nesting

Lookup Functions

Lookup functions are Excel's database query tools. They search for a value in one column and return a corresponding value from another column. Essential for matching data between tables, price lists, employee records, and more.

VLOOKUP: Vertical Lookup

VLOOKUP is one of Excel's most iconic functions. It searches vertically (down) in the first column of a table to find a match, then returns a value from a specified column in that same row. Think of it as asking: "Find this ID in column 1, then tell me what is in column 3 of that same row."

Lookup Function

VLOOKUP

Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value: The value to search for (like an employee ID or product code)
table_array: The range containing your lookup table (must include the search column)
col_index_num: Which column number to return (1 = first column, 2 = second, etc.)
range_lookup: FALSE for exact match (recommended), TRUE for approximate match

Key limitation: VLOOKUP can only look to the right. The lookup column must be the leftmost column of your table range. Use XLOOKUP or INDEX/MATCH for more flexibility.

VLOOKUP Examples

=VLOOKUP(A2, Products!A:D, 3, FALSE)
  Find A2 in Products sheet column A, return column 3 value

=VLOOKUP(EmployeeID, EmployeeTable, 4, FALSE)
  Look up employee info from a named table

=IFERROR(VLOOKUP(A2, PriceList, 2, FALSE), "Not Found")
  Handle lookup errors gracefully

When to Use VLOOKUP

Lookup column is leftmost in table
Simple, straightforward lookups
Working with older Excel versions
Table structure will not change

VLOOKUP Limitations

Cannot look left (only right)
Breaks if you insert/delete columns
Less flexible than newer alternatives
col_index_num can be error-prone

XLOOKUP: Modern Replacement

XLOOKUP (Excel 365 and Excel 2021) is the modern successor to VLOOKUP and HLOOKUP. It overcomes their limitations: it can look in any direction, returns an array, handles errors elegantly, and supports approximate matches with multiple modes. If you have access to XLOOKUP, use it instead of VLOOKUP.

XLOOKUP Examples

=XLOOKUP(A2, LookupRange, ReturnRange)
  Simple lookup (search A2, return from ReturnRange)

=XLOOKUP(A2, IDColumn, NameColumn, "Not Found")
  Lookup with custom error message

=XLOOKUP(A2, Products!A:A, Products!D:D)
  Can reference separate columns (no col_index needed!)

Why XLOOKUP is Better: Works left or right, built-in error handling, simpler syntax, more flexible matching options, and supports searching from bottom-up. If available in your Excel version, always prefer XLOOKUP over VLOOKUP.

INDEX and MATCH Combination

Before XLOOKUP existed, Excel pros combined INDEX and MATCH to overcome VLOOKUP limitations. INDEX returns a value from a specific row and column position. MATCH finds the position of a lookup value. Together, they create a powerful, flexible lookup that works in any direction.

INDEX/MATCH Pattern

=INDEX(ReturnRange, MATCH(LookupValue, LookupRange, 0))
  Classic INDEX/MATCH pattern

=INDEX(B:B, MATCH(A2, A:A, 0))
  Find A2 in column A, return corresponding value from column B

=INDEX(Prices!C:C, MATCH(ProductID, Prices!A:A, 0))
  Lookup across sheets with full flexibility

Text Functions

Text functions help you manipulate strings: extract parts of text, combine multiple text values, change case, remove spaces, and more. Essential for data cleaning and formatting messy imported data.

Combining Text: CONCATENATE and CONCAT

Joining text from multiple cells is a common task. Excel offers several methods: the ampersand operator, CONCATENATE function, and the newer CONCAT and TEXTJOIN functions.

Text Combination

=A1&" "&B1
  Join with ampersand (fastest way)

=CONCATENATE(A1," ",B1)
  Older function (still works)

=CONCAT(A1:A5)
  Modern function, joins range

=TEXTJOIN(", ", TRUE, A1:A5)
  Join with delimiter, skip blanks

Extracting Text: LEFT, RIGHT, MID

Extract portions of text strings using these functions. Specify how many characters to take from the left, right, or middle of a string.

Text Extraction

=LEFT(A1, 5)
  First 5 characters

=RIGHT(A1, 3)
  Last 3 characters

=MID(A1, 3, 4)
  4 characters starting at position 3

=LEFT(A1, FIND(" ", A1)-1)
  Extract first word (everything before space)

Finding and Replacing: FIND, SEARCH, SUBSTITUTE

Locate specific text within strings or replace text. FIND is case-sensitive, SEARCH is not. SUBSTITUTE replaces text occurrences.

Find and Replace

=FIND("@", A1)
  Position of @ character (case-sensitive)

=SEARCH("excel", A1)
  Position of "excel" (not case-sensitive)

=SUBSTITUTE(A1, "old", "new")
  Replace "old" with "new"

=SUBSTITUTE(A1, " ", "")
  Remove all spaces

Case Conversion and Trimming

Clean up text by standardizing case or removing extra spaces. Critical for data imported from external sources.

Text Cleaning

=UPPER(A1)
  CONVERT TO UPPERCASE

=LOWER(A1)
  convert to lowercase

=PROPER(A1)
  Convert To Title Case

=TRIM(A1)
  Remove extra spaces (keep only single spaces)

=LEN(A1)
  Count number of characters

Date and Time Functions

Master date and time calculations for project management, age calculations, business day tracking, and time-based analysis. Excel stores dates as numbers, making calculations straightforward once you understand the system.

Current Date and Time

TODAY and NOW return the current date and date-time, respectively. They update automatically when the worksheet recalculates, making them perfect for dynamic dashboards and aging calculations.

Current Date/Time

=TODAY()
  Current date (updates daily)

=NOW()
  Current date and time (updates continuously)

=TODAY()+30
  Date 30 days from today

Date Component Extraction

Extract specific parts of a date value: year, month, day, weekday, etc. Useful for grouping, filtering, and date-based calculations.

Date Parts

=YEAR(A1)
  Extract year (2026)

=MONTH(A1)
  Extract month (1-12)

=DAY(A1)
  Extract day (1-31)

=WEEKDAY(A1)
  Day of week (1=Sunday, 7=Saturday)

=TEXT(A1, "dddd")
  Full weekday name ("Monday")

Date Calculations

Calculate differences between dates, add or subtract time periods, and determine business days. Remember: dates are stored as numbers, so subtraction gives you the number of days between dates.

Date Arithmetic

=B1-A1
  Days between two dates

=DATEDIF(A1, B1, "Y")
  Complete years between dates

=DATEDIF(A1, B1, "M")
  Complete months between dates

=NETWORKDAYS(A1, B1)
  Business days between dates (excludes weekends)

=EDATE(A1, 3)
  Date 3 months from A1

=EOMONTH(A1, 0)
  Last day of month for date in A1

DATEDIF Hidden Function: DATEDIF is not listed in Excel's function list but works perfectly. Use "Y" for years, "M" for months, "D" for days, "YM" for months ignoring years, and "MD" for days ignoring months and years.

Key Takeaways

Master the Basics

SUM, AVERAGE, COUNT, MIN, MAX are your foundation. Conditional versions add powerful filtering.

Logic is Power

IF functions bring decision-making to spreadsheets. Combine with AND/OR for complex conditions.

Lookups Connect Data

VLOOKUP finds data vertically. XLOOKUP is more flexible. INDEX/MATCH offers maximum control.

Text Functions Clean Data

Use TRIM, UPPER, LOWER for standardization. LEFT, RIGHT, MID extract portions of text.

Date Math Matters

Use NETWORKDAYS for business days. DATEDIF calculates age. TODAY and NOW for dynamic dates.

Handle Errors Gracefully

Use IFERROR to prevent #N/A and #VALUE! errors from breaking dashboards and reports.

Excel Formulas & Functions

What You'll Learn

Contents