AI Reading
Quick summary of this article
Excel formulas and functions are essential for performing calculations and automating data analysis. This guide covers the basics of writing formulas, using cell references, applying common functions, and troubleshooting errors, with practical examples for real-world tasks like grading student performance.
- All formulas begin with an equal sign (=), and cell references can be relative (A1), absolute ($A$1), or mixed ($A1 or A$1) to control how they behave when copied.
- Excel follows the BODMAS rule (Brackets, Orders, Division, Multiplication, Addition, Subtraction) for calculation order, so formulas like =(A1+B1)*C1 work as expected.
- Common functions include SUM, AVERAGE, MAX, MIN, COUNT, COUNTA, and ROUND, which simplify tasks like adding ranges or finding the highest value.
- Common errors like #DIV/0!, #VALUE!, and #NAME? can be handled gracefully using the IFERROR function, e.g., =IFERROR(A1/B1, "Error").
- A practical example shows how to calculate total marks, percentage, and grades for 20 students across 8 subjects using SUM, division, and nested IF functions.
📘 Chapter 3: Working with Formulas & Functions
Excel’s true power lies in its ability to perform calculations and automate data analysis using formulas and functions. Whether you’re summing values, finding averages, or applying conditions, mastering formulas is essential for anyone working with data.
In this chapter, you’ll learn how to write formulas, use cell references, apply basic mathematical functions, and fix common formula errors.
🔣 What is a Formula in Excel?
A formula in Excel is an expression that calculates the value of a cell. All formulas begin with an equal sign (=). For example:
=A1 + B1 adds the values of cells A1 and B1.
🔗 Types of Cell References
- Relative Reference:
A1– changes when copied to another cell - Absolute Reference:
$A$1– stays constant when copied - Mixed Reference:
$A1orA$1– only part remains fixed
🧮 Basic Arithmetic Operators
+– Addition-– Subtraction*– Multiplication/– Division^– Exponent (Power)
Example: =B2*10% calculates 10% of the value in B2
🧠 Order of Operations (BODMAS)
Excel follows the BODMAS rule – Brackets, Orders, Division, Multiplication, Addition, and Subtraction.
Example: =(A1 + B1) * C1
🛠️ Commonly Used Functions
SUM(range)– Adds numbers in a rangeAVERAGE(range)– Returns the meanMAX(range)– Finds the highest valueMIN(range)– Finds the lowest valueCOUNT(range)– Counts numbers in a rangeCOUNTA(range)– Counts non-blank cellsROUND(value, num_digits)– Rounds to desired decimal
💡 Tips While Using Functions
- Use
=to start all formulas - Use
Ctrl + `to view all formulas in the sheet - Use function suggestions (auto-complete) while typing
- Check the Function Arguments dialog (
Shift + F3) for help
❗ Error Handling in Excel
#DIV/0!– Division by zero#VALUE!– Wrong data type in formula#NAME?– Misspelled function=IFERROR(A1/B1, "Error")– Handles errors gracefully
🎯 Real-Life Examples
=SUM(B2:B10)– Add total marks of students=AVERAGE(C2:C6)– Calculate average sales=MAX(E2:E100)– Find top performer=ROUND(F2, 2)– Round value to 2 decimal places
📝 Practice Task
- Create columns:
Product,Price,Quantity - Calculate
Total = Price * Quantity - Use
SUM()to get the grand total - Find the highest quantity using
MAX() - Use
IFERROR()to catch divide errors
🎓 Excel Example: 20 Students with 8 Subjects
Let’s understand how to apply Excel formulas practically using a real-life scenario. Imagine you are a teacher and you want to calculate the total marks, percentage, and assign grades for 20 students based on marks in 8 subjects.
📋 Sample Table Structure
| Student Name | Math | English | Science | History | Geography | Computer | Hindi | Physics | Total | Percentage | Grade |
|---|---|---|---|---|---|---|---|---|---|---|---|
| Amit | 88 | 75 | 80 | 68 | 72 | 90 | 65 | 70 | =SUM(B2:I2) | =J2/8 | =IF(K2>=90,”A+”,IF(K2>=80,”A”,IF(K2>=70,”B”,IF(K2>=60,”C”,”F”)))) |
| Priya | 76 | 85 | 78 | 80 | 90 | 88 | 84 | 79 | =SUM(B3:I3) | =J3/8 | =IF(K3>=90,”A+”,IF(K3>=80,”A”,IF(K3>=70,”B”,IF(K3>=60,”C”,”F”)))) |
🧮 Explanation of Formulas
- Total:
=SUM(B2:I2)– Adds all subject marks - Percentage:
=J2/8– Averages total marks over 8 subjects - Grade:
=IF(K2>=90,"A+",IF(K2>=80,"A",IF(K2>=70,"B",IF(K2>=60,"C","F"))))– Uses nested IF to assign grades
🔚 Conclusion
This example shows how Excel can automate academic performance tracking with accuracy. From SUM() to IF(), these core functions simplify real-world tasks and make grading a breeze.