CONDITIONAL LOGIC
1. IF Function
Definition: Returns one value if a condition is true and another value if it’s false.
Assignment:
1. Create a Dataset:
o Enter the following data starting from cell A1:
Name Score
Alice 85
Bob 76
Charlie 92
Diana 65
2. Task:
o In cell C1, enter the formula: =IF(B1>=80, "Pass", "Fail").
o Drag the formula down to apply it to the other cells. This will mark scores 80 and
above as "Pass" and others as "Fail".
2. IFS Function
Definition: Evaluates multiple conditions and returns a value corresponding to the first true
condition.
Assignment:
1. Create a Dataset:
o Enter the following data starting from cell A1:
Name Score
SHIVANJALI DEORE
Alice 85
Bob 76
Charlie 92
Diana 65
2. Task:
o In cell C1, enter the formula: =IFS(B1>=90, "Excellent", B1>=80, "Good", B1>=70,
"Average", TRUE, "Needs Improvement").
o Drag the formula down to apply it to the other cells. This categorizes scores into
different performance levels.
3. SWITCH Function
Definition: Evaluates an expression against a list of values and returns the result corresponding
to the first match.
Assignment:
1. Create a Dataset:
o Enter the following data starting from cell A1:
Grade
2. Task:
o In cell B1, enter the formula: =SWITCH(A1, "A", "Excellent", "B", "Good", "C",
"Average", "D", "Below Average", "Unknown").
o Drag the formula down to apply it to the other cells. This will translate letter
grades into descriptive performance levels.
4. SUMPRODUCT Function
Definition: Multiplies corresponding components in given arrays and returns the sum of those
products.
Assignment:
1. Create a Dataset:
o Enter the following data starting from cell A1:
SHIVANJALI DEORE
Quantity Price
10 5
15 3
8 7
2. Task:
o In cell C1, enter the formula: =SUMPRODUCT(A1:A3, B1:B3).
o This will calculate the total revenue (Quantity * Price) for the items.
5. Conditional SUMPRODUCT
Definition: Uses SUMPRODUCT with conditions to sum products based on specific criteria.
Assignment:
1. Create a Dataset:
o Enter the following data starting from cell A1:
Product Quantity Price
A 10 5
B 15 3
A 8 7
B 12 6
2. Task:
o In cell D1, enter the formula: =SUMPRODUCT((A1:A4="A") * (B1:B4 * C1:C4)).
o This will calculate the total revenue for product "A".
6. LET Function
Definition: Allows you to define and name calculation results to use in a formula, improving
readability and performance.
Assignment:
1. Create a Dataset:
o Enter the following data starting from cell A1:
Value1 Value2
10 5
20 15
SHIVANJALI DEORE
30 25
2. Task:
o In cell C1, enter the formula: =LET(x, A1:A3, y, B1:B3, x + y).
o This formula calculates the sum of the ranges A1:A3 and B1:B3 using named
variables x and y.
7. LAMBDA Function
Definition: Creates custom functions that can be reused in Excel formulas.
Assignment:
1. Create a Dataset:
o Enter the following data starting from cell A1:
Value
10
20
30
2. Task:
o Define a LAMBDA function for calculating the square of a number:
▪ Go to Formulas > Name Manager > New.
▪ Name: Square
▪ Refers to: =LAMBDA(x, x^2)
o In cell B1, enter =Square(A1) and drag down to apply it to other cells. This
calculates the square of each value.
8. Name Manager
Definition: Manages named ranges and formulas used in your workbook.
Assignment:
1. Create a Named Range:
o Enter data in cells A1 to A3 as follows:
Data
100
200
SHIVANJALI DEORE
300
2. Task:
o Select the range A1
, and go to Formulas > Define Name.
o Name it SalesData.
o Use the name SalesData in a formula. For example, in cell B1, enter
=SUM(SalesData).
This sums up the values in the named range SalesData.
SHIVANJALI DEORE
