LOGICAL OPERATORS

AND
OR
XOR
NOT
IF
IFS
IFERROR
IFNA
TRUE
FALSE
Logical Operators Sales Sales =
= 19 13 0
<> 12 12
> 11 10
< 10 -5
>= 20 15
<=

FALSE FALSE

Publisher Genre Date Qty Unit Sales

Nintendo Sports 1/22/2020 10 ₹ 137,853.00
Nintendo Shooter 1/23/2020 12 ₹ 132,117.00
Nintendo Puzzle 1/24/2021 20 ₹ 106,470.00
Nintendo Shooter 1/25/2023 13 ₹ 136,030.00
Berkeley Simulation 1/20/2020 16 ₹ 143,686.00
Berkeley Racing 1/21/2021 10 ₹ 105,918.00
Avanquest Software Shooter 1/17/2020 15 ₹ 148,762.00
Avanquest Software Puzzle 1/18/2021 14 ₹ 123,919.00
Avanquest Software Shooter 1/19/2023 15 ₹ 121,464.00
Avanquest Simulation 1/14/2020 10 ₹ 149,433.00
Avanquest Racing 1/15/2021 14 ₹ 102,671.00
Avanquest Sports 1/16/2023 18 ₹ 119,780.00
Avalon Interactive Shooter 1/11/2020 14 ₹ 120,131.00
Avalon Interactive Puzzle 1/12/2021 16 ₹ 123,919.00
Avalon Interactive Shooter 1/13/2023 17 ₹ 132,522.00
<> > < >=
1 TRUE

AND OR XOR
1 TRUE

XOR TABLE - 1 argument

Expression1 Result
TRUE TRUE
FALSE FALSE

XOR TABLE - 2 arguments

Expression1 Expression2 Result
TRUE TRUE FALSE
TRUE FALSE TRUE
FALSE TRUE TRUE
FALSE FALSE FALSE

XOR TABLE - 3 or more arguments

Expression1 Expression2 Expression3
TRUE TRUE TRUE
TRUE TRUE TRUE
FALSE FALSE FALSE

Numeric Value True / False Equiv NOT

0 0 TRUE
1 1 FALSE
2 1 FALSE
-5 1 FALSE
4 1 FALSE
2000 1 FALSE
 <=

NOT

Logic Result
True Count is ODD TRUE
All cells are FALSE FALSE
True Count is EVEN FALSE

Expression4 Result
TRUE FALSE
FALSE TRUE
FALSE FALSE
 Employee 01 02 03 04 05 06 07 08 09 10
Anjali Ray P P P P P P P P P P
Suraj Cheema P P P P P P P P P P
Rajesh Rai P P P P A A A A A P
Aditi Chaudhuri P P P P P P P P P P
Sid Rout P P P P P P P P P P

Absent - Unapproved Leave A

Leave - Approved Leave L
Leave - Approved Leave W/O

Employee 01 02 03 04 05 06 07 08 09 10
Anjali Ray P P P P P P P P P P
Suraj Cheema P P P P P P P P P P
Rajesh Rai P P P P A P P P P P
Aditi Chaudhuri P P P P P P P P P P
Sid Rout P P P P P P P P P P

Condition TRUE
Not more than 2 approved leaves
Not more than 1 unapproved leave

Employee Sales Employee Incentive Incentive

Anjali Ray 93 Anjali Ray 2000 2000
Suraj Cheema 32 Suraj Cheema 500 500
Rajesh Rai 15 Rajesh Rai 0 0
Aditi Chaudhuri 28 Aditi Chaudhuri 500 500
Sid Rout 73 Sid Rout 1000 1000

Criteria
>=80 Sales - Rs. 2000
>=50 and <80 Rs. 1000
>=25 and <50 Rs. 500

Employee 01 02 03 04 05 06 07 08 09 10
Anjali Ray W/O A P P P P W/O W/O P P
Suraj Cheema W/O P P P P A W/O W/O P P
Rajesh Rai W/O P P P A P W/O W/O A P
Aditi Chaudhuri W/O P P P P P W/O W/O P P
Sid Rout W/O P P P P P W/O W/O P P

Condition
if absent on Monday - not eligible
if absent on Friday - not eligible
 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
P P P A P P P P P P P P P P P
P P P P P P P P P P P P P P P
P P P P P P P P P P P P P P P
P P P P P P P P P A A A P P P
P P P P P P P P P P P P P P P

11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
P P P A P P P P L L L L L L L
P P P P P P P P P P P L L L P
P P P L L P P P P P P P P P P
P P P P P P P L L A A A P P P
P P P P P P P P P P P P P P P

FALSE

Incentive Incentive
2000 2000 2000
500 500 500 CheckAnotherCondition
0 0 #N/A YES IT IS NA CheckAnotherCondition
500 500 500
1000 1000 1000
1 TRUE

11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
P P P W/O W/O A P P P P W/O W/O P P P
P A P W/O W/O P P P P A W/O W/O P P P
P P P W/O W/O A P P P P W/O W/O P P P
P P P W/O W/O P P P P A W/O W/O P P P
P P A W/O W/O A P P P P W/O W/O P P P

2 0 2
3 3
3 0
1 1
2 1
26 27 28 29 30 31 Days Present Eligibility
P P P P P P 30 Eligible
P P P P P P 31 Eligible
P P P P P P 26 NA
P P P P P P 28 NA
P P P P P P 31 Eligible

TRUE

26 27 28 29 30 31 Unapproved Approved Eligibility

P P P P P P 1 7 NOT ELIGIBLE
P P P P P P 0 3 NOT ELIGIBLE
P P P P P P 1 2 ELIGIBLE
P P P P P P 3 2 NOT ELIGIBLE
P P P P P P 0 0 ELIGIBLE

TRUE

26 27 28 29 30 31 Eligibility
P P W/O W/O P P NA
P A W/O W/O P P Eligible
P P W/O W/O A P NA
P P W/O W/O P P Eligible
P P W/O W/O P P NA
 This is the final homework for today's functions with some of the functions that we have covered in the previous

Publisher Genre Date Qty Unit Sales Total Sales

Nintendo Sports 1/22/2020 10 ₹ 137,853.00 ₹ 1,378,530.00
Nintendo Shooter 1/23/2020 12 ₹ 132,117.00 ₹ 1,585,404.00
Nintendo Puzzle 1/24/2021 20 ₹ 106,470.00 ₹ 2,129,400.00
Nintendo Shooter 1/25/2023 13 ₹ 136,030.00 ₹ 1,768,390.00
Berkeley Simulation 1/20/2020 16 ₹ 143,686.00 ₹ 2,298,976.00
Avanquest Software Racing 1/21/2021 10 ₹ 105,918.00 ₹ 1,059,180.00
Avanquest Software Shooter 1/17/2020 15 ₹ 148,762.00 ₹ 2,231,430.00
Avanquest Software Puzzle 1/18/2021 14 ₹ 123,919.00 ₹ 1,734,866.00
Avanquest Software Shooter 1/19/2023 15 ₹ 121,464.00 ₹ 1,821,960.00
Avanquest Simulation 1/14/2020 10 ₹ 149,433.00 ₹ 1,494,330.00
Avanquest Racing 1/15/2021 14 ₹ 102,671.00 ₹ 1,437,394.00
Avanquest Sports 1/16/2023 18 ₹ 119,780.00 ₹ 2,156,040.00
Avalon Interactive Shooter 1/11/2020 14 ₹ 120,131.00 ₹ 1,681,834.00
Avalon Interactive Puzzle 1/12/2021 16 ₹ 123,919.00 ₹ 1,982,704.00
Avalon Interactive Shooter 1/13/2023 17 ₹ 132,522.00 ₹ 2,252,874.00

1 If the total sum of sales for Nintendo for Genre-Shooter is greater than 2.5 lakhs then return "Target met" else retu
Using IF and SUMPRODUCT
Using IF and SUMIFS

2 Convert the belwo 4 criteria to formulas to return TRUE or FALSE. None of the 4 criteria is interconnected with oth
Criteria 1 Count of Nintendo is > 3
Criteria 2 Count of Shooter is > 5
Criteria 3 sum of total qty > 500
Criteria 4 average of total sales is >18 Lakhs

Use the above TRUE/FALSE from G25:G28 in XOR and return the result.

Explanation on why you got TRUE or FALSE

3 Using Vlookup find the total sales. Handle any errors using IFERROR/IFNA. If there is an error then return "Not Fou

Publisher Total Sales

Berkeley Using IFERROR
Berkeley Using IFNA

4 If the sales of the employee is above the average sales then return "Above Average" else return "Below Average".
Make proper use of Relative and Absolute referencing.

Employee Sales Status

Anjali Ray 93
Suraj Cheema 32
 Rajesh Rai 15
Aditi Chaudhuri 28
Sid Rout 73

5 If the name of the employee starts with "A" AND the sales is greater than 50 then return TRUE else return FALSE

Employee Sales Status

Anjali Ray 93
Ashish Jain 32
Rajesh Rai 15
Aditi Chaudhuri 28
Ana Michael 73
we have covered in the previous sessions

TRUE FALSE

0
0
0
0

hen return "Target met" else return "Target not Met".

iteria is interconnected with others

is an error then return "Not Found". Once you have written the formula, change "Berkeley" in cell B39/B40 to "Apple" and see if you get "

e" else return "Below Average". Do not calculate the Average manually.
return TRUE else return FALSE
to "Apple" and see if you get "Not Found"