Spreadsheets and Formulas

Learn to use Excel or Google Sheets to automate calculations, analyze data, and solve real-world problems efficiently. These are the most practical math tools for business and personal finance.

Why Spreadsheets Matter

Automation: Calculate once, use forever
Accuracy: Eliminate manual calculation errors
Speed: Handle thousands of calculations instantly
Analysis: Visualize data and identify patterns
Professional: Essential skill in modern workplace

When to use spreadsheets vs calculator:

• Repetitive calculations
• Multiple scenarios ("what if" analysis)
• Data that changes over time
• Complex formulas
• Need to share/document work

Spreadsheet Basics

Cell References

Cell: Identified by column letter + row number

• A1 = Column A, Row 1
• B5 = Column B, Row 5
• Z100 = Column Z, Row 100

Relative Reference: A1 (adjusts when copied) Absolute Reference: $A$1 (stays fixed when copied) Mixed Reference: $A1 or A$1 (one part fixed)

Basic Formulas

All formulas start with =

Basic Operations:

• Addition: =A1+B1
• Subtraction: =A1-B1
• Multiplication: =A1*B1
• Division: =A1/B1
• Exponent: =A1^2 (A1 squared)

Example: Calculate total

• A1: 45
• B1: 30
• C1: =A1+B1 → Result: 75

Order of Operations (PEMDAS)

Spreadsheets follow standard math order:

1. Parentheses
2. Exponents
3. Multiplication & Division
4. Addition & Subtraction

Example: =(A1+B1)*C1 vs =A1+B1*C1

• If A1=10, B1=5, C1=3
• With parentheses: (10+5)*3 = 45
• Without: 10+5*3 = 25

Essential Functions

SUM

Add multiple cells

Syntax: =SUM(range)

Examples:

• =SUM(A1:A10) → Sum of A1 through A10
• =SUM(A1:C1) → Sum of A1, B1, C1
• =SUM(A1:A10,C1:C10) → Sum of two ranges
• =SUM(A:A) → Sum entire column A

Use case: Total sales, total expenses, sum of grades

AVERAGE

Calculate mean

Syntax: =AVERAGE(range)

Examples:

• =AVERAGE(B2:B50) → Average of B2 through B50
• =AVERAGE(A1,A3,A5) → Average of specific cells

Use case: Average revenue, mean score, typical value

COUNT and COUNTA

COUNT: Count cells with numbers
COUNTA: Count non-empty cells

Syntax:

• =COUNT(range)
• =COUNTA(range)

Examples:

• =COUNT(A1:A100) → How many cells have numbers
• =COUNTA(A1:A100) → How many cells aren't empty

Use case: Count responses, count sales, count entries

MIN and MAX

Find smallest or largest value

Syntax:

• =MIN(range)
• =MAX(range)

Examples:

• =MIN(B2:B50) → Smallest value in range
• =MAX(B2:B50) → Largest value in range

Use case: Lowest price, highest score, range analysis

IF (Conditional Logic)

Make decisions based on conditions

Syntax: =IF(condition, value_if_true, value_if_false)

Examples:

Pass/Fail:

• =IF(A1>=70, "Pass", "Fail")
• If A1 is 70 or more, shows "Pass", otherwise "Fail"

Discount eligibility:

• =IF(B1>=1000, B1*0.1, 0)
• If purchase ≥ $1,000, 10% discount, else no discount

Performance bonus:

• =IF(C1>100000, "Bonus", "No Bonus")

Nested IF (multiple conditions):

• =IF(A1>=90, "A", IF(A1>=80, "B", IF(A1>=70, "C", "F")))
• Grades based on score

SUMIF and COUNTIF

Sum or count based on criteria

SUMIF Syntax: =SUMIF(range, criteria, sum_range)

Examples:

Sum sales over $100:

• =SUMIF(B2:B100, ">100", B2:B100)

Sum specific product sales:

• =SUMIF(A2:A100, "Widget", B2:B100)
• Sums values in B where A contains "Widget"

COUNTIF Syntax: =COUNTIF(range, criteria)

Count values over 50:

• =COUNTIF(C2:C100, ">50")

Count specific item:

• =COUNTIF(A2:A100, "Yes")

VLOOKUP (Lookup Values)

Find value in table

Syntax: =VLOOKUP(lookup_value, table_range, column_number, FALSE)

Example: Price lookup table

• Column A: Product ID
• Column B: Product Name
• Column C: Price

Formula: =VLOOKUP("P101", A2:C50, 3, FALSE)

• Looks for "P101" in column A
• Returns value from column 3 (Price)

Use case: Product prices, employee info, grade scales

Financial Functions

PMT (Loan Payment)

Calculate monthly payment

Syntax: =PMT(rate, nper, pv)

• rate = interest rate per period
• nper = number of payments
• pv = present value (loan amount)

Example: $200,000 mortgage at 5% for 30 years

• Rate: 5%/12 or 0.05/12
• Nper: 30*12 or 360
• Formula: =PMT(5%/12, 30*12, 200000)
• Result: -$1,073.64 (negative = payment out)

Display as positive: =-PMT(5%/12, 30*12, 200000)

FV (Future Value)

Calculate investment growth

Syntax: =FV(rate, nper, pmt, pv)

Example: $10,000 invested at 7% for 10 years

• =FV(7%, 10, 0, -10000)
• Result: $19,671.51

Example: Monthly savings of $500 for 30 years at 8%

• =FV(8%/12, 30*12, -500, 0)
• Result: $745,179.54

PV (Present Value)

Calculate current value of future amount

Syntax: =PV(rate, nper, pmt, fv)

Example: Need $50,000 in 10 years at 6%

• =PV(6%, 10, 0, 50000)
• Result: -$27,919.74 (need to invest this much today)

IRR (Internal Rate of Return)

Calculate investment return rate

Syntax: =IRR(values)

• Values = series of cash flows (negative = investment, positive = return)

Example:

• A1: -10000 (initial investment)
• A2: 3000 (year 1 return)
• A3: 3500 (year 2)
• A4: 4000 (year 3)
• A5: 4500 (year 4)
• Formula: =IRR(A1:A5)
• Result: ~20% annual return

Practical Applications

Personal Budget

Setup:

Highlight over-budget:

• D2: =IF(C2>B2, "OVER", "OK")

Sales Tracker

Columns:

• A: Date
• B: Product
• C: Quantity
• D: Unit Price
• E: Total (=C*D)
• F: Commission (=E*0.05)

Summary:

• Total Sales: =SUM(E:E)
• Total Commission: =SUM(F:F)
• Average Sale: =AVERAGE(E:E)
• Largest Sale: =MAX(E:E)

Investment Calculator

Inputs:

• A1: Initial Investment = 10000
• A2: Monthly Addition = 500
• A3: Annual Return = 8%
• A4: Years = 25

Calculation:

• A5: =FV(A3/12, A4*12, -A2, -A1)
• Shows: Future value of investment

Scenario analysis: Change inputs to see different outcomes

Loan Comparison

Compare total interest to choose best option.

Grade Calculator

Setup:

• A: Assignment Name
• B: Score
• C: Max Points
• D: Percentage (=B/C*100)
• E: Weight
• F: Weighted Score (=D*E)

Bottom row:

• Total Weight: =SUM(E:E) (should be 100%)
• Final Grade: =SUM(F:F)/SUM(E:E)*100

Business Metrics Dashboard

Revenue Section:

• Monthly Revenue: =SUM(B2:B13)
• Average Month: =AVERAGE(B2:B13)
• Growth Rate: =(B13-B2)/B2*100

Customer Section:

• Total Customers: =COUNTA(C2:C100)
• Average Order: =SUM(Revenue)/COUNT(Orders)
• Conversion Rate: =(Purchases/Visitors)*100

Break-Even Calculator

Inputs:

• A1: Fixed Costs = 10000
• A2: Price per Unit = 50
• A3: Variable Cost per Unit = 20

Calculations:

• A4: Contribution Margin = =A2-A3
• A5: Break-Even Units = =A1/A4
• A6: Break-Even Revenue = =A5*A2

Data Visualization

Basic Charts

Create charts to visualize data:

• Line Chart: Trends over time (sales, stock prices)
• Bar/Column Chart: Comparing categories (sales by product)
• Pie Chart: Parts of a whole (budget breakdown)
• Scatter Plot: Relationships (price vs demand)

Steps (general):

1. Select data range
2. Insert → Chart
3. Choose chart type
4. Customize labels, titles

Conditional Formatting

Highlight cells based on values

Use cases:

• Highlight negative numbers in red
• Show top 10 values in green
• Color scale for performance (red → yellow → green)
• Show cells above/below average

Example: Highlight sales over $1,000

• Select range
• Conditional Formatting → Highlight Cell Rules → Greater Than
• Enter 1000, choose format

Data Analysis Tips

Scenario Analysis (What-If)

Method: Change inputs to see impact

Example: Retirement savings

• Try different contribution amounts
• Test various return rates
• See impact of starting age

Data Table feature: Automatically test multiple scenarios

Goal Seek

Find input needed for desired output

Example: What interest rate needed to reach $1M in 20 years?

• Set up FV formula
• Data → What-If Analysis → Goal Seek
• Set cell (result) to 1000000
• By changing cell (interest rate)

Pivot Tables

Summarize large datasets

Use when:

• Thousands of rows
• Need to group and summarize
• Multiple dimensions (sales by region by product)

Example: Sales data

• Rows: Product
• Columns: Month
• Values: Sum of Sales
• Quickly see which products sell best each month

Common Mistakes to Avoid

Circular References

Problem: Cell refers to itself (directly or indirectly)

• A1: =A1+10 ❌
• A1: =B1, B1: =A1 ❌

Solution: Check formula references

Hardcoded Values

Bad: =A1*0.08 (what is 0.08?) Good: Put 0.08 in named cell (B1: Tax Rate), use =A1*B1

Why: Easier to update, clear what values mean

Not Using Absolute References

Problem: Copy formula and it breaks

• D1: =B1/C1 works
• Copy to D2: =B2/C2 (might want B2/$C$1)

Solution: Use $ when needed: =B1/$C$1

Ignoring Errors

Common errors:

• #DIV/0!: dividing by zero
• #VALUE!: wrong data type
• #REF!: invalid cell reference
• #N/A: value not available (VLOOKUP failed)

Fix: Check formulas, use IFERROR to handle gracefully

Keyboard Shortcuts

Essential shortcuts (Excel/Sheets similar):

• Ctrl+C: copy
• Ctrl+V: paste
• Ctrl+Z: undo
• Ctrl+S: save
• Ctrl+;: insert today's date
• F2: edit cell
• Alt+=: AutoSum
• Ctrl+Arrow: jump to edge of data

Practice Exercises

1. Budget Tracker: Create monthly budget with categories, actual vs budgeted, and totals

2. Loan Calculator: Compare 3 loan options (different rates/terms), show monthly payments and total interest

3. Investment Growth: Calculate future value of $10,000 + $500/month for 20 years at 7%

4. Sales Dashboard: Track sales by product, calculate totals, averages, and commission (5% of sales)

5. Grade Calculator: Create gradebook with weighted categories, calculate final grade

6. Break-Even Analysis: Given fixed costs, price, and variable costs, calculate break-even point

Key Takeaways

✓ Start with equals sign: all formulas begin with =
✓ Use cell references: don't hardcode values
✓ Master basic functions: SUM, AVERAGE, IF, VLOOKUP
✓ Absolute vs relative: use $ when needed
✓ Visualize data: charts make patterns obvious
✓ Practice regularly: spreadsheet skills improve with use

Real-World Applications

• Personal Finance: Budget tracking, loan comparisons, investment planning
• Business: Sales analysis, financial projections, KPI dashboards
• Project Management: Task tracking, timeline calculations, resource allocation
• Data Analysis: Summarizing large datasets, identifying trends
• Education: Grade calculations, attendance tracking
• Home Projects: Cost estimation, material calculations

Resources

Learn More:

• Excel/Sheets built-in help and tutorials
• YouTube: ExcelIsFun, Google Workspace
• Practice datasets: Kaggle, data.gov

Templates:

• Microsoft Office templates
• Google Sheets template gallery
• Create your own and reuse

Conclusion

You've completed the mathematics course! You now have the foundation to:

• Perform calculations confidently
• Make informed financial decisions
• Analyze business metrics
• Solve practical problems
• Use tools effectively

Remember: Mathematical competency comes from practice. Apply these skills daily in your life and business. Start with small calculations and gradually tackle more complex problems.

Keep this course as a reference: return to specific chapters as needed. Mathematics is a tool; the more you use it, the sharper it becomes.