Math Functions in Google Sheets
Objective:
Understand and use essential mathematical functions like SUM, AVERAGE, MAX, MIN, and learn how to use autofill.
Step 1: Enter Data
Create the following table in a new sheet:
| Item | Quantity | Price (₹) |
|---|---|---|
| Pen | 10 | 5 |
| Notebook | 4 | 40 |
| Pencil | 6 | 3 |
| Eraser | 5 | 2 |
Step 2: Calculate Total for Each Item
In Column D (Total), calculate Quantity × Price:
-
In D2, type:
=B2*C2 -
Drag the fill handle from D2 to D5 to autofill totals for all items
This will give total cost per item.
Step 3: Use SUM Function
To get the grand total:
-
In D6, type:
=SUM(D2:D5)
This adds all item totals together.
Step 4: Use AVERAGE, MAX, MIN
Try these formulas in separate cells:
-
=AVERAGE(D2:D5)→ average item total -
=MAX(D2:D5)→ highest total -
=MIN(D2:D5)→ lowest total
You can also apply these to any other numeric column like Price or Quantity.
Step 5: Additional Useful Math & Count Functions
Below are some commonly used Google Sheets functions you should try:
| Function | Usage Example | Description |
=COUNT(A1:A10) |
Counts cells with numbers | Counts how many cells contain numbers |
=COUNTA(A1:A10) |
Counts all non-empty cells | Includes numbers and text |
=ROUND(D2, 0) |
Rounds a number to 0 decimals | You can change 0 to 1, 2, etc. |
=ROUNDUP(D2, 0) |
Always rounds up | |
=ROUNDDOWN(D2, 0) |
Always rounds down | |
=POWER(A2,2) |
Squares the value in A2 | Like A² |
=SQRT(A2) |
Square root of A2 | |
=MOD(A2, B2) |
Remainder of A2 ÷ B2 | |
=ABS(A2) |
Absolute value (removes minus) |
Try using these with random numbers in a separate sheet to understand their effect.
Step 6: Practice with Autofill
Try:
-
Type numbers 1 and 2 in two adjacent cells
-
Select both → drag fill handle down to auto-number
-
Type
Monday→ drag down to autofill weekdays
Autofill works great for patterns and repetitive values.
Mini Challenge – Sales Report Table
| Product | Sold Qty | Unit Price | Total Sales |
| Bag | 8 | 250 | |
| Shoes | 3 | 500 | |
| Watch | 2 | 1500 |
Tasks:
-
Calculate total sales per item using
=B2*C2 -
Use
=SUM()to get grand total -
Use
=AVERAGE()to get average sale value -
Use autofill to apply formulas down rows
-
Try
=MAX(),=MIN(),=ROUND(), and=COUNT()in new rows
