Logical Function in Excel

Logical Function in Excel

If and AND Function

Bonus-

=IF(D2<=20000,5%,IF(D2<=50000,10%,15%))

Selection-

=IF(AND(C2="Faridabad",D2<50000)=TRUE,"Yes","No")

 

	A	B	C	D	E	F
1	Emp Id	Candidate Name	Location	Salary	Bonus	Selection ?
2	1001	Pankaj	Delhi	24000	10%	No
3	1002	Naresh	Faridabad	61000	15%	No
4	1003	Mohan	Delhi	64000	15%	No
5	1004	Rahul	Faridabad	42000	10%	Yes
6	1005	Nakul	Gurgaon	68400	15%	No
7	1006	Neha	Faridabad	52120	15%	No
8	1007	Deepak	Delhi	87100	15%	No
9	1008	Lalit	Gurgaon	63140	15%	No

     

Salary	Bonus
0 to 20000	5%
20000 to 50000	10%
More then 50000	15%

Selection Criteria

Loaction- Faridabad

Salary<8000

       

OR Function

Selection-

=IF(OR(C2="Delhi",C2="Faridabad")=TRUE,"Yes","No")

 

	A	B	C	D	E
1	Emp Id	Candidate Name	Location	Salary	Selection ?
2	1001	Pankaj	Delhi	24000	Yes
3	1002	Naresh	Faridabad	61000	Yes
4	1003	Mohan	Delhi	64000	Yes
5	1004	Rahul	Faridabad	42000	Yes
6	1005	Nakul	Gurgaon	68400	No
7	1006	Neha	Faridabad	52120	Yes
8	1007	Deepak	Delhi	87100	Yes
9	1008	Lalit	Gurgaon	63140	No

 

Selection Criteria

Location=Faridabad, Delhi

 

OR and AND Function

Eligible-

=IF(AND(D2>50000,OR(C2="Finance",C2="Operations"))=TRUE,"Yes","No")

 

	A	B	C	D	E
1	Emp Id	Candidate Name	Department	Salary	Eligible ?
2	1001	Pankaj	Finance	24000	No
3	1002	Naresh	Operations	61000	Yes
4	1003	Mohan	HRA	64000	No
5	1004	Rahul	Sales	42000	No
6	1005	Nakul	Finance	68400	Yes
7	1006	Neha	HRA	52120	No
8	1007	Deepak	Operations	87100	Yes
9	1008	Lalit	Finance	63140	Yes

 

Bonus Eligible Criteria

Salary>50000

Department=finance, Operation

 

AND And OR Function

 

=IF(AND(OR(D2="Faridabad",D2="Delhi",OR(C2="Finance",C2="Operations")))=TRUE,"Yes","No")

 

A	B	C	D	E
Emp Id	Candidate Name	Department	Location	Eligible ?
1001	Pankaj	Finance	Delhi	Yes
1002	Naresh	Operations	Faridabad	Yes
1003	Mohan	HRA	Delhi	Yes
1004	Rahul	Sales	Faridabad	Yes
1005	Nakul	Finance	Gurgaon	Yes
1006	Neha	HRA	Faridabad	Yes
1007	Deepak	Operations	Delhi	Yes
1008	Lalit	Finance	Gurgaon	Yes

 

Eligibility Criteria

Location=Delhi, Faridabad

Department=Finance, Operation

 

Not Function

 

=IF(NOT(OR(A2="Red",A2="Orange")),"X"," ")

 

	A	B	C
1	Color	Quantity	Flag
2	Red	250
3	Orange	120
4	Black	530	X
5	Pink	450	X
6	Green	236	X
7	Magenta	450	X
8	Yellow	268	X