Ranges and formulas
Name a row or column by its first and last cell. A formula does the adding for you.
Nagesh's total
Nagesh's six marks are in the cells B3, C3, D3, E3, F3 and G3. Sudhakar wants their total. With 40 students he would do this 40 times.
What is the quickest way to tell the sheet which cells to add?
What this lesson covers
The idea
A row or column of cells is described by Start:End, its first and last cells, and a formula such as =SUM(Start:End) or =AVERAGE(Start:End) computes the total or average of those cells.
Nagesh's total
Nagesh's six marks are in the cells B3, C3, D3, E3, F3 and G3. Sudhakar wants their total. With 40 students he would do this 40 times.
What is the quickest way to tell the sheet which cells to add?
- Write B3 + C3 + D3 + E3 + F3 + G3 each time
- Name the first and the last cell once
- Add them on a calculator and type the answer
Write the formulas
Pick a function, then tap the first cell and the last cell of a row or a column. The sheet writes the formula and shows the result. Do all three tasks.
Start:End and the formula
A row or column of cells is described by Start:End, its first and last cells, and a formula such as =SUM(Start:End) or =AVERAGE(Start:End) computes the total or average of those cells.
Nagesh's marks start in B3 and end in G3, so the row is B3:G3. =SUM(B3:G3) gives his total: 42 + 36 + 39 + 33 + 45 + 41 = 236.
Gowri's Odia, Telugu and English marks are B7:D7. =AVERAGE(B7:D7) gives (36 + 48 + 42) ÷ 3 = 42.
A column works the same way. D2:D6 are the English marks of the first five students, and =AVERAGE(D2:D6) gives 185 ÷ 5 = 37. A formula always starts with =.
Notes
A row or column of cells is described by Start:End, its first and last cells, and a formula such as =SUM(Start:End) or =AVERAGE(Start:End) computes the total or average of those cells.
Check yourself
Which range describes Gowri's marks in Odia, Telugu and English?
What number does =SUM(B2:G2) give?
Answer: 235
B2:G2 are the six marks of Anita in row 2: 38 + 41 + 35 + 44 + 40 + 37 = 235.
What number does =AVERAGE(F4:F6) give?
Answer: 39
F4:F6 are 36, 42 and 39. Their total is 117, so the average is 117 ÷ 3 = 39.
Which formula gives the average Science marks of all seven students?
- B3:G3. That is row 3, Nagesh's marks. Gowri is in row 7.
- B7:G7. That is all six subjects of Gowri. We only want the first three, B, C and D.
- B7:D7 — correct. Yes! The row is 7. The first cell is B7 (Odia) and the last cell is D7 (English).
- D2:D6. That is a column: the English marks of the first five students.
- =SUM(F2:F8). That adds the marks and gives a total. An average needs AVERAGE.
- =AVERAGE(F2:F8) — correct. Yes! Science is column F and the seven students are in rows 2 to 8.
- =AVERAGE(B8:G8). That is row 8, Tenzin's average over six subjects.
- =AVERAGE(F2:G8). F2:G8 covers two columns, Science and Social Science. We only want column F.