[go: up one dir, main page]
Scribd
Upload
5 views18 pages

Logical Functions

The document contains a detailed employee payroll listing, including employee codes, names, addresses, cities, regions, departments, and various salary components such as basic pay, allowances, bonuses, and deductions. It also includes calculations for taxes and other financial metrics related to employee compensation. Additionally, there are sections with product stock information, customer discounts, and student scores.

Uploaded by

dm583077
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
5 views18 pages

Logical Functions

The document contains a detailed employee payroll listing, including employee codes, names, addresses, cities, regions, departments, and various salary components such as basic pay, allowances, bonuses, and deductions. It also includes calculations for taxes and other financial metrics related to employee compensation. Additionally, there are sections with product stock information, customer discounts, and student scores.

Uploaded by

dm583077
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
You are on page 1/ 18

Emp Code Employee Name Address City Region Department Basic DA HRA

JP Kumar Andheri (W) Mumbai W Training 6,250 1,875 3,250


2 Anjali Thakur Govindpuri Delhi N Accounts 8,750 2,625 4,550
3 Priya Agarwal Sector 9 Noida N Marketing 11,250 3,375 5,850
4 R Vasu Egmore Chennai S R&D 10,000 3,000 5,200
5 Sanjay Gupta M.G Road Delhi N Training 16,250 4,875 8,450
6 Jharna Biswal Link Road Cuttack E Accounts 6,400 1,920 3,328
7 Prakash Dutta Elgin Road Kolkata E Marketing 4,500 1,350 2,340
8 Manisha Guha Alipore Kolkata E R&D 6,275 1,883 3,263
9 Arjun Jain MG Road Mumbai W Training 6,250 1,875 3,250
10 Arjun Kapoor North Road Bangalore S Accounts 8,750 2,625 4,550
11 Abrar Jayanagar Bangalore S Marketing 11,250 3,375 5,850
12 Shahid Khan M.G Road Mangalore S R&D 10,000 3,000 5,200
13 Anupam Mishra Govindpuri Mysore S Training 16,250 4,875 8,450
14 Ashwini Sector 9 Mangalore S Accounts 6,400 1,920 3,328
15 Prateek Babbar North Road Delhi N Marketing 4,500 1,350 2,340
16 Sukanya Reddy North Road Mangalore S R&D 6,275 1,883 3,263
17 Satish Puri Bandra Mumbai W Operation 6,250 1,875 3,250
18 Anjum Chopra Worli Mumbai W R&D 8,750 2,625 4,550
19 Akram Khan S P B Road Pune W Operation 11,250 3,375 5,850
20 Manish Grover L L R Road Noida N Training 10,000 3,000 5,200

=30%*G2 =40%*(G2+H2)
TA TA PF
0 0 750
0 0 1,050
5,000 5,000 1,350
0 0 1,200
0 0 1,950
0 0 768
5,000 5,000 540
0 0 753
0 0 750
0 0 1,050
5,000 5,000 1,350
0 0 1,200
0 0 1,950
0 0 768
5,000 5,000 540
0 0 753
0 0 750
0 0 1,050
0 0 1,350
0 0 1,200

=IF(AND(F2="Marketing"),500=IF(AND(F2=$F$4),5000,0) =12%*G2
Bonus Bonus
2,500 2,500
0 0
0 0
0 0
0 0
0 0
0 0
0 0
2,500 2,500
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0

=IF(AND(F2="Training",E2="W"),40%*G2,0) =IF(AND(F2=$F$2,E2=$E$2),40%*G2,0)
Incentive Incentive
0 0
5000 5000
0 0
0 0
0 0
5000 5000
0 0
0 0
0 0
5000 5000
0 0
0 0
0 0
5000 5000
0 0
0 0
5000 5000
0 0
5000 5000
0 0

=IF(OR(F2="Accounts",F2="Operation"),5000,0) =IF(OR(F2=$F$3,F2=$F$18),5000,0)
Overtime Overtime
3,500 3,500
0
3,500
3,500
3,500
0
3,500
3,500
3,500
0
3,500
3,500
3,500
0
3,500
3,500
0
3,500
0
3,500

=IF(OR(F2=$F$3,F2=$F$18),0,3500)
Overtime
3,500
0
3,500
3,500
3,500
0
3,500
3,500
3,500
0
3,500
3,500
3,500
0
3,500
3,500
0
3,500
0
3,500

=IF(NOT(OR(F2="Accounts",F2="Operation")),3500,0)
P.Tax
50
50
100
50
150
50
0
50
50
50
100
50
150
50
0
50
50
50
100
50

=IF(G2<5000,0,IF(G2<=10000,50,IF(G2<=15000,100,IF(G2<=20000,150,200))))
P.Tax
50
50
100
50
150
50
0
50
50
50
100
50
150
50
0
50
50
50
100
50

{=IFS(G2<5000,0,G2<=10000,50,G2<=15000,100,G2<=20000,150,G2>20000,200)}
CCA
5000
5000
0
5000
5000
0
5000
5000
5000
5000
5000
0
0
0
5000
0
5000
5000
0
0

=IF(OR(D2="Mumbai",D2="Delhi",D2="Kolkata",D2="Chennai",D2="Bangalore"),5000,0)
CCA
5000
5000
0
5000
5000
0
5000
5000
5000
5000
5000
0
0
0
5000
0
5000
5000
0
0

=IF(NOT(OR(D2="Mumbai",D2="Delhi",D2="Kolkata",D2="Chennai",D2="Bangalore")),0,5000)
CCA
5000
5000
0
5000
5000
0
5000
5000
5000
5000
5000
0
0
0
5000
0
5000
5000
0
0

=IF(OR(D2=$D$2,D2=$D$3,D2=$D$8,D2=$D$5,D2=$D$11),5000,0)
Special Allowance

=IF(OR(AND(F2="Training",E2="W"),AND(F2="R&D",E2="S"),AND(F2="Marketing",E2="N"),AND(F2="Accounts",E2="E")),30%*
Gross Salary Net Salary Annual Salary
25,050 24,250 300,600
27,025 25,925 324,300 P.Tax on Basic
33,800 32,350 405,600 <5000 0
30,950 29,700 371,400 >=5000 & <=10000 50
40,175 38,075 482,100 >10000 & <=15000 100
19,386 18,568 232,632 >15000 & <=20000 150
22,230 21,690 266,760 >20000 200
20,724 19,921 248,682
25,050 24,250 300,600
27,025 25,925 324,300 Training W
35,425 33,975 425,100 R&D S
30%*Basic 0
25,950 24,700 311,400 Marketing N
35,175 33,075 422,100 Accounts E
17,466 16,648 209,592
23,580 23,040 282,960
17,606 16,803 211,272
22,175 21,375 266,100
25,525 24,425 306,300
26,925 25,475 323,100
22,950 21,700 275,400

=SUM(G2:J2,L2:M2,P2:Q2,U2:V2,Y2) =Z2-L2-T2 =Z2*12


Emp Code Employee Name Address City Region Department Basic
1 JP Kumar Andheri (W) Mumbai W Training 6,250
2 Anjali Thakur Govindpuri Delhi N Accounts 8,750
3 Priya Agarwal Sector 9 Noida N Marketing 11,250
4 R Vasu Egmore Chennai S R&D 10,000
5 Sanjay Gupta M.G Road Delhi N Training 16,250
6 Jharna Biswal Link Road Cuttack E Accounts 6,400
7 Prakash Dutta Elgin Road Kolkata E Marketing 4,500
8 Manisha Guha Alipore Kolkata E R&D 6,275
9 Arjun Jain MG Road Mumbai W Training 6,250
10 Arjun Kapoor North Road Bangalore S Accounts 8,750
11 Abrar Jayanagar Bangalore S Marketing 11,250
12 Shahid Khan M.G Road Mangalore S R&D 10,000
13 Anupam Mishra Govindpuri Mysore S Training 16,250
14 Ashwini Sector 9 Mangalore S Accounts 6,400
15 Prateek Babbar North Road Delhi N Marketing 4,500
16 Sukanya Reddy North Road Mangalore S R&D 6,275
17 Satish Puri Bandra Mumbai W Operation 6,250
18 Anjum Chopra Worli Mumbai W R&D 8,750
19 Akram Khan S P B Road Pune W Operation 11,250
20 Manish Grover L L R Road Noida N Training 10,000
Logical Functions
IF
AND
OR
NOT
IFS
NESTED IFS

Product Stock Sold Availability Order No.


Bananas 40 30 Available 101
Apples 50 50 Out of Stock 102
Bananas 30 30 Out of Stock 103
Oranges 50 20 Out of Stock 104
105
106
107
108
109

Customer Nmae Items Discount % Note Discount %


Shreya 44 25 5-10 Items 5
Sarmistha 33 20 11-20 Item 10
Abhishek 37 20 21-30 Item 15
Subham 20 15 31-40 Item 20
Samrat 14 10 41-50 Item 25
Sahil 8 5
Name Score Result
Hindolii 80 Pass
Saurav 38 Fail
Ripon 86 Pass
Rajasree 97 Pass
Subhendu 65 Pass
Arpan 78 Pass
Ramiz 43 Pass
Soma 40 Fail
Pankaj 37 Fail

Progress Status Item Descriptio Price


Delivered Closed 1234 Phone 5000 TRUE
In Transit Open 1235 Laptop 10000 TRUE
Cancelled Open 1236 Headphon 500 FALSE
Delivered Closed 1237 Charger 500 FALSE
Paid Closed 1238 Adapter 1500 FALSE
In Transit Open
Paid Closed
Cancelled Open
Delivered Closed

Discount %

)
Emp Code Employee Name Address City Region Department Basic Age Gender
1 JP Kumar Andheri (W) Mumbai W Training 6,250 25 Male
2 Anjali Thakur Govindpuri Delhi N Accounts 8,750 33 Female
3 Priya Agarwal Sector 9 Noida N Marketing 11,250 21 Others
4 R Vasu Egmore Chennai S R&D 10,000 32
5 Sanjay Gupta M.G Road Delhi N Training 16,250 17
6 Jharna Biswal Link Road Cuttack E Accounts 6,400
7 Prakash Dutta Elgin Road Kolkata E Marketing 4,500
8 Manisha Guha Alipore Kolkata E R&D 6,275
9 Arjun Jain MG Road Mumbai W Training 6,250
10 Arjun Kapoor North Road Bangalore S Accounts 8,750
11 Abrar Jayanagar Bangalore S Marketing 11,250
12 Shahid Khan M.G Road Mangalore S R&D 10,000
13 Anupam Mishra Govindpuri Mysore S Training 16,250
14 Ashwini Sector 9 Mangalore S Accounts 6,400
15 Prateek Babbar North Road Delhi N Marketing 4,500
16 Sukanya Reddy North Road Mangalore S R&D 6,275
17 Satish Puri Bandra Mumbai W Operation 6,250
18 Anjum Chopra Worli Mumbai W R&D 8,750
19 Akram Khan S P B Road Pune W Operation 11,250
20 Manish Grover L L R Road Noida N Training 10,000
ALT+A+V+V

ALT+D+L

You might also like