Core - Required
Simple Formulas in Excel
BM101.04
⌨️ Keyboard Shortcuts:
- Alt + =: AutoSum.
- F2: Edit Cell.
⏰ Estimated Learning Time: 45 Minutes
Objective
- Understand how to create and use basic formulas for calculations.
- Learn key functions like SUM, AVERAGE, MIN, and MAX.
- Practice writing and editing formulas directly in the formula bar or within cells.
Fun Fact 🧮
- Did you know Excel can handle formulas with over 8,000 characters? This means you can create incredibly detailed calculations, but most formulas for everyday tasks are just a few characters long, like =SUM(A1:A10).
Key Concepts
- Formulas Basics:
- All formulas in Excel start with an = sign.
- Example: =A1 + B1 adds the values in cells A1 and B1.
- Basic Functions:
- SUM(range): Adds values in a range of cells. Example: =SUM(A1:A5).
- AVERAGE(range): Calculates the average of values in a range. Example: =AVERAGE(B1:B10).
- MIN(range): Finds the smallest value in a range. Example: =MIN(C1:C5).
- MAX(range): Finds the largest value in a range. Example: =MAX(C1:C5).
- Cell References:
- Relative Reference: Changes when the formula is copied (e.g., A1).
- Absolute Reference: Remains constant with $ (e.g., $A$1).
- AutoSum:
- A quick way to add values using the Σ button in the Ribbon.
- Shortcut: Alt + = to automatically sum a range.
Lesson Steps
- Open Excel:
- Create a new workbook and navigate to cell A1.
- Enter Data:
- In column A, enter items: "Apples," "Oranges," "Bananas".
- In column B, enter quantities: 10, 15, 20.
- In column C, enter prices: 2.50, 3.00, 1.80.
- Write Formulas:
- In column D, calculate the total cost using =B1*C1 and copy it down for other rows.
- Use SUM to calculate the total cost for all items: =SUM(D1:D3).
- Explore Other Functions:
- Use AVERAGE to calculate the average price: =AVERAGE(C1:C3).
- Use MIN and MAX to find the lowest and highest prices.
Common Errors
- Formulas Not Updating: Ensure you press Enter after typing a formula.
- Incorrect Cell References: Double-check that your references (e.g., A1) are accurate.
- #VALUE! Error: Ensure all cells referenced in a formula contain numbers, not text.
Exercise
Scenario: You are calculating sales data for a small shop. Perform the following:
Enter the following data into a worksheet:
| Item | Quantity | Price | Total |
|---|---|---|---|
| Apples | 10 | 2.50 | |
| Oranges | 15 | 3.00 | |
| Bananas | 20 | 1.80 |
Apply Formulas:
- In the Total column, calculate the total cost for each item by multiplying Quantity and Price.
- Use SUM to calculate the total revenue.
- Use AVERAGE, MIN, and MAX on the Price column.
Experiment:
- Change the quantity of an item and observe how the formulas update automatically.
Quiz
Solutions
- Question 1: The key that starts all formulas in Excel is =.
- Question 2: The function SUM(range) adds all the values in a range.
- Question 3: You can quickly calculate the sum of a range using Alt + =.