Formulas in spreadsheet applications like Microsoft Excel or Google Sheets help you quickly calculate, analyze, and summarize data. Some of the most used simple formulas include:

1. SUM

Purpose: Adds values in a selected range of cells.
Example: If you have sales figures in cells B2 to B10, the formula =SUM(B2:B10) will calculate the total sales.
Use Case: Quickly find total revenue, expenses, marks, or any cumulative values.

2. AVERAGE

Purpose: Calculates the mean (average) of the values in a given range.
Example: =AVERAGE(C2:C12) will return the average test score of students listed in cells C2 to C12.
Use Case: Useful for tracking student performance, analyzing employee productivity, or finding the average of financial metrics.

3. COUNT / COUNTA

COUNT: Counts only numeric entries in a range.
Example: =COUNT(D2:D20) counts how many cells in column D contain numbers.
COUNTA: Counts all non-empty entries (both numbers and text).
Example: =COUNTA(D2:D20) will count all filled cells in column D, regardless of whether they contain numbers, text, or dates.
Use Case: Useful for counting the number of students who submitted assignments (COUNTA) versus those who received numerical grades (COUNT).

4. MIN / MAX

MIN: Returns the smallest value in a range.
Example: =MIN(E2:E15) gives the lowest score in a test.
MAX: Returns the largest value in a range.
Example: =MAX(E2:E15) shows the highest score in the test.
Use Case: Useful for quickly identifying top performers, lowest prices, or extreme values in datasets.

5. Autofill & Drag-Down Formula Shortcuts

Instead of typing the same formula repeatedly, you can drag the fill handle (the small square at the bottom-right corner of a selected cell) down or across to apply the formula to other rows or columns.
Example: If you write =B2+C2 in cell D2, dragging the formula down automatically adjusts it to =B3+C3, =B4+C4, etc.
Shortcut Benefit: Saves time and reduces errors when applying the same calculation to multiple rows or columns.

Exercise: Using Simple Formulas in Excel/Google Sheets

You are given the following dataset of students’ test scores:

Student (Column A)	Score (Column B)
A	78
B	65
C	90
D	55
E	88
F	72
G	95
H	60

Questions

Use a formula to calculate the total score of all students.
Find the average score of the students.
Count how many students have a numeric score entered.
Identify the lowest score and the highest score.
If the class had 20 students but only 8 wrote the test, use a formula to count the number of filled entries in the Score column.

Answers

Total Score

Formula: =SUM(B2:B9)
Answer: 603

Average Score

Formula: =AVERAGE(B2:B9)
Answer: 75.38 (approx. 75.4)

Count of Numeric Scores

Formula: =COUNT(B2:B9)
Answer: 8

Lowest and Highest Scores

Formula for lowest: =MIN(B2:B9) → 55
Formula for highest: =MAX(B2:B9) → 95

Count of Filled Entries

Formula: =COUNTA(B2:B21)
Answer: 8 (since only 8 students wrote the test)

M4:S4 - Using Simple Formulas

PREVIOUS STEP

Exercise: Sorting, Filtering and Tabulating Data

NEXT STEP