Scribd
Upload
53 views10 pages

Data Analysis & Loan Overview

Here are the steps to find the requested information from the employee data: 1. Filter the data for employees in the south region. 2. Sum their salaries. The total salary for south region employees is 350000. 2. Filter the data for employees with salary more than 50000. 3. Count the number of employees. There are 4 employees with salary more than 50000. 3. Filter the data for employees in the ADM dept. 4. Count the number of employees. There are 3 employees in the ADM dept. 4. Filter the data for employees in the sales dept in the south region with salary more than 50000. 5. Count the number of employees

Uploaded by

ROHAN DESAI
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
53 views10 pages

Data Analysis & Loan Overview

Here are the steps to find the requested information from the employee data: 1. Filter the data for employees in the south region. 2. Sum their salaries. The total salary for south region employees is 350000. 2. Filter the data for employees with salary more than 50000. 3. Count the number of employees. There are 4 employees with salary more than 50000. 3. Filter the data for employees in the ADM dept. 4. Count the number of employees. There are 3 employees in the ADM dept. 4. Filter the data for employees in the sales dept in the south region with salary more than 50000. 5. Count the number of employees

Uploaded by

ROHAN DESAI
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/ 10

Item Date Cost

Brakes 1-Jan-98 80
Tyres 10-May-98 25
Brakes 1-Feb-98 80
Service 1-Mar-98 150
Service 5-Jan-98 300
Window 1-Jun-98 50
Tyres 1-Apr-98 200
Tyres 1-Mar-98 100
Clutch 1-May-98 250

How many Brake Shoes Have been bought. 2 =COUNTIF(C4:C12,"Brakes")


How many Tyres have been bought. 3 =COUNTIF(C4:C12,"Tyres")
How many items cost £100 or above. 5 =COUNTIF(E4:E12,">=100")

Type the name of the item to count. service 2 =COUNTIF(C4:C12,E18)

What Does It Do ?
This function counts the number of items which match criteria set by the user.
LOAN BORROWED 20000000
LIFE IN YEARS 30
INTEREST RATE 9%   LOAN AMORTIZATION TABLE
PMT 1946727.03 YEAR OPN BAL PMT
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
N TABLE  
INT PRINCIPLE CLO BALANCE
LOAN BORROWED 20000000
LIFE IN YEARS 30
INTEREST RATE 9%   LOAN AMORTIZATION TABLE
PMT 1946727.03 YEAR OPN BAL PMT
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
N TABLE  
INT PRINCIPLE CLO BALANCE
FirstDate SecondDatInterval Difference
1-Jan-60 ### days 3782 =DATEDIF(C4,D4,"d")
1-Jan-60 ### months 124 =DATEDIF(C5,D5,"m")
1-Jan-60 ### years 10 =DATEDIF(C6,D6,"y")
1-Jan-60 ### yeardays 130 =DATEDIF(C7,D7,"yd")
1-Jan-60 ### yearmonth 4 =DATEDIF(C8,D8,"ym")
1-Jan-60 ### monthdays 9 =DATEDIF(C9,D9,"md")

What Does         
This function calculates the difference between two dates.
It can show the result in weeks, months or years.
Division Budget Amt.SpentGrowth PotNeeds review Needs review
Engines 250000 286000 Yes 0 no review needed
Wheels 75000 71245 Yes 0 no review needed
Axles 125000 137456 No 1 needs review
Chassis 205000 190000 No 0 no review needed
emp code name dept region branch hire date salary commission
1 as adm south blore 1/6/2018 50000 6000 q1
2 sd sale north del 1/5/2018 20000 2400
3 fg fin east kol 5/25/2017 50000 6000 q2
4 ty hr west mum 1/3/2018 30000 3600
5u prod south blore 4/8/2018 100000 12000 q3
6i adm north del 5/8/2018 20000 2400
7 opo sale east kol 4/8/2017 50000 6000 q4
8 ert fin west mum 4/1/2017 30000 3600
9 fgh hr south blore 3/3/2018 100000 12000 q5
10 yui prod north del 1/6/2018 20000 2400
11 opl adm east kol 1/5/2018 50000 6000
12 jk sale west mum 5/25/2017 30000 3600
13 lo fin south blore 1/3/2018 100000 12000
14 we hr north del 4/8/2018 20000 2400
15 rt prod east kol 5/8/2018 50000 6000
16 yui hr west mum 4/8/2017 30000 3600
17 iop sale east kol 4/1/2017 45000 5400
18 pl prod east kol 3/3/2018 55000 6600
find the salry of employees in south region
350000
find total salary paid to employees more than 50000
4
find the number of employees workiung in adm dept
3
find total salary paid to employees in sales dept in south region more than 50000
0
total salary paid in sales dept. in south more than 50000
0
North region
80000
more than 100000
3
PROD DEPT.
4
PROD DEPT.,NORTH,SALARY>10000
20000

You might also like