Day 3: Essential Formulas — Part 1

SUM adds all numbers in a range: =SUM(A1:A100). Common mistakes: (1) Including a header row in the range — SUM ignores text but it is sloppy practice. (2) Summing a column that contains text-formatted numbers — these are ignored silently, giving a wrong total. (3) Using + operators instead of SUM for large ranges — =A1+A2+A3... is harder to maintain. Always verify your SUM against a rough manual estimate.

AVERAGE calculates the arithmetic mean of a range. Important: AVERAGE ignores blank cells but includes zeros. If a zero represents "no data" in your dataset, use AVERAGEIF to exclude zeros: =AVERAGEIF(A1:A100,"<>0"). If you want to average only values meeting a condition: =AVERAGEIF(B1:B100,"North",A1:A100) averages column A values where column B is "North".

MIN and MAX return the smallest and largest values in a range unconditionally. MINIFS and MAXIFS (available in Excel 2019+) return the min or max meeting one or more conditions. Example: =MAXIFS(Revenue, Region, "North") returns the highest revenue from the North region only.

ROUND(number, num_digits) rounds a number to a specified number of decimal places. ROUND(3456.789, 2) returns 3456.79. ROUNDUP always rounds away from zero; ROUNDDOWN always rounds toward zero. In financial reports, rounding matters because: (1) displaying =A1*B1 may show a value that appears to add up correctly but the underlying unrounded values do not. (2) Slight rounding differences cause "total does not match sum" issues. Best practice: round at the final calculation step, not intermediates.

A named range assigns a descriptive name to a cell or range — for example, naming B2:B100 as "Revenue". Formulas using named ranges are more readable: =SUM(Revenue) vs =SUM(B2:B100). Named ranges are also absolute by default, so they do not shift when copied. Create via the Name Box or Formulas → Define Name. For large workbooks with multiple formula writers, named ranges reduce errors and make formulas self-documenting.