Essential Functions for Analysis

Which functions should beginners know?

Example

In Excel 2019 and later, and in Google Sheets, IFS can replace nested IFs: =IFS(F2>=5000,"Large",F2>=1000,"Medium",TRUE,"Small")

Conditional calculations

Use these for questions like "revenue by region" or "number of orders above a threshold".

How it works

• Functions take inputs called arguments.
• Ranges specify which cells to evaluate.
• Criteria specify what should match. Text and comparison operators go in quotes, e.g., ">1000".
• Some spreadsheet locales use semicolons instead of commas to separate arguments: =SUM(A1;A10).

Common mistakes

• Averages can mislead when values are highly skewed. Check MEDIAN too.
• Ranges that are too short silently exclude new rows (e.g., F2:F100 when data reaches row 150). Use full columns or formal tables.
• COUNT vs COUNTA: COUNT ignores text, so it undercounts text IDs.
• Numbers stored as text are ignored by SUM.
• Confirm the range and the business definition before trusting a result.

Try it: Calculate total revenue, average order value, and the number of orders in a sample table. Then use COUNTIF to count orders from one region.