📊 Formulas & Functions in Excel

1. SUM

 Adds values together.

 Example 1: =SUM (10, 20, 30) → 60

 Example 2: =SUM (A1:A5) (if A1:A5 = 5, 8, 12, 15, 20) → 60

 Example 3: =SUM(A1+A2+A3+A4+A5)

2. AVERAGE

 Finds the mean (total ÷ number of values).

 Example 1: =AVERAGE(15, 25, 35) → 25

 Example 2: =AVERAGE(B1:B4) (if B1:B4 = 40, 50, 60, 70) → 55

3. MIN

 Returns the smallest value.

 Example 1: =MIN(18, 25, 5, 40) → 5

 Example 2: =MIN(C1:C6) (if C1:C6 = 12, 18, 5, 25, 30, 20) → 5

4. MAX

 Returns the largest value.

 Example 1: =MAX(10, 50, 75, 100) → 100

 Example 2: =MAX(D1:D5) (if D1:D5 = 35, 20, 15, 55, 45) → 55

5. COUNT

 Counts only numbers.

 Example 1: =COUNT(10, "Hello", 20, "World") → 2

 Example 2: =COUNT(E1:E6) (if E1:E6 = 5, Apple, 7, Mango, 10, Banana) → 3

6. COUNTA

 Counts everything that is not empty (numbers, text, dates, etc.).

 Example 1: =COUNTA(10, "Hello", 20, "") → 3

 Example 2: =COUNTA(F1:F5) (if F1:F5 = A, 15, Mango, , 40) → 4

🏢 Case Studies

📌 Case Study 1: Sales Data

A shop records daily sales for a week:

1200, 1500, 1800, 900, 2000, 1750, 1600

 Total Sales: =SUM(A1:A7) → 10,750

 Average Sales: =AVERAGE(A1:A7) → 1,536

 Highest Sale (Best Day): =MAX(A1:A7) → 2000

 Lowest Sale (Slowest Day): =MIN(A1:A7) → 900

 Number of Days Recorded: =COUNT(A1:A7) → 7

✅ Business Insight: Manager can see average performance and identify the best/worst days.

📌 Case Study 2: Student Marks

Marks of 5 students:
85, 70, 95, 60, 75

 Total Marks: =SUM(B1:B5) → 385

 Average Marks: =AVERAGE(B1:B5) → 77

 Highest Mark: =MAX(B1:B5) → 95

 Lowest Mark: =MIN(B1:B5) → 60

 Number of Students: =COUNT(B1:B5) → 5

✅ Educational Insight: Teacher can quickly analyze class performance.

📌 Case Study 3: Attendance Register

Class attendance sheet:

Present, Absent, Present, Present, Absent, Present

 Total Entries: =COUNTA(C1:C6) → 6

 Total “Present” Count (using COUNTIF): =COUNTIF(C1:C6, "Present") → 4

 Total “Absent” Count (using COUNTIF): =COUNTIF(C1:C6, "Absent") → 2

✅ HR/Education Insight: Attendance percentage can be tracked.

📌 Case Study 4: Inventory Stock

Items in warehouse:
Shoes=50, Shirts=75, Pants=100, Caps=20, Jackets=40

 Total Stock: =SUM(D1:D5) → 285

 Average Stock: =AVERAGE(D1:D5) → 57

 Lowest Stock Item: =MIN(D1:D5) → 20 (Caps)

 Highest Stock Item: =MAX(D1:D5) → 100 (Pants)

 Total Items Counted: =COUNT(D1:D5) → 5

✅ Business Insight: Helps identify stock shortages & best-selling products.

🔹 Problem 1: Sales Report Analysis

A company has sales for 5 products in different regions:

Product North South East West

Shoes 1200 1500 1100 1000

Shirts 800 950 700 650

Pants 1500 1800 1700 1600

Caps 300 400 250 200

Jackets 900 1000 850 750

Tasks:

1. Find total sales of Shoes across all regions.

👉 =SUM(B2:E2) → 4800

2. Find average sales of Pants across all regions.

👉 =AVERAGE(B4:E4) → 1650

3. Find region with minimum sales for Jackets.

👉 =MIN(B6:E6) → 750

4. Find region with maximum sales for Shirts.

👉 =MAX(B3:E3) → 950

5. Count how many regions recorded more than 1000 sales for Shoes.
👉 =COUNTIF(B2:E2, ">1000") → 3

🔹 Problem 2: Student Performance

Student marks (out of 100):

Student Math Science English History

A 85 90 78 88
Student Math Science English History

B 70 75 80 72

C 95 88 92 90

D 60 65 58 62

Tasks:

1. Find total marks of Student A.

👉 =SUM(B2:E2) → 341

2. Find average marks of Student C.

👉 =AVERAGE(B4:E4) → 91.25

3. Find highest marks in Science subject.

👉 =MAX(C2:C5) → 90

4. Find lowest marks in English subject.

👉 =MIN(D2:D5) → 58

5. Count how many students scored more than 80 in Math.

👉 =COUNTIF(B2:B5, ">80") → 2

🔹 Problem 3: Employee Attendance

Attendance record for 1 week:

Employee Mon Tue Wed Thu Fri

John P P A P P

Mary A P P A P

Alex P P P P P

Rina P A P P A

Tasks:

1. Count total working days recorded for John.

👉 =COUNTA(B2:F2) → 5

2. Count number of Absents for Mary.

👉 =COUNTIF(B3:F3, "A") → 2

3. Count number of Presents for Alex.

👉 =COUNTIF(B4:F4, "P") → 5

4. Find employee with maximum absents (using MAX on absents).

👉 =MAX(G2:G5) (if you calculate absents separately in column G).

🔹 Problem 4: Inventory Stock Levels

Warehouse items:

Item Stock

Shoes 500

Shirts 250

Pants 800

Caps 150

Jackets 300

Tasks:

1. Find total stock available.

👉 =SUM(B2:B6) → 2000

2. Find average stock per item.

👉 =AVERAGE(B2:B6) → 400

3. Find item with minimum stock.

👉 =MIN(B2:B6) → 150

4. Find item with maximum stock.

👉 =MAX(B2:B6) → 800

5. Count how many items have stock less than 400.

👉 =COUNTIF(B2:B6, "<400") → 3

🔹 Problem 1: Monthly Sales

Sales data for a shop:

Product January February March April

Shoes 1200 1500 1000 1800

Shirts 800 950 700 850

Pants 1500 1800 1700 2000

Caps 300 400 250 450

Jackets 900 1000 850 950

Questions:

1. Find the total sales of Shoes across all months.

2. Find the average sales of Pants.

3. Find the highest monthly sales of Jackets.

4. Find the lowest monthly sales of Shirts.

 5. Count how many months recorded more than 1500 sales for Pants.

🔹 Problem 2: Student Marks

Marks of students in different subjects:

Student Math Science English History

A 72 85 65 90

B 88 70 80 75

C 95 92 89 94

D 60 65 58 68

Questions:

1. Find the total marks of Student B.

2. Find the average marks of Student C.

3. Find the highest marks scored in English.

4. Find the lowest marks scored in History.

5. Count how many students scored more than 80 in Math.

🔹 Problem 3: Employee Attendance

Attendance record for one week:

Employee Mon Tue Wed Thu Fri

John P A P P P

Mary P P A A P

Alex P P P P P

Rina A P A P P

Questions:

1. Count the total working days recorded for John.

2. Count the number of Absents for Mary.

3. Count the number of Presents for Alex.

4. Find which employee has the highest number of absents.

5. Find which employee has the highest number of presents.

🔹 Problem 4: Inventory Stock

Warehouse stock levels:

Item Stock

Shoes 550

Shirts 200

Pants 900

Caps 120

Jackets 320

Questions:

1. Find the total stock available.

2. Find the average stock per item.

3. Find the item with minimum stock.

4. Find the item with maximum stock.

5. Count how many items have stock less than 400.

Logical functions are used to test conditions and return results automatically (like Pass/Fail, Yes/No,
etc.).

🔹 1. IF Function

👉 Tests a condition and returns one value if TRUE and another if FALSE.

Syntax:
=IF(condition, value_if_true, value_if_false)

Example:
Marks = 60, Pass mark = 50
=IF(A1>=50,"Pass","Fail") → Pass
🔹 2. AND Function

👉 Returns TRUE if all conditions are TRUE, else FALSE.

Syntax:
=AND(condition1, condition2, …)

Example:
Check if a student scored more than 50 in both Math and English:
=AND(B2>=50, C2>=50) → TRUE/FALSE

Often used inside IF:

=IF(AND(B2>=50, C2>=50),"Pass","Fail")

🔹 3. OR Function

👉 Returns TRUE if any one condition is TRUE.

Syntax:
=OR(condition1, condition2, …)

Example:
Check if a student passed in at least one subject:
=OR(B2>=50, C2>=50) → TRUE/FALSE

Inside IF:
=IF(OR(B2>=50, C2>=50),"Pass","Fail")

🔹 4. IFERROR Function

👉 Catches and replaces errors with a custom value.

Syntax:
=IFERROR(value, value_if_error)

Example:
=IFERROR(A1/B1,"Error: Division by Zero")

 If B1 = 0 → Output = "Error: Division by Zero"

 If B1 = 5 → Output = A1/5

🔹 5. Nested IF

👉 Multiple IF statements combined for complex decisions.

Syntax:
=IF(condition1, result1, IF(condition2, result2, result3))

=IF(condition1, result1, IF(condition2, result2, IF(CONDITION 3, result3, RESULT 4))

Example:
Grading system:

 =80 → "A"

 =60 → "B"

 <60 → "C"

 <21 - FAIL

Formula:
=IF(A1>=80,"A",IF(A1>=60,"B","C"))

=IF(A1>=80,"A",IF(A1>=60,"B",IF(A1<60,”C”,”FAIL”)

🏢 Case Studies

📌 Case Study 1: Pass/Fail Logic

Student marks:

Student Marks

A 85

B 45

C 60

 Formula: =IF(B2>=50,"Pass","Fail")

👉 Result: A = Pass, B = Fail, C = Pass

📌 Case Study 2: Employee Bonus Eligibility

Eligibility rules:

 Salary > 50,000 AND Experience > 5 years → Bonus = Yes

 Otherwise → Bonus = No

Formula:
=IF(AND(B2>50000,C2>5),"Yes","No")

📌 Case Study 3: Discount Offer

Condition:

 If purchase amount ≥ 5000 → "Eligible for Discount"

 Otherwise → "Not Eligible"

Formula:
=IF(A2>=5000,"Eligible","Not Eligible")

📌 Case Study 4: Handling Errors

Division of sales by number of employees:

=IFERROR(A2/B2,"Check Data")

👉 Avoids #DIV/0! errors when B2 = 0.

📌 Case Study 5: Grading System (Nested IF)

Marks:

Student Marks

X 92

Y 75

Z 55

Formula:
=IF(B2>=80,"A",IF(B2>=60,"B","C"))

👉 X = A, Y = B, Z = C

1. IF Function

👉 Program to check if a student passed or failed (Pass mark = 50).

=IF(B2>=50,"Pass","Fail")

 If Marks in B2 = 72 → Pass

 If Marks in B2 = 45 → Fail

🔹 2. AND Function

👉 Program to check if an employee is eligible for promotion:

 Salary > 50000

 Experience > 5 years

=IF(AND(C2>50000,D2>5),"Eligible","Not Eligible")

 If C2 = 60000, D2 = 7 → Eligible

 If C2 = 45000, D2 = 8 → Not Eligible

🔹 3. OR Function

👉 Program to check if a customer gets free delivery:

 Order value > 5000 OR Membership = "Gold"

=IF(OR(B2>5000,C2="Gold"),"Free Delivery","Delivery Charge")

 Order 6000, Silver → Free Delivery

 Order 3000, Gold → Free Delivery

 Order 3000, Silver → Delivery Charge

🔹 4. IFERROR Function

👉 Program to safely divide Sales by Employees (avoid #DIV/0! error):

=IFERROR(A2/B2,"Check Data")

 If A2=10000, B2=5 → 2000

 If A2=10000, B2=0 → Check Data

🔹 5. Nested IF

👉 Program for grading system:

 Marks ≥ 80 → A

 Marks ≥ 60 → B

 Marks < 60 → C

=IF(B2>=80,"A",IF(B2>=60,"B","C"))

 Marks 85 → A

 Marks 65 → B

 Marks 45 → C

🔹 6. Combined Example (Complex Program)

👉 Bonus Calculation:

 If Sales ≥ 10000 AND Target Achieved = "Yes" → Bonus = 20% of Sales

 Else If Sales ≥ 5000 → Bonus = 10% of Sales

 Otherwise → No Bonus

=IF(AND(B2>=10000,C2="Yes"),B2*20%,

IF(B2>=5000,B2*10%,"No Bonus"))
  Sales 12000, Yes → 2400

 Sales 7000, No → 700

 Sales 3000, Yes → No Bonus

🔹 Problem 1: Pass/Fail

Marks of students are in column B. Pass mark = 40.

👉 Write a formula to check whether each student is Pass or Fail.

🔹 Problem 2: Scholarship Eligibility

Eligibility rules:

 Marks ≥ 90 AND Attendance ≥ 80% → Eligible

 Otherwise → Not Eligible

👉 Write a formula to check scholarship status.

🔹 Problem 3: Discount Offer

Condition:

 If order amount ≥ 5000 OR customer is a “Premium Member” → Discount = Yes

 Otherwise → No Discount
👉 Write a formula.

🔹 Problem 4: Division Error Handling

Sales are in A2, Employees in B2.

👉 Divide sales by employees but return "Check Data" if Employees = 0.

🔹 Problem 5: Grading System

Marks are in column B.

 ≥ 80 → Grade A

 ≥ 60 → Grade B

 < 60 → Grade C
👉 Write a formula to assign grades.

🔹 Problem 6: Employee Bonus

Rules:

 If Salary ≥ 50000 AND Experience ≥ 5 years → Bonus = 10000

 Otherwise → Bonus = 5000

👉 Write a formula.

🔹 Problem 7: Free Shipping

Rules:

 If purchase amount ≥ 3000 OR Payment Mode = "Credit Card" → Free Shipping

 Otherwise → Shipping Charge 100

👉 Write a formula.

🔹 Problem 8: Sales Commission

Sales values are in column B.

 If Sales ≥ 20000 → Commission = 20% of Sales

 If Sales ≥ 10000 → Commission = 10% of Sales

 Otherwise → No Commission
👉 Write a formula.

🔹 Problem 9: Attendance Report

Attendance (P/A) of each day is recorded.

👉 Write a formula to count total Absents (A) for each employee.

🔹 Problem 10: Profit Check

If Profit is positive → "Profit"

If Profit is zero → "Break Even"
If Profit is negative → "Loss"
👉 Write a formula.