Formulas and Functions

The Power of Calculation

Formulas transform spreadsheets from static tables into dynamic tools. Once you set up formulas, your spreadsheet calculates automatically — change an input, and everything updates.

This chapter covers the functions you'll use constantly.

Formula Basics

How Formulas Work

Every formula starts with an equals sign =

Examples:

  • =5+3 returns 8
  • =A1+A2 adds the values in A1 and A2
  • =SUM(A1:A10) sums the range A1 through A10

Operators

OperatorMeaningExample
+Add=A1+B1
-Subtract=A1-B1
*Multiply=A1*B1
/Divide=A1/B1
^Exponent=A1^2 (squared)
&Concatenate text=A1&" "&B1

Order of Operations

Excel follows standard math order (PEMDAS):

  1. Parentheses
  2. Exponents
  3. Multiplication and Division (left to right)
  4. Addition and Subtraction (left to right)

Use parentheses to control order: =(A1+B1)*C1 is different from =A1+B1*C1

Cell References

Relative: =A1 — adjusts when copied (A1 becomes A2, A3, etc.)

Absolute: =$A$1 — stays fixed when copied

Mixed: =$A1 or =A$1 — partially fixed

When to use absolute: When referencing a fixed value like a tax rate or conversion factor.

Essential Functions

SUM — Add Numbers

=SUM(range)

Adds all numbers in a range.

Examples:

  • =SUM(A1:A10) — sum of A1 through A10
  • =SUM(A1:A10, C1:C10) — sum of both ranges
  • =SUM(A:A) — sum of entire column A

AVERAGE — Calculate Mean

=AVERAGE(range)

Returns the arithmetic mean.

Example: =AVERAGE(B2:B100)

COUNT and COUNTA

=COUNT(range) — counts cells with numbers

=COUNTA(range) — counts non-empty cells (including text)

Example:

  • =COUNT(A1:A100) — how many numbers?
  • =COUNTA(A1:A100) — how many non-empty cells?

MIN and MAX

=MIN(range) — smallest value

=MAX(range) — largest value

Example:

  • =MIN(C2:C100) — lowest value in range
  • =MAX(C2:C100) — highest value in range

IF — Conditional Logic

=IF(condition, value_if_true, value_if_false)

Returns different values based on a condition.

Examples:

  • =IF(A1>100, "Over budget", "OK")
  • =IF(B1>=60, "Pass", "Fail")
  • =IF(C1="Yes", 1, 0)

Nested IFs

For multiple conditions: =IF(A1>=90, "A", IF(A1>=80, "B", IF(A1>=70, "C", "F")))

Or use IFS (Excel 2019+): =IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C", TRUE, "F")

SUMIF and COUNTIF — Conditional Aggregation

=SUMIF(range_to_check, criteria, range_to_sum)

=COUNTIF(range, criteria)

Examples:

  • =SUMIF(A:A, "Sales", B:B) — sum of B where A is "Sales"
  • =COUNTIF(A:A, "Complete") — count of "Complete" in column A
  • =COUNTIF(B:B, ">100") — count of values over 100

SUMIFS and COUNTIFS — Multiple Criteria

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

Example: =SUMIFS(C:C, A:A, "Sales", B:B, "2024") — sum C where A is "Sales" AND B is "2024"

ROUND, ROUNDUP, ROUNDDOWN

=ROUND(number, decimals)

Examples:

  • =ROUND(3.14159, 2) returns 3.14
  • =ROUNDUP(3.1, 0) returns 4
  • =ROUNDDOWN(3.9, 0) returns 3

TEXT — Format Numbers as Text

=TEXT(value, format_code)

Examples:

  • =TEXT(A1, "$#,##0.00") — formats as currency
  • =TEXT(A1, "MM/DD/YYYY") — formats date
  • =TEXT(A1, "0000") — pads with zeros

Text Functions

CONCATENATE / CONCAT / &

Join text together.

Examples:

  • =A1&" "&B1 — joins with space
  • =CONCAT(A1, " ", B1) — same result
  • ="Hello, "&A1&"!" — includes literal text

LEFT, RIGHT, MID

Extract portions of text.

  • =LEFT(A1, 3) — first 3 characters
  • =RIGHT(A1, 4) — last 4 characters
  • =MID(A1, 2, 5) — 5 characters starting at position 2

LEN

=LEN(A1) — length of text in characters

TRIM

=TRIM(A1) — removes extra spaces

UPPER, LOWER, PROPER

  • =UPPER(A1) — ALL CAPS
  • =LOWER(A1) — all lowercase
  • =PROPER(A1) — Title Case

FIND and SEARCH

Find position of text within text.

  • =FIND("@", A1) — position of @ (case-sensitive)
  • =SEARCH("the", A1) — position of "the" (not case-sensitive)

SUBSTITUTE

=SUBSTITUTE(text, old_text, new_text)

Example: =SUBSTITUTE(A1, "-", "/") — replaces dashes with slashes

Date Functions

TODAY and NOW

  • =TODAY() — current date
  • =NOW() — current date and time

YEAR, MONTH, DAY

Extract parts of a date:

  • =YEAR(A1) — the year
  • =MONTH(A1) — the month (1-12)
  • =DAY(A1) — the day of month

DATE

Create a date from components: =DATE(2024, 6, 15) — returns June 15, 2024

EOMONTH

End of month: =EOMONTH(A1, 0) — end of same month =EOMONTH(A1, 1) — end of next month

NETWORKDAYS

Working days between dates (excludes weekends): =NETWORKDAYS(start_date, end_date)

Handling Errors

IFERROR

Catch errors and return something else: =IFERROR(A1/B1, 0) — returns 0 if division causes error

=IFERROR(VLOOKUP(...), "Not found") — returns "Not found" if lookup fails

Common Errors

ErrorMeaning
#DIV/0!Division by zero
#VALUE!Wrong type of value
#REF!Invalid reference
#NAME?Unrecognized formula name
#N/AValue not available
#NUM!Invalid numeric value

AI Prompt: Formula Help

I need an Excel formula that:

[Describe what you want in plain English]

My data is structured like:
- Column A: [description]
- Column B: [description]
- etc.

Please give me the formula and explain how it works.

What's Next

You can calculate. Now let's organize your data effectively.

Next chapter: Data management — sorting, filtering, cleaning, and organizing.

Spreadsheet FundamentalsData Management