SUM, AVERAGE, MIN, and MAX

Calculate and transform data on MO-210

Domain 4 of Exam MO-210 (Perform Operations by Using Formulas and Functions) includes the skill Calculate and transform data. At Associate level, the core aggregate functions you must type accurately—and recognize when AutoSum offers them—are SUM, AVERAGE, MIN, and MAX. Projects rarely ask you to reinvent arithmetic; they ask you to put the right function in a labeled summary cell so a workbook totals, averages, or finds extremes correctly.

Typical task wording: "In cell B12, calculate the total of the values in B2:B11", "Enter a formula that averages the scores in D2:D20", "Display the highest and lowest unit prices using MAX and MIN", or "Use AutoSum to total the Quantity column." Graders check formula text as well as the displayed number, so =B2+B3+B4 may match one total today but still fail if the project required =SUM(B2:B4).

Function syntax at a glance

Arguments can be individual cells, ranges, or mixed lists: =SUM(A1, A3, B2:B10). On the exam, a single contiguous range is the usual pattern: =SUM(C2:C25).

SUM — totals the numbers

SUM adds every numeric value in its arguments. Text labels and empty cells inside the range do not break the formula; Excel skips non-numeric cells for the addition.

Worked example: monthly sales total

In B6 enter:

=SUM(B2:B5)

Result: 4650. Equivalent longhand =B2+B3+B4+B5 produces the same number but is brittle if rows are inserted. MOS projects prefer the function form, especially when AutoSum is mentioned.

If B4 were blank, =SUM(B2:B5) would still return 1200+950+1100 = 3250. Blank cells contribute nothing to the sum.

AVERAGE — mean of numeric values only

AVERAGE divides the sum of numeric values by how many numeric values it found—not by every cell in the range. Blanks and text are ignored in both the numerator and the divisor.

Worked example: quiz scores

Range D2:D6 contains 88, 92, blank, 75, and "N/A" (text).

=AVERAGE(D2:D6)

Excel uses only 88, 92, and 75. Mean = (88+92+75)/3 = 85. It does not divide by 5. That behavior matters when a project leaves empty score cells for students who have not taken a quiz yet—AVERAGE correctly excludes them.

Contrast with a mistaken approach of typing =SUM(D2:D6)/5, which would understate the average by counting empty or text cells in the divisor. On MO-210, use AVERAGE when the task says average, mean, or average score.

MIN and MAX — extremes

MIN returns the smallest number; MAX returns the largest. Both ignore blanks and text the same way SUM and AVERAGE do for aggregation of numbers.

Worked example: price list

Unit prices in E2:E8: 14.99, 9.50, 22.00, 18.75, 9.50, 31.00, 12.25.

=MIN(E2:E8)   → 9.50
=MAX(E2:E8)   → 31.00

Duplicate lows or highs are fine—MIN/MAX report the extreme value, not how many times it appears. Tasks often place MIN in one summary cell and MAX in another (for example, "Lowest price" and "Highest price" labels in column G).

AutoSum on the exam

AutoSum is the fastest Associate path to a SUM (and, from its menu, to Average, Count Numbers, Max, and Min).

Ways to start AutoSum:

1. Select the cell below (or to the right of) a numeric column/row → Home → AutoSum (Σ) or Formulas → AutoSum.
2. Keyboard: Alt+= (Windows) selects a likely range and inserts =SUM(...).
3. Open the AutoSum drop-down to choose Average, Max, Min, or Count Numbers instead of Sum.

Worked AutoSum sequence

1. Select B12 directly under B2:B11.
2. Press Alt+= or click AutoSum.
3. Excel proposes =SUM(B2:B11) with the range highlighted.
4. Confirm the marching ants cover exactly the data—not a header, not an extra blank column total.
5. Press Enter.

Exam habit: Always verify the suggested range. If Excel grabs B1:B11 and B1 is a title, or stops one row short because of a blank, fix the range before Enter. Graders care that the formula references the cells named in the project instructions.

When the task says "use AutoSum to average," open the AutoSum menu and choose Average so the formula becomes =AVERAGE(...), not SUM.

Where to put summary formulas

MO-210 projects specify the destination cell. Do not invent a total row elsewhere. Common patterns:

• Total under a column of values (=SUM(B2:B20) in B21).
• Dashboard block to the right: Average in H2, Max in H3, Min in H4, each pointing at the same source range.
• Table Total Row (Domain 3) may already offer Sum/Average via the drop-down—if the task says Total Row, use that; if it says enter a formula in cell X, type the function in that cell.

Structured references also work: =SUM(Sales[Amount]) or =AVERAGE(Sales[Score]) when the range is an Excel Table. Prefer structured syntax if the project already named the table and asked for a formula against a column.

Combining aggregates with other Domain 4 skills

You may need an absolute reference on a rate while summing quantities, or an IF that flags values above AVERAGE. Keep the aggregate itself simple and correct first:

=IF(B2>AVERAGE($B$2:$B$20),"Above","At or below")

Here AVERAGE supplies the threshold; absolute locks keep the range fixed when you fill the IF down. That pattern is Associate-level and appears when calculate-and-transform skills meet references and IF in the same project.

Common MO-210 traps

• Typing =B2+B3+… when the instruction named SUM, AVERAGE, MIN, or MAX.
• Accepting AutoSum's range without checking—headers, blank gaps, or wrong columns.
• Using AVERAGE on a range that includes zeros you thought were "blank"—zeros are numeric and pull the mean down.
• Confusing MIN/MAX with SORT (SORT rearranges a spill list; MIN/MAX return a single extreme).
• Placing the formula in the wrong cell even when the math is perfect.
• Averaging with SUM/COUNTA when empty text-looking cells should be excluded—prefer AVERAGE for numeric means.

Practice sequence for exam day

1. Enter ten numbers in A1:A10; in A11 use AutoSum and confirm =SUM(A1:A10).
2. In B1 enter =AVERAGE(A1:A10); clear A5 and watch the average recalculate over nine numbers.
3. In C1 and C2 enter =MIN(A1:A10) and =MAX(A1:A10); change one value and verify both update.
4. From the AutoSum drop-down, insert Average and Max into labeled cells so muscle memory covers the menu, not only Σ.

Master these four aggregates plus AutoSum range verification, and you cover the primary summary-statistics half of Domain 4's Calculate and transform data objective.