Generative AI Terminology

Introduction

Generative AI is a dynamic and rapidly evolving field within artificial intelligence. It

focuses on developing algorithms that can generate novel content, such as text,
images, audio, or video, from existing data. Understanding the terminology in this
domain clarifies how these technologies function and sheds light on their implications
for various industries. In this reading, you will explore an extensive glossary of terms
pertinent to generative AI, examining foundational concepts, advanced techniques,
and their practical applications.

Artificial intelligence

Artificial intelligence (AI) is the field of computing focused on creating systems

capable of performing tasks that would typically require human intelligence. These
tasks include reasoning, learning, problem-solving, perception, language
understanding, and even the ability to move and manipulate objects. AI technologies
leverage algorithms and dynamic computing environments to enable machines to
solve complex problems, adapt to new situations, and learn from past experiences.
Central to AI is machine learning (ML), where algorithms detect patterns and infer
probabilities from data, allowing the machine to improve its performance over time.
AI systems can range from simple, rule-based algorithms to complex neural
networks modeled on the human brain.

Machine learning

Machine learning (ML) is a critical domain within artificial intelligence that

emphasizes the development of algorithms and statistical models that enable
computers to perform specific tasks without explicit instructions. Instead, these
systems learn and make predictions or decisions based on data. Here's a more
technical breakdown:

1. Types of learning:

 Supervised learning: Algorithms learn from labeled training data, aiming to

predict outcomes for new inputs.
 Unsupervised learning: Algorithms identify patterns in data without needing
labeled responses, often used for clustering and association.

 Reinforcement learning: Models learn to make sequences of decisions by

receiving feedback on the actions' effectiveness.

2. Algorithms and techniques:

 Common algorithms include linear regression, decision trees, and neural networks.

 Advanced techniques involve deep learning, which uses layered neural networks to
analyze various levels of data features.

3. Data handling and processing:

 Effective machine learning requires robust data preprocessing, including

normalization, handling missing values, and feature selection to improve model
accuracy.

4. Performance evaluation:

 ML models are evaluated based on metrics such as accuracy, precision, recall, and
the area under the receiver operating characteristic (ROC) curve, ensuring that they
perform well on unseen data.

5. Application areas:

 ML is applied in various fields such as finance for algorithmic trading, healthcare for
predictive diagnostics, and autonomous vehicles for navigation systems.

Deep learning

Deep learning (DL) is an advanced branch of ML that uses artificial neural networks
with multiple layers, known as deep neural networks. These networks are capable of
learning from large amounts of unstructured data. DL models automatically extract
and learn features at multiple levels of abstraction, enabling the system to learn
complex patterns in large datasets. The learning process can be:

 Supervised - where the model is trained with labeled data

 Semi-supervised - which uses a mix of labeled and unlabeled data

 Unsupervised - which relies solely on unlabeled data

This technique is particularly effective in areas such as image recognition, natural

language processing (NLP), and speech recognition, where conventional machine-
learning techniques may fall short due to the data structures' complexity. DL has
propelled advancements in generative AI, enabling the creation of sophisticated
models like generative adversarial networks (GANs) that can generate new data
instances that mimic real data.

Neural networks

Neural networks (NN) are a cornerstone of AI. They are particularly effective in
pattern recognition and data interpretation tasks, which they achieve through a
structure inspired by the human brain. Comprising layers of interconnected nodes, or
neurons, each with its weights and biases, NN processes input data through these
nodes. The connections between nodes represent synapses and are weighted
according to their importance. As data passes through each layer, the network
adjusts the weights, which is how learning occurs. This structure enables neural
networks to learn from vast amounts of data to make decisions, classify data, or
predict outcomes with high accuracy. NN are particularly crucial in fields such as
computer vision, speech recognition, and NLP where they can recognize complex
patterns and nuances better than traditional algorithms. The training process
involves techniques such as backpropagation, where the model learns to minimize
errors by adjusting weights to produce the most accurate outputs possible.

Generative adversarial networks (GAN)

GANs are a sophisticated class of AI algorithms used in ML, characterized by their

unique structure of two competing NNs: the generator and the discriminator. The
generator is tasked with creating data that is indistinguishable from genuine data,
while the discriminator evaluates whether the generated data is real or fake. This
adversarial process, much like a teacher-student dynamic, continuously improves
the accuracy of the generated outputs. The training involves the discriminator
learning to better distinguish between real and generated data, while the generator
strives to produce increasingly convincing data, enhancing its ability to deceive the
discriminator. This setup not only helps in generating new data samples but is also
useful in unsupervised learning, semi-supervised learning, and reinforcement
learning. GANs are particularly renowned for their applications in image generation,
video creation, and voice synthesis, where they can produce highly realistic outputs.

Natural language processing (NLP)

NLP is an advanced area of AI that focuses on the interaction between computers

and humans through natural language. The goal of NLP is to read, decipher,
understand, and make sense of human languages in a manner that is valuable. It
involves several disciplines, including computer science and computational
linguistics, in an effort to bridge the gap between human communication and
computer understanding. Key techniques in NLP include syntax tree parsing, entity
recognition, and sentiment analysis, among others. These techniques help
computers to process and analyze large amounts of natural language data. NLP is
used in a variety of applications, such as automated chatbots, translation services,
email filtering, and voice-activated global position systems (GPS). Each application
requires the computer to understand the input provided by humans, process that
data in a meaningful way, and if necessary, respond in a language that humans
understand.

Transformers

Transformers represent a significant advancement in deep learning, particularly in

the field of NLP. Introduced by Google researchers in the seminal 2017 paper
"Attention is All You Need", transformers use a mechanism known as self-attention
to weigh the importance of each word in a sentence, regardless of its position. Unlike
previous models that processed data sequentially, transformers process all words or
tokens in parallel, which significantly increases efficiency and performance on tasks
that require understanding context over long distances within text. This architecture
avoids recurrence and convolutions entirely, relying instead on stacked self-attention
and point-wise, fully connected layers for both the encoder and the decoder
components. This design allows for more scalable learning and has been
fundamental in developing models that achieve state-of-the-art results on a variety of
NLP tasks, including machine translation, text summarization, and sentiment
analysis. The transformer's ability to handle sequential data extends beyond text,
making it versatile in other domains like image processing and even music
generation.
Generative pre-trained transformers

Generative pre-trained transformers (GPT) are state-of-the-art language models

developed by OpenAI that use DL techniques, specifically the transformer
architecture, for natural language understanding and generation. These models are
first pre-trained on a diverse range of internet text to develop a broad understanding
of language structure and context. The pre-training involves unsupervised learning,
where the model predicts the next word in a sentence without human-labeled
corrections. This allows GPT models to generate coherent and contextually
appropriate text sequences based on the prompts they are given. Once pre-trained,
GPT models can be fine-tuned on specific tasks such as translation, question-
answering, and summarization, enhancing their applicability across various domains.
Their ability to generate human-like text and perform language-based tasks has
implications across fields such as AI-assisted writing, conversational agents, and
automated content creation. Each successive version of GPT has been larger and
more complex, with GPT-4, the latest iteration, containing 175 billion parameters,
which significantly advances its learning and generative capabilities.

Tokenization, Word2vec, and BERT

Tokenization in NLP involves splitting text into smaller units known as tokens, which
can be words, characters, or subwords. This step is crucial for preparing text for
processing with various NLP models, as it standardizes the initial input into
manageable pieces for algorithms to process. Word2vec, developed by researchers
at Google, is a technique that embeds words into numerical vectors using shallow,
two-layer NNs. The models are trained to reconstruct the linguistic contexts of words,
thereby capturing the relationships and multiple degrees of similarity among them.
Meanwhile, Bidirectional Encoder Representations from Transformers (BERT)
represents a significant advancement in pre-training language representations.
Developed also by Google, BERT incorporates a transformer architecture that
processes words in relation to all the other words in a sentence, rather than one-by-
one in order. This allows BERT to capture the full context of a word based on all its
surroundings, leading to a deeper understanding of language nuances. BERT's
ability to handle context from both directions makes it exceptionally powerful for
tasks where context is crucial, such as question answering and sentiment analysis.

Conclusion
In this reading, you examined the foundational concepts of generative AI. You
learned about ML, DL, and NLP, and unraveled their roles and applications in
various industries. Additionally, you delved into emerging advancements like GANs,
transformers, and GPT, recognizing their pivotal role in generating innovative
content.

Understanding these foundational terms in generative AI not only enriches the

conversation among tech enthusiasts but also empowers professionals to leverage
this technology in various industries effectively. As AI continues to advance, keeping
abreast of terminologies and concepts will provide the necessary tools to navigate
this dynamic field successfully.

Introduction to Microsoft Excel

Microsoft Excel is a powerful spreadsheet application developed by Microsoft. It is widely

used for data organization, analysis, and visualization. Excel allows users to work with
numbers, text, and formulas in a structured grid format.

Key Features of Excel:

1. Workbook and Worksheets

o An Excel file is called a workbook.
o Each workbook contains one or more worksheets (or sheets), which consist
of rows and columns.
2. Cells, Rows, and Columns
o The intersection of a row and a column is called a cell, identified by a cell
reference (e.g., A1, B2).
o Rows are numbered (1, 2, 3…), and columns are labeled alphabetically (A, B,
C…).
3. Formulas and Functions
o Formulas perform calculations (e.g., =A1+B1).
o Functions are pre-built formulas like SUM(), AVERAGE(), IF(), VLOOKUP().
4. Data Analysis Tools
o Sorting and Filtering – Organize data or find specific information.
o PivotTables – Summarize and analyze large datasets quickly.
o Charts and Graphs – Visualize data with bar charts, line charts, pie charts,
etc.
5. Formatting Options
o Change font styles, colors, cell borders, and number formats to make data
easier to read and present.
6. Automation
o Excel supports macros and VBA (Visual Basic for Applications) to
automate repetitive tasks.
Formatting Data in Excel
Formatting improves readability and makes your data look professional.

A. Font Formatting

 Change font type, size, and color from the Home tab.
 Apply bold, italics, or underline for emphasis.

B. Cell Formatting

 Borders: Add borders to separate data clearly.

 Fill Color: Highlight important cells with background colors.
 Alignment: Adjust text left, center, right, and wrap text to fit long content.

C. Number Formatting

 General: Default format.

 Number: Shows decimals, can include commas.
 Currency: Displays money symbols ($, ₹).
 Percentage: Converts numbers to % format.
 Date/Time: Displays date or time in chosen formats.

D. Conditional Formatting

 Highlights cells based on criteria (e.g., all values > 100 turn green).
 Found under Home → Conditional Formatting.

E. Adjusting Rows and Columns

 Resize: Drag row/column borders or use Format → AutoFit.

 Hide/Unhide: Right-click row/column to hide or unhide.

Sorting in Excel
Sorting allows you to rearrange data in a specific order for better organization and analysis.

Types of Sorting

1. Ascending (A → Z or smallest → largest)

o Example: Names from A to Z, Numbers from 1 to 100.
2. Descending (Z → A or largest → smallest)
o Example: Names from Z to A, Numbers from 100 to 1.
3. Custom Sort
o You can sort by multiple columns or use a custom list (like days of the week).
How to Sort

1. Select the column you want to sort.

2. Go to the Data tab → Sort & Filter group.
3. Choose:
o Sort A to Z (ascending)
o Sort Z to A (descending)
o Custom Sort for multiple criteria

Tip: Make sure all related columns are selected so data stays consistent (use Sort dialog →
“My data has headers”).

2. Filtering in Excel
Filtering allows you to display only the data that meets certain criteria while hiding the
rest.

How to Apply a Filter

1. Select the header row of your dataset.

2. Go to Data tab → Filter.
3. Small dropdown arrows appear next to each header.

Using Filters

 Text Filters: Filter by contains, equals, begins with, etc.

 Number Filters: Filter by greater than, less than, between, top 10, etc.
 Date Filters: Filter by before, after, specific months/years, or date ranges.
 Custom Filters: Combine multiple conditions.

Clearing Filters

 Click the filter icon → Clear Filter.

Formula
 In Excel, a formula is an equation used to perform calculations, process data, or
automate tasks using cell values.
 ✅ Basic structure of a formula:
Every formula in Excel starts with an equal sign (=).
 = value1 operator value2

  🧮 Examples of common formulas
Type Example Explanation

Addition =A1 + B1 Adds values in cells A1 and B1

Subtraction =A1 - B1 Subtracts B1 from A1

Multiplication =A1 * B1 Multiplies A1 by B1

Division =A1 / B1 Divides A1 by B1

Sum of range =SUM(A1:A5) Adds all values from A1 to A5

Average =AVERAGE(A1:A5) Finds average of A1 to A5

Maximum =MAX(A1:A5) Returns the largest value in A1:A5

Minimum =MIN(A1:A5) Returns the smallest value in A1:A5

Count numbers =COUNT(A1:A10) Counts how many cells contain numbers

IF condition =IF(A1>50, "Pass", "Fail") Returns “Pass” if A1>50, otherwise “Fail”

In Excel, parentheses ( ) are used to control the order of calculations and group parts of a
formula — just like in mathematics.

🧮 1. Purpose of Parentheses

Parentheses tell Excel which part of a formula to calculate first.

Example:

= (2 + 3) * 4

👉 Excel will first add 2 + 3 = 5,

then multiply 5 × 4 = 20.

Without parentheses:

= 2 + 3 * 4

👉 Excel follows the order of operations (multiplication first),

so it gives 2 + 12 = 14.

🔢 2. Order of Operations (BODMAS Rule)

Excel follows this sequence:

 1. B – Brackets (Parentheses)
2. O – Orders (Exponents, e.g. ^)
3. D – Division (/)
4. M – Multiplication (*)
5. A – Addition (+)
6. S – Subtraction (-)

Parentheses help you override this natural order.

📘 3. Examples
Formula Explanation Result

=10 + 5 * 2 Multiplies first, then adds 20

=(10 + 5) * 2 Adds first, then multiplies 30

=A1 + (B1 * C1) Multiplies B1 × C1 first depends on values

=(A1 + B1) / C1 Adds A1 + B1, then divides by C1 depends on values

💡 4. Parentheses in Functions

Parentheses are also used to enclose function arguments.

Examples:

 =SUM(A1:A5) → adds all values from A1 to A5

 =IF(A1>50, "Pass", "Fail") → condition and results are inside parentheses
 =AVERAGE(A1, B1, C1) → calculates the average of listed cells

Each function must have parentheses, even if there’s only one argument.

Functions
In Excel, a function is a predefined formula that performs a specific calculation
automatically — saving you time and reducing errors.

You just need to enter the function name (like SUM, AVERAGE, or IF) followed by
arguments inside parentheses.

🧮 1. Structure of a Function
=FUNCTION_NAME(argument1, argument2, …)

✅ Always starts with an equal sign (=)

✅ Arguments are the values, cell references, or ranges used in the function.
Example:

=SUM(A1:A5)

→ Adds all numbers from A1 to A5.

📚 2. Types of Functions in Excel

Type Example Description

Mathematical =SUM(A1:A5) Adds numbers

=AVERAGE(A1:A5) Finds average

Rounds a number to 2 decimal

=ROUND(A1,2)
places

Text =CONCATENATE(A1, " ", B1) Joins text from two cells

=LEN(A1) Counts number of characters

=UPPER(A1) Converts text to uppercase

Logical =IF(A1>50,"Pass","Fail") Tests a condition

=AND(A1>50,B1<100) Returns TRUE if both are true

Statistical =MAX(A1:A10) Finds highest value

=MIN(A1:A10) Finds lowest value

Date and Time =TODAY() Returns today’s date

=NOW() Returns current date and time

=DATEDIF(A1,B1,"Y") Calculates years between two dates

=VLOOKUP(A1, B1:D10, 3,
Lookup and Reference FALSE) Looks up a value in a table

=HLOOKUP(A1, B1:D10, 2,
FALSE) Looks up horizontally

=INDEX(A1:C10,2,3) Finds value by row & column

=MATCH("John", A1:A10, 0) Finds position of a value

Financial =PMT(rate, nper, pv) Calculates loan payment

=FV(rate, nper, pmt) Future value of investment

How to Use a Function

1. Click a cell where you want the result.

2. Type = then the function name.
 3. Open parentheses (.
4. Enter cell references or numbers.
5. Close parentheses ) and press Enter.

Example:

=AVERAGE(B1:B5)

🪄 4. Using the “Insert Function” Tool

If you forget a formula name:

1. Click in a cell.
2. Click fx (Insert Function) beside the formula bar.
3. Search for a function (e.g., “add numbers”).
4. Excel shows a list — pick one, and it will guide you step-by-step.

LEFT Function

Purpose: Extracts characters from the left side of a text.

Formula:

=LEFT(text, [num_chars])

 text → the cell or text string

 [num_chars] → number of characters you want to extract

Example:
If A1 = "FinanceMarket"
=LEFT(A1, 7) → "Finance"

🧩 2. RIGHT Function

Purpose: Extracts characters from the right side of a text.

Formula:

=RIGHT(text, [num_chars])

Example:
If A1 = "FinanceMarket"
=RIGHT(A1, 6) → "Market"

🧩 3. MID Function

Purpose: Extracts text from the middle of a string.

Formula:
=MID(text, start_num, num_chars)

 start_num → position to start from

 num_chars → how many characters to extract

Example:
If A1 = "FinanceMarket"
=MID(A1, 4, 3) → "anc"

🧩 4. TRIM Function

Purpose: Removes extra spaces from text (except single spaces between words).
Formula:

=TRIM(text)

Example:
If A1 = " Hello World "
=TRIM(A1) → "Hello World"

🧩 5. UPPER Function

Purpose: Converts text to UPPERCASE.

Formula:

=UPPER(text)

Example:
If A1 = "finance"
=UPPER(A1) → "FINANCE"

🧩 6. LOWER Function

Purpose: Converts text to lowercase.

Formula:

=LOWER(text)

Example:
If A1 = "FINANCE"
=LOWER(A1) → "finance"
🧩 7. PROPER Function

Purpose: Converts text to Proper Case (first letter of each word capitalized).
Formula:

=PROPER(text)

Example:
If A1 = "finance market"
=PROPER(A1) → "Finance Market"

🧩 8. CONCAT (or CONCATENATE) Function

Purpose: Joins (combines) text from multiple cells.

Old version:

=CONCATENATE(text1, text2, ...)

New version (recommended):

=CONCAT(text1, text2, ...)

Example:
If A1 = "Finance" and B1 = "Market"
=CONCAT(A1, " ", B1) → "Finance Market"

✅ Tip: You can also use the ampersand (&) symbol to join text:

=A1 & " " & B1

→ same result: "Finance Market"

🧠 Summary Table
Function Purpose Example Result

LEFT(A1, n) Get text from left LEFT("Market", 3) Mar

RIGHT(A1, n) Get text from right RIGHT("Market", 3) ket

MID(A1, start, n) Get text from middle MID("Finance", 2, 3) ina

TRIM(A1) Remove extra spaces " Hello " → TRIM() Hello

UPPER(A1) Uppercase "finance" FINANCE

LOWER(A1) Lowercase "FINANCE" finance

PROPER(A1) Capitalize each word "finance market" Finance Market
 Function Purpose Example Result

CONCAT(A1,B1) Join text "Finance", "Market" FinanceMarket

TEXTJOIN Function
Purpose:
👉 Combines (joins) text from multiple cells or items into one cell — with a separator (like a
space, comma, or dash) between them.

Formula:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

Arguments:

 delimiter: What you want between each piece of text (e.g., " ", ",", "-")
 ignore_empty: TRUE to skip blank cells, FALSE to include them
 text1, text2…: The cells or values to join

Example 1:
If A1 = "Finance", B1 = "Market", C1 = "Course"

=TEXTJOIN(" ", TRUE, A1, B1, C1)

✅ Result: Finance Market Course

Example 2 (with commas):

=TEXTJOIN(", ", TRUE, A1:C1)

✅ Result: Finance, Market, Course

Example 3 (ignoring blanks):

If A1 = "Excel", B1 = "", C1 = "Functions"

=TEXTJOIN(" ", TRUE, A1:C1)

✅ Result: Excel Functions

(Blank cell is skipped automatically)
🧩 2. TEXTSPLIT Function

Purpose:
👉 Splits a text string into multiple cells (columns or rows) based on a separator.
It’s like the opposite of TEXTJOIN.

Formula:

=TEXTSPLIT(text, col_delimiter, [row_delimiter])

Arguments:

 text: The text you want to split

 col_delimiter: Symbol used to split into columns (like a comma, space, or dash)
 row_delimiter: (optional) Symbol to split into rows

Example 1 (split into columns):

If A1 = "Finance,Market,Course"

=TEXTSPLIT(A1, ",")

✅ Result:

Cell Value

B1 Finance

C1 Market

D1 Course

Example 2 (split into rows):

If A1 = "Finance;Market;Course"

=TEXTSPLIT(A1, , ";")

✅ Result (in rows):

B1: Finance
B2: Market
B3: Course
🧠 3. Key Difference Between TEXTJOIN and TEXTSPLIT
Feature TEXTJOIN TEXTSPLIT

Function Combines multiple cells into one Breaks one cell into multiple
Finance + Market = Finance Finance Market → Finance /
Example Market Market

Output Single cell Multiple cells

Introduced in Excel 2019 / Office 365 Excel 365 / Excel 2021

DATE Function

➡️Used to make a complete date using year, month, and day.

Formula:

=DATE(year, month, day)

Example:

=DATE(2025, 11, 8)

✅ Result → 08-Nov-2025

🗓️2. MONTH Function

➡️Used to find the month number from a date.

Formula:

=MONTH(cell)

Example:
If A1 = 08-Nov-2025

=MONTH(A1)

✅ Result → 11

🗓️3. YEAR Function

➡️Used to find the year from a date.

Formula:

=YEAR(cell)
Example:
If A1 = 08-Nov-2025

=YEAR(A1)

✅ Result → 2025

💡 In short:
Function What it does Example Result

DATE Makes a full date =DATE(2025,11,8) 08-Nov-2025

MONTH Gives month number =MONTH(A1) 11

YEAR Gives year number =YEAR(A1) 2025

What the IF Function Does

The IF function checks whether something is true or false,
and then gives one result if it’s true, and another result if it’s false.

🧮 2. Formula of IF
=IF(logical_test, value_if_true, value_if_false)

Where:

 logical_test → the condition you want to check

 value_if_true → what to show if the condition is TRUE
 value_if_false → what to show if the condition is FALSE

✅ 3. Simple Example

If cell A1 has a student’s marks:

=IF(A1>=40, "Pass", "Fail")

Explanation:

 Checks if A1 is 40 or more
 If yes → shows "Pass"
 If no → shows "Fail"

Marks (A1) Formula Result

75 =IF(A1>=40,"Pass","Fail") Pass
Marks (A1) Formula Result

35 =IF(A1>=40,"Pass","Fail") Fail

📊 4. IF with Text

If B1 = “India”,

=IF(B1="India","Yes","No")

✅ If B1 is India → shows “Yes”

❌ If not → shows “No”

💰 5. IF with Numbers

If C1 = 1000 (sales amount):

=IF(C1>500,"Bonus","No Bonus")

✅ Shows “Bonus” if sales are above 500.

🔁 6. Nested IF (Multiple Conditions)

You can use more than one IF inside another:

=IF(A1>=75,"Distinction",IF(A1>=40,"Pass","Fail"))

✅ If marks ≥ 75 → Distinction
✅ If 40–74 → Pass
❌ If below 40 → Fail

🧠 7. Tip

You can also combine IF with other functions like:

 AND → check if multiple conditions are true

 =IF(AND(A1>50,B1>50),"Good","Bad")

 OR → check if any condition is true

 =IF(OR(A1>50,B1>50),"Pass","Fail")
💡 Summary Table
Function Purpose Example Result

=IF(A1>=40,"Pass","Fail") Test a single condition A1=35 Fail

=IF(A1="India","Yes","No") Compare text A1=India Yes

=IF(AND(A1>50,B1>50),"Good","Bad") Two conditions Both >50 Good

=IF(OR(A1>50,B1>50),"Pass","Fail") Either condition One >50 Pass

IF Function
Purpose:
Used to test one condition — gives one result if TRUE, another if FALSE.

Formula:

=IF(logical_test, value_if_true, value_if_false)

Example:
If A1 has marks of a student:

=IF(A1>=40, "Pass", "Fail")

✅ If A1 is 40 or more → shows “Pass”

❌ If less than 40 → shows “Fail”

Marks (A1) Result

75 Pass

35 Fail

🧮 2. IFS Function
Purpose:
Used when you want to test many conditions — without typing many nested IFs.

Formula:

=IFS(condition1, value1, condition2, value2, condition3, value3, …)

How it works:
Excel checks each condition in order —
the first condition that’s TRUE decides the result.
Example:
If A1 has marks:

=IFS(A1>=75, "Distinction",
A1>=60, "First Class",
A1>=40, "Pass",
A1<40, "Fail")
Marks (A1) Result

82 Distinction

65 First Class

45 Pass

30 Fail

💡 Difference Between IF and IFS

Feature IF IFS

Checks One condition Multiple conditions

Formul
Short and simple Easier for many conditions
a

Exampl =IF(A1>=40,"Pass","Fai =IFS(A1>=75,"Distinction",A1>=40,"Pass",A1<40,

e l") "Fail")

Works
All Excel versions Excel 2019 / Office 365 / Excel 2021
in

AND Function
Purpose:
Checks if all conditions are TRUE.
It returns:

 ✅ TRUE → if all conditions are true

 ❌ FALSE → if any one condition is false

Formula:

=AND(condition1, condition2, ...)

Example 1:
If A1 = 50 and B1 = 60

=AND(A1>40, B1>40)

✅ Result → TRUE (because both are greater than 40)

Example 2:
If A1 = 50 and B1 = 30

=AND(A1>40, B1>40)

❌ Result → FALSE (B1 is not greater than 40)

🧮 2. OR Function
Purpose:
Checks if any one condition is TRUE.
It returns:

 ✅ TRUE → if one or more conditions are true

 ❌ FALSE → if all conditions are false

Formula:

=OR(condition1, condition2, ...)

Example 1:
If A1 = 50 and B1 = 30

=OR(A1>40, B1>40)

✅ Result → TRUE (A1 is greater than 40)

Example 2:
If A1 = 20 and B1 = 30

=OR(A1>40, B1>40)

❌ Result → FALSE (none are greater than 40)

🧠 3. Using AND / OR Inside IF

You can combine these functions with IF for decisions.

✅ Example with AND:

If a student passes both subjects:

=IF(AND(A1>=40, B1>=40), "Pass", "Fail")

 If both marks ≥ 40 → “Pass”

  If any mark < 40 → “Fail”

✅ Example with OR:

If a student passes in at least one subject:

=IF(OR(A1>=40, B1>=40), "Pass", "Fail")

 If either mark ≥ 40 → “Pass”

 If both < 40 → “Fail”

🧾 4. Summary Table
Returns TRUE
Function Checks If Example Result
When

All conditions
AND() All are true AND(50>40,60>40) TRUE
true

Any condition One or more

OR() OR(50>40,30>40) TRUE
true are true

Combines Both true =

IF(AND()) =IF(AND(A1>=40,B1>=40),"Pass","Fail") Pass
both Pass

Combines Any true =

IF(OR()) =IF(OR(A1>=40,B1>=40),"Pass","Fail") Pass
both Pass

SUMIF Function
➡️Adds (sums) values that meet a specific condition.

Formula:

=SUMIF(range, criteria, sum_range)

 range: where to check the condition

 criteria: the condition to match
 sum_range: the cells to add

Example:
If column A has names and column B has sales:

A (Name) B (Sales)
Riya 100
A (Name) B (Sales)
Aman 200
Riya 150
=SUMIF(A2:A4, "Riya", B2:B4)

✅ Result → 250 (adds Riya’s sales: 100 + 150)

📊 2. AVERAGEIF Function
➡️Finds the average of values that meet a specific condition.

Formula:

=AVERAGEIF(range, criteria, average_range)

Example:
Using the same table:

=AVERAGEIF(A2:A4, "Riya", B2:B4)

✅ Result → 125 ((100 + 150) ÷ 2)

🔢 3. COUNTIF Function
➡️Counts how many cells meet a specific condition.

Formula:

=COUNTIF(range, criteria)

Example:

=COUNTIF(A2:A4, "Riya")

✅ Result → 2 (Riya appears twice)

🧠 4. Summary Table
Function Purpose Example Result
SUMIF Adds values matching a condition =SUMIF(A2:A4,"Riya",B2:B4) 250
AVERAGEIF
Averages values matching a =AVERAGEIF(A2:A4,"Riya",B2:B4) 125
condition
 Function Purpose Example Result
Counts how many cells match a
COUNTIF =COUNTIF(A2:A4,"Riya") 2
condition

Relative Cell Reference

➡️It changes when you copy or move a formula to another cell.
Excel adjusts it automatically based on the new location.

Example:
If in cell C1 you write:

=A1 + B1

and then copy it to the next row (C2),

Excel automatically changes it to:

=A2 + B2

✅ Relative reference moves with the formula.

🔒 2. Absolute Cell Reference

➡️It does not change when you copy or move a formula.
You lock the row or column using $ sign.

Symbol: $

Formula:

=$A$1 + B1

Here, $A$1 is fixed — it will not change if copied anywhere.

Example:
If you copy this formula from row 1 to row 5:

=$A$1 + B1

Excel keeps $A$1 the same but changes B1 → B5.

✅ Absolute reference stays fixed.

🔢 3. Mixed References
You can lock only column or only row:

Type Example Meaning

Absolute Column $A1 Column A is fixed, row can change

Absolute Row A$1 Row 1 is fixed, column can change

🧠 4. Summary Table
Type Example Changes when copied? Description

Relative A1 ✅ Yes Adjusts automatically

Absolute $A$1 ❌ No Stays the same

Mixed $A1 or A$1 🔁 Partially Only row or column fixed