SUM, AVERAGE and ROUND practice exercises

The basics & math functions, with examples

Every example runs on q1-orders.xlsx, the same sheet the exercises use, and the results are calculated from that data.

• SUM

  Adds up numbers, usually a whole column or range.

  SUM(number1, [number2], …)

  Example

  =SUM(F2:F11)→returns$18,700.00

• AVERAGE

  Adds the numbers and divides by how many there are. Empty cells and text are skipped.

  AVERAGE(number1, [number2], …)

  Example

  =AVERAGE(E2:E11)→returns5.2

• MAX

  Returns the largest number in a range. MIN returns the smallest.

  MAX(number1, [number2], …)

  Example

  =MAX(F2:F11)→returns$4,800.00

• COUNT

  Counts the cells that contain numbers. To count cells with text as well, use COUNTA.

  COUNT(value1, [value2], …)

  Example

  =COUNT(F2:F11)→returns10

• ROUND

  Rounds a number to the decimal places you choose. Negative places round to tens, hundreds and so on.

  ROUND(number, num_digits)

  Example

  =ROUND(AVERAGE(F2:F11), -2)→returns$1,900.00

Common mistakes

1. 1

   Numbers stored as text

   If a number sits on the left of its cell or has a small green triangle, Excel sees it as text, and SUM and AVERAGE skip it without any warning. Convert it to a real number before totalling, for example with Data → Text to Columns.

2. 2

   Mixing up blanks and zeros

   AVERAGE ignores empty cells but counts zeros. If missing data was typed as 0, the average comes out lower than it should. Leave unknown values empty.

3. 3

   Thinking formatting rounds the number

   Showing two decimal places only changes how a number looks; Excel keeps the full value and uses it in later sums. That's why a column of rounded-looking prices can total a cent off. Use ROUND when the stored value itself must be rounded.