Microsoft Excel & Google Sheets: How to Use SUM, AVERAGE, and IF Formulas
Spreadsheet formulas automate calculations instantly. Learn the core building blocks used in financial models, school grading, and home budgets.
Whether you are organizing personal finances, auditing household expenses, or calculating school grades, Microsoft Excel and Google Sheets share identical foundational formula syntax.
All formulas begin with the equals sign (=). This tells the software: 'Do not display the text I type; calculate the mathematical result instead.'
The Three Fundamental Formulas
| Function | Formula Syntax | What It Does | Real-World Example |
|---|---|---|---|
| SUM | =SUM(B2:B20) | Adds up all numbers in a contiguous range | Totaling a column of monthly bills |
| AVERAGE | =AVERAGE(C2:C31) | Calculates the arithmetic mean (Total ÷ Count) | Finding your average daily electricity cost |
| COUNT | =COUNT(A2:A50) | Counts how many cells contain numeric numbers | Tracking how many days you recorded expenses |
| IF | =IF(C2>B2, "Alert", "OK") | Tests a logical condition: returns value A if true, value B if false | Flagging expenses that exceeded budget |
How to Write an IF Statement Step-by-Step
An IF statement has three parts separated by commas: =IF(Logical_Test, Value_If_True, Value_If_False)
• Logical Test: The question you are asking (e.g. `C2 > 100` or `B2 = "Paid"`).
• Value if True: What Excel displays if the test is TRUE (e.g. `"Overdue"` or `C2 * 0.1`).
• Value if False: What Excel displays if the test is FALSE (e.g. `"On Time"` or `0`).
Click on cell D2 where you want the result to appear.
Type `=IF(B2>=50, "Pass", "Retest")` and press Enter.
Hover your cursor over the bottom-right corner of cell D2 until it becomes a black cross (+). Double-click or drag down to copy the formula for all rows instantly!
✨ Key Takeaways
- All spreadsheet formulas in Excel and Google Sheets must begin with an equals sign (=).
- Use colon notation (B2:B10) to designate a contiguous range of cells.
- =SUM() calculates totals; =AVERAGE() calculates means; =IF() creates logical decision rules.
- The small square fill handle at the bottom-right of a cell copies formulas down hundreds of rows in one click.