Skip to main content
FreeAcademy

Formulas and Functions

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