Excel Formulas and Functions (SUM, AVERAGE, IF, COUNTIF, etc.)
Complete Guide for Delhi Police Computer Section
1. Introduction to Excel Formulas
Excel is not just about entering numbers — it's about making your data work for you. Formulas and functions are the heart of Excel that turn dull numbers into meaningful insights.
Formulas vs Functions:
Formula
Written by the user manually
Example: =A1 + A2 + A3
Function
Predefined formula by Excel
Example: =SUM(A1:A3)
Important Tip: Every function in Excel starts with an "=" sign.
2. Common Excel Functions You Must Know
Master these essential functions for Delhi Police exams and practical use.
| Function | Purpose | Example |
|---|---|---|
| SUM() | Adds values | =SUM(A1:A10) |
| AVERAGE() | Finds mean value | =AVERAGE(B1:B5) |
| MAX() / MIN() | Largest or smallest value | =MAX(C1:C10) |
| COUNT() | Counts cells with numbers | =COUNT(D1:D20) |
| COUNTA() | Counts non-empty cells | =COUNTA(D1:D20) |
| IF() | Conditional formula | =IF(A1>50, "Pass", "Fail") |
| COUNTIF() | Counts cells meeting condition | =COUNTIF(A1:A10, ">70") |
| SUMIF() | Adds values meeting condition | =SUMIF(B1:B10, ">5000") |
| ROUND() | Rounds to nearest number | =ROUND(A1, 2) |
3. Previous Year Delhi Police Questions
Which Excel function is used to add all numbers in a range of cells?
Year: 2020 | Shift: 1
View Answer
SUM()
The formula =AVERAGE(10, 20, 30) will return —
Year: 2021 | Shift: 2
View Answer
20
What is the correct syntax for the IF function?
Year: 2020 | Shift: 2
View Answer
=IF(logical_test, value_if_true, value_if_false)
In Excel, which function counts cells containing numbers only?
Year: 2022 | Shift: 1
View Answer
COUNT()
Which function adds only those values that meet a specific condition?
Year: 2021 | Shift: 1
View Answer
SUMIF()
If A1=40, A2=50, what is =IF(A1>A2, "Yes", "No")?
Year: 2022 | Shift: 2
View Answer
No
What is the shortcut key to insert a function in Excel?
Year: 2021 | Shift: 2
View Answer
Shift + F3
4. Excel Tricks for Exams
Trick 1: Use Alt + = to instantly insert SUM formula
Trick 2: Use Ctrl + Shift + L to apply filters quickly
Trick 3: If you forget a formula, type "=A" and Excel shows auto suggestions
Trick 4: Use Ctrl + ` (backtick) to see all formulas in sheet
Exam Tip: IF & COUNTIF are most frequently asked in Delhi Police and SSC exams — never skip them!
5. Practice Assignment
Task:
Create a salary sheet of 10 employees with the following columns:
- Employee Name
- Basic Salary
- Bonus
- Total Salary (using SUM formula)
- Status (use IF to display "Above 20K" or "Below 20K")
Format the table neatly, center-align all text, and bold the header row.
Solution Hint: Use =SUM(B2:C2) for Total Salary and =IF(D2>20000, "Above 20K", "Below 20K") for Status column.
6. Quick Recap
Formulas vs Functions: Formulas = manual equations; Functions = predefined formulas
Essential Functions: SUM, AVERAGE, IF, COUNTIF, SUMIF, MAX, MIN, COUNT, COUNTA
Important Shortcuts: Alt+= (SUM), Shift+F3 (Insert Function), Ctrl+` (Show Formulas)
Exam Focus: IF & COUNTIF are most frequently asked in Delhi Police exams
Tip: Practice these functions regularly to build confidence for both exams and practical use.
7. Practice Questions
Q1. What is the difference between COUNT and COUNTA functions?
View Answer
COUNT counts only cells with numbers, while COUNTA counts all non-empty cells (including text, dates, etc.)
Q2. Write an IF formula to assign "Pass" if marks are greater than or equal to 40, otherwise "Fail".
View Answer
=IF(A1>=40, "Pass", "Fail")
Q3. How would you count how many students scored above 70 in a range A1:A50?
View Answer
=COUNTIF(A1:A50, ">70")
Q4. What is the purpose of the ROUND function and how is it used?
View Answer
ROUND function rounds a number to a specified number of digits. Example: =ROUND(12.6789, 2) returns 12.68
Q5. When would you use SUMIF instead of SUM?
View Answer
Use SUMIF when you need to add only those values that meet a specific condition, like summing all sales above $5000
You've completed Excel Formulas and Functions!
Courage Tip: Practice these functions with real data to master Excel logic — don't just memorize! Remember that IF, COUNTIF, and SUM functions are exam favorites. Use the shortcuts like Alt+= for quick SUM formulas to save time during exams.
Master Excel Formulas for Delhi Police Exam!
Join Courage Library for comprehensive study materials and expert guidance.
Be a Couragian!