Module 2

Core Business Formulas &

Logical Functions

Complete Step-by-Step Excel Guide with Screenshots

Sheet 1 | SUM · AVERAGE · COUNT · MIN · MAX

Sheet 2 | IF · AND · OR · Grading System

Sheet 3 | LEFT RIGHT MID TRIM UPPER LOWER PROPER

Sheet 4 | TODAY · NOW · DATEDIF

Bonus | Conditional Formatting · Payment & Experience Tracker

Excel Workbook Included · 5 Sheets · 217 Live Formulas · Zero Errors

 Table of Contents

# Section Page

01 Module Overview & Workbook Structure 3

02 Sheet 1: SUM, AVERAGE, COUNT, MIN, MAX 4

03 Sheet 2: IF, AND, OR — Pass/Fail & Grading 6

04 Sheet 3: Text Functions 8

05 Sheet 4A: Date Functions — Experience Calculator 10

06 Sheet 4B: Overdue Payment Tracker 12

07 Conditional Formatting Rules 14

08 Quick Function Reference 15

Module 2 – Core Business Formulas & Logical Functions Page 2

 Module Overview & Workbook Structure

This workbook covers every formula and function in Module 2. It has 4 functional sheets plus an index, with
realistic data, live formulas, and conditional formatting.

Sh
Name Contents Functions
eet

0 Module Index Overview, tips, navigation —

10 students, 5 subjects — totals &

1 Basic Formulas SUM AVERAGE COUNT MIN MAX
averages

2 IF/AND/OR Grading Pass/Fail, letter grades, scholarship IF AND OR Nested-IF

TRIM UPPER LOWER PROPER LEFT

3 Text Functions Name cleanup, email formatting
RIGHT MID

TODAY NOW DATEDIF + Cond.

4 Date Functions Experience + overdue payment tracker
Formatting

Screenshot: Module Index sheet

Module 2 – Core Business Formulas & Logical Functions Page 3

 Sheet 1 – Basic Formulas: SUM, AVERAGE, COUNT, MIN, MAX

Build a marks register for 10 students across 5 subjects that automatically calculates totals, averages, and class
statistics.

Step-by-Step Instructions

# Action Formula / Function What it Does

Row 2: Student ID, Name, Math,

1 Set up headers — Science, English, History, Computer,
Total, Average

Rows 3–12: type marks for 10

2 Enter student data —
students in columns C to G

Adds all 5 subject marks. Enter in H3,

3 Total marks =SUM(C3:G3)
drag down to H12

Mean of 5 subjects. Enter in I3, drag

4 Average marks =AVERAGE(C3:G3)
down. Format to 2 decimals

Counts how many students have a

5 Count students =COUNT(H3:H12)
total entered

Returns the maximum total marks in

6 Highest total =MAX(H3:H12)
the class

Returns the minimum total marks in

7 Lowest total =MIN(H3:H12)
the class

Average of all student averages — the

8 Class average =AVERAGE(I3:I12)
class mean

How many students scored over 400

9 Count above 400 =COUNTIF(H3:H12,">400")
out of 500

Function Reference

Function Syntax Example Returns

SUM =SUM(range) =SUM(C3:G3) Sum of all values

AVERAGE =AVERAGE(range) =AVERAGE(C3:G3) Mean of all values

COUNT =COUNT(range) =COUNT(H3:H12) Count of numeric cells

MAX =MAX(range) =MAX(H3:H12) Largest value

MIN =MIN(range) =MIN(H3:H12) Smallest value

=COUNTIF(range,criteri
COUNTIF =COUNTIF(H3:H12,">400") Count matching condition
a)

Tip: Select the formula cell and drag the small square at the bottom-right corner downward to copy the formula to all rows
automatically.

Module 2 – Core Business Formulas & Logical Functions Page 4

 Screenshot: Sheet 1 – SUM, AVERAGE, COUNT, MIN, MAX with summary block

Module 2 – Core Business Formulas & Logical Functions Page 5

 Sheet 2 – IF, AND, OR: Pass/Fail & Grading System

Logical functions let Excel make decisions. This sheet uses IF, AND, and OR to determine Pass/Fail status,
assign letter grades, and check scholarship eligibility.

Step-by-Step Instructions

# Action Formula / Function What it Does

Add 3 subject marks per student in

1 Enter marks —
columns C, D, E

2 Calculate total =SUM(C3:E3) Total of 3 subjects in column F

Mean marks in column G, formatted to

3 Average =AVERAGE(C3:E3)
1 decimal

=IF(AND(C3>=40,D3>=40,E3>=40),"PA PASSES only if ALL 3 subjects are 40

4 Pass / Fail
SS","FAIL") or above (AND logic)

=IF(G3>=90,"A+",IF(G3>=80,"A",IF(
Nested IF checks average and
5 Letter grade G3>=70,"B",IF(G3>=60,"C",IF(G3>=5
assigns grade A+ through F
0,"D","F")))))

=IF(OR(G3>=90,H3="PASS"),"Eligibl Eligible if average >=90 OR student

6 Scholarship
e","Not Eligible") passed (OR logic)

Select H3:H12 > New Rule > Formula:

7 Colour PASS green Conditional Formatting
=H3="PASS" > green fill

New Rule > Formula: =H3="FAIL" >

8 Colour FAIL red Conditional Formatting
red fill, dark red font

How IF, AND, OR Work

Function Logic Use When

IF IF(condition, true_value, false_value) — one decision Single yes/no choice

AND AND(c1,c2,...) — TRUE only if ALL conditions are met All conditions must pass

OR OR(c1,c2,...) — TRUE if ANY one condition is met At least one must pass

Nested IF IF inside another IF — chain of decisions Multiple tiers / categories

Grade Scale

Grade Range Meaning

A+ 90 – 100 Outstanding

A 80 – 89 Excellent

B 70 – 79 Good

C 60 – 69 Average

Module 2 – Core Business Formulas & Logical Functions Page 6

 D 50 – 59 Below Average

F Below 50 Fail

Tip: AND is stricter than OR. If a student fails even ONE subject, AND marks them FAIL — even if their overall average is
high. Always choose the right logic for your rule.

Screenshot: Sheet 2 – IF/AND/OR with Pass/Fail, grades and scholarship column

Module 2 – Core Business Formulas & Logical Functions Page 7

 Sheet 3 – Text Functions

Text functions clean messy data — remove extra spaces, fix capitalisation, and extract specific characters.
Essential for any real-world data cleanup task.

Step-by-Step Instructions

# Action Formula / Function What it Does

Column B: names with inconsistent

1 Enter raw data —
spaces/case. Column E: raw emails

Removes all leading, trailing, and

2 TRIM the name =TRIM(B3)
extra internal spaces

Converts trimmed name to ALL

3 UPPER case =UPPER(TRIM(B3))
CAPITALS

Converts email to all lowercase —

4 LOWER case email =LOWER(E3)
standard format

Capitalises First Letter of Each Word

5 PROPER case =PROPER(TRIM(B3))
— for display names

Gets the first 1 character from Student

6 LEFT extract =LEFT(A3,1)
ID (e.g. 'S' from 'S001')

Gets 3 chars starting from position 2

7 MID extract =MID(A3,2,3)
(e.g. '001' from 'S001')

Gets the last 3 characters (e.g. '.in'

8 RIGHT extract =RIGHT(E3,3)
from 'x@[Link]')

Text Function Reference

Function Syntax Example Result

TRIM =TRIM(text) =TRIM(" hello ") "hello"

UPPER =UPPER(text) =UPPER("hello") "HELLO"

LOWER =LOWER(text) =LOWER("HELLO") "hello"

PROPER =PROPER(text) =PROPER("hello world") "Hello World"

LEFT =LEFT(text, n) =LEFT("S001",1) "S"

RIGHT =RIGHT(text, n) =RIGHT("[Link]",3) ".in"

MID =MID(text,start,n) =MID("S001",2,3) "001"

LEN =LEN(text) =LEN("Hello") 5

CONCAT =CONCAT(t1," ",t2) =CONCAT(C3," ",D3) Full Name

Tip: Always TRIM first before applying UPPER, LOWER, or PROPER. Otherwise, extra spaces get preserved inside the
result.

Module 2 – Core Business Formulas & Logical Functions Page 8

 Screenshot: Sheet 3 – TRIM, UPPER, LOWER, PROPER, LEFT, MID, RIGHT applied to raw name and email data

Module 2 – Core Business Formulas & Logical Functions Page 9

 Sheet 4A – Date Functions: Employee Experience Calculator

Date functions calculate the difference between dates automatically. This tracker updates every single day
without any manual input.

Step-by-Step Instructions

# Action Formula / Function What it Does

Type date of joining in column D in

1 Enter joining date —
DD-MMM-YYYY format

Column E — returns today's date.

2 Today's date =TODAY()
Refreshes automatically every day

Complete years between joining date

3 Years experience =DATEDIF(D4,TODAY(),"Y")
and today

Months beyond the full years (e.g. 2

4 Remaining months =DATEDIF(D4,TODAY(),"YM")
yrs 4 months)

Days beyond the full months — most

5 Remaining days =DATEDIF(D4,TODAY(),"MD")
precise breakdown

=F4&" yrs "&G4;&" mths "&H4;&" Combines all three into a readable
6 Full experience text
days" text string

=IF(F4>=10,"Senior",IF(F4>=5,"Mid
Nested IF classifies employee by
7 Seniority level -Level",IF(F4>=2,"Junior","Freshe
years of experience
r")))

=IF(F4>=5,"Eligible","Not
8 Bonus eligibility 5+ years qualifies for bonus
Eligible")

Senior = Green fill; Fresher = Red fill

9 Apply colour coding Conditional Formatting
using Formula rules

DATEDIF Parameters Explained

Paramet
Returns Example Formula Result
er

"Y" Complete years =DATEDIF(D4,TODAY(),"Y") 7

"M" Total months (all) =DATEDIF(D4,TODAY(),"M") 87

"D" Total days (all) =DATEDIF(D4,TODAY(),"D") 2647

"YM" Months after subtracting full years =DATEDIF(D4,TODAY(),"YM") 3

"MD" Days after subtracting full months =DATEDIF(D4,TODAY(),"MD") 9

Tip: DATEDIF is a hidden function — it does NOT appear in Excel autocomplete. You must type it manually. Syntax:
=DATEDIF(start_date, end_date, unit)

Module 2 – Core Business Formulas & Logical Functions Page 10

 Screenshot: Sheet 4A – Employee experience with DATEDIF, seniority level and bonus eligibility

Module 2 – Core Business Formulas & Logical Functions Page 11

 Sheet 4B – Overdue Payment Tracker

A live payment tracking system that calculates overdue days, applies a 5% monthly penalty, and uses NOW() to
timestamp the last refresh.

Step-by-Step Instructions

# Action Formula / Function What it Does

Invoice#, Customer, Amount, Invoice

1 Enter invoice data —
Date, Due Date in columns A–E

Column F — always shows today's

2 Today's date =TODAY()
date for comparison

Subtracts due date from today.

3 Days overdue =MAX(0,TODAY()-E4)
MAX(0,...) prevents negative numbers

3-way check: past = OVERDUE,

=IF(TODAY()>E4,"OVERDUE",IF(TODAY
4 Payment status same day = DUE TODAY, future =
()=E4,"DUE TODAY","NOT DUE"))
NOT DUE

5% per month on overdue amount.

5 Penalty =IF(G4>0,C4*(G4/30)*0.05,0)
(days/30) converts days to months

Records exact date AND time the file

6 NOW() timestamp =NOW()
was last opened or refreshed

=IF(G4>30,"URGENT",IF(G4>0,"FOLLO Escalates alert based on how many

7 Alert column
W UP","OK")) days overdue

Formula: =ISNUMBER(SEARCH("OV
8 OVERDUE = red Conditional Formatting
ERDUE",H4)) → Red fill, dark text

Formula:
9 NOT DUE = green Conditional Formatting =ISNUMBER(SEARCH("NOT
DUE",H4)) → Light fill, dark text

Colour scale on Select G column → Color Scales →

10 Conditional Formatting
days Green (0) to Red (90+)

TODAY() vs NOW()

Function Returns Example Output Best Used For

=TODAY() Current date only 24-Mar-2026 Age, overdue days, deadlines

=NOW() Current date and time 24-Mar-2026 14:35 Audit timestamps, last-updated

Tip: Penalty formula =C4*(G4/30)*0.05 works by converting overdue days to months (G4/30), then multiplying by the 5%
rate. Example: 60 days overdue on Rs.10,000 = 10000 x 2 x 0.05 = Rs.1,000 penalty.

Module 2 – Core Business Formulas & Logical Functions Page 12

 Screenshot: Sheet 4B – Overdue payment tracker with status, penalty and NOW() timestamp

Module 2 – Core Business Formulas & Logical Functions Page 13

 Conditional Formatting – Complete Rules Guide

Conditional Formatting changes the appearance of cells automatically based on their values. No manual
colouring needed — rules update in real time.

All Rules Applied in the Workbook

Rule Type Range Condition Format Purpose

Sheet 2 – PASS
Formula Rule H3:H12 =H3="PASS" Green fill, bold text
students

Sheet 2 – FAIL
Formula Rule H3:H12 =H3="FAIL" Red fill, dark text
students

Formula Rule J4:J10 =J4="Senior" Green fill, bold Sheet 4A – Senior staff

Formula Rule J4:J10 =J4="Fresher" Red fill, bold Sheet 4A – Freshers

ISNUMBER(SEARCH("OVERDUE Sheet 4B – Overdue

Formula Rule H19:H24 Red fill
",H19)) invoices

ISNUMBER(SEARCH("NOT Sheet 4B – On-time

Formula Rule H19:H24 Light fill
DUE",H19)) invoices

Green to Red Sheet 4B – Severity of

Color Scale G19:G24 0 days to 90+ days
gradient delay

How to Apply Conditional Formatting (Step by Step)

1 Select the cell range you want to format (e.g., H3:H12)

2 Go to the Home tab → click Conditional Formatting in the Styles group

3 Choose New Rule from the dropdown

4 Select 'Use a formula to determine which cells to format'

5 Type the formula (e.g., =H3="PASS")

6 Click Format → choose Fill colour and Font style → click OK

7 Click OK again to apply. The rule will now update automatically

8 For Color Scale: Conditional Formatting → Color Scales → pick 3-color option

Module 2 – Core Business Formulas & Logical Functions Page 14

 Quick Function Reference — All 29 Functions

A one-page reference covering all functions taught in Module 2.

Function Syntax Example What it Does

STATISTICAL FUNCTIONS

SUM =SUM(range) =SUM(C3:G3) Adds all values in a range

AVERAGE =AVERAGE(range) =AVERAGE(C3:G3) Mean of all values

COUNT =COUNT(range) =COUNT(H3:H12) Count of numeric cells

COUNTA =COUNTA(range) =COUNTA(B3:B12) Count of non-empty cells

MAX =MAX(range) =MAX(H3:H12) Largest value

MIN =MIN(range) =MIN(H3:H12) Smallest value

=COUNTIF(range,criteri Count cells matching a

COUNTIF =COUNTIF(H3:H12,">400")
a) condition

LOGICAL FUNCTIONS

Decision based on a
IF =IF(cond,true,false) =IF(A1>50,"Pass","Fail")
condition

TRUE if ALL conditions are

AND =AND(cond1,cond2,...) =AND(A1>40,B1>40)
met

OR =OR(cond1,cond2,...) =OR(A1>=90,B1="PASS") TRUE if ANY condition is met

Multiple conditions (Excel

IFS =IFS(c1,v1,c2,v2,...) =IFS(A1>=90,"A+","...")
2019+)

TEXT FUNCTIONS

TRIM =TRIM(text) =TRIM(" hello ") Remove extra spaces

UPPER =UPPER(text) =UPPER("hello") Convert to UPPERCASE

LOWER =LOWER(text) =LOWER("HELLO") Convert to lowercase

PROPER =PROPER(text) =PROPER("hello world") Title Case

LEFT =LEFT(text,n) =LEFT("S001",1) First n characters

RIGHT =RIGHT(text,n) =RIGHT("[Link]",3) Last n characters

MID =MID(text,start,n) =MID("S001",2,3) n characters from position

LEN =LEN(text) =LEN("Hello") Number of characters

CONCAT =CONCAT(t1," ",t2) =CONCAT(C3," ",D3) Join text values together

DATE FUNCTIONS

Today's date (auto-updates

TODAY =TODAY() =TODAY()
daily)

NOW =NOW() =NOW() Today's date + current time

DATEDIF =DATEDIF(s,e,"Y") =DATEDIF(D4,TODAY(),"Y") Years between two dates

Extract year number from

YEAR =YEAR(date) =YEAR(TODAY())
date

Module 2 – Core Business Formulas & Logical Functions Page 15

 MONTH =MONTH(date) =MONTH(TODAY()) Extract month number

DAY =DAY(date) =DAY(TODAY()) Extract day number

DATE =DATE(y,m,d) =DATE(2026,3,24) Build a date from parts

NETWORKDAY Working days excluding

=NETWORKDAYS(s,e) =NETWORKDAYS(D4,TODAY())
S weekends

Tip: To practise: open the Excel file, change any student's marks and watch all calculated columns update instantly —
totals, grades, pass/fail, and penalties recalculate automatically.

Module 2 – Core Business Formulas & Logical Functions Page 16