1-exercises- training.xlsx

7/30/2019 1-Exercises- Training.xlsx

1/72

2 2 1 jan week 1

2 feb week 2

3 3 mar week 3

4 4 apr week 4

5 5 may week 5

6 18 6 jun week 6

18 7 jul week 7

18 8 aug week 8

18 9 sep week 9

10 oct week 10

11 nov week 11

12 dec week 12

13 jan week 13

14 feb week 14


2/72

Monday

Tuesday

Wednesday

Thursday

Friday

Monday

Tuesday


3/72

Region Activation


4/72


5/72

Question 1:

1-create a list of numbers ranging from 1 to 30 without

manually inserting numbers or using sum function

Answer:

1

2

3

4

5

6

7

8

9

10

11

12

1314

15

16

17

18

19

20

21

22

23

2425

26

27

28

29

30


6/72

Question 2:

1- Create a list of even numbers ranging from 2 to 50

without manually inserting numbers or using sum function

Answer:

2

4

6

8

10

12

14

16

18

20

22

24

2628

30

32

34

36

38

40

42

44

46

4850


7/72

Question:

Create Table from 1 to 18 by typing in only 4 times (table should be till 12 times i.e 2X12=24, 3

Only result is required no need to right all 2X1=2, 2X2=4 simply answer will be 2,4)

Table of 1 Table of 2 Table of 3 Table of 4 Table of 5 Table of 6 Table of 7 Table of 8

1 2 3 4 5 6 7 8

2 4 6 8 10 12 14 16

3 6 9 12 15 18 21 24

4 8 12 16 20 24 28 32

5 10 15 20 25 30 35 40

6 12 18 24 30 36 42 48

7 14 21 28 35 42 49 56

8 16 24 32 40 48 56 64

9 18 27 36 45 54 63 72

10 20 30 40 50 60 70 80

11 22 33 44 55 66 77 88

12 24 36 48 60 72 84 96


8/72

X12=36

Table of 9 Table of 10 Table of 11 Table of 12 Table of 13 Table of 14 Table of 15 Table of 16

9 10 11 12 13 14 15 16

18 20 22 24 26 28 30 32

27 30 33 36 39 42 45 48

36 40 44 48 52 56 60 64

45 50 55 60 65 70 75 80

54 60 66 72 78 84 90 96

63 70 77 84 91 98 105 112

72 80 88 96 104 112 120 128

81 90 99 108 117 126 135 144

90 100 110 120 130 140 150 160

99 110 121 132 143 154 165 176

108 120 132 144 156 168 180 192


9/72

Table of 17 Table of 18

17 18

34 36

51 54

68 72

85 90

102 108

119 126

136 144

153 162

170 180

187 198

204 216


10/72

Question:

Create weekly timetable template with 6 one hour consecutive classes for 5 days a week. Mak

Time will rannge from 8:30 to 15:30 by typing just once

Days will range from Monday to Friday by typing just twice

Time

Days

8:30 9:30 10:30 11:30 12:30 13:30 14:30

Monday 8:30 9:30 10:30 11:30 12:30 13:30 14:30

Tuesday 8:30 9:30 10:30 11:30 12:30 13:30 14:30

Wednesday 8:30 9:30 10:30 11:30 12:30 13:30 14:30

Thursday 8:30 9:30 10:30 11:30 12:30 13:30 14:30

Friday 8:30 9:30 10:30 11:30 12:30 13:30 14:30

Monday 8:30 9:30 10:30 11:30 12:30 13:30 14:30


11/72

this template for a month

15:30

15:30

15:30

15:30

15:30

15:30

15:30


12/72

Question:

Create a autofill series from below list.

Just by typing in twice repeat the list 4 times

N1 N1

N2 N2

S1 N3

S2 N4


13/72

Question:

1- Highlight Top 5 Scores with Green Color

2-Highlight Lowest 5 Scores with Pink color

3- Highlight upto 80% marks with green ball, 79% to 70% marks with yellow, 69% to 60% marks with

Clear both formatting in one go.

Name Marks %

Usman 82

Ahmer 76

Farah 85

Naseem 87

Naila 93

Sadaf 80

Khurrum 66

Mazher 32

Ali 78

Sana 59

Umer 92

Imran 68Fatima 79

Erum 77

Niaz 89

Iqbal 91

Gazi 67

Zahid 75


14/72

Month A B C

Jan-11 25,000 17,000 9,000

Feb-11 22,000 19,000 8,500

Mar-11 24,000 19,000 11,000

Apr-11 28,000 20,000 10,000

May-11 27,000 23,000 13,000Jun-11 29,000 21,000 14,500

Question:

Make the above data look like the picture on right side.

Month A B C

Jan-11 25,000 17,000 9,000

Feb-11 22000 19000 8500

Mar-11 24000 19000 11000 Apr-11 28000 20000 10000

May-11 27000 23000 13000

Jun-11 29000 21000 14500

Monthly Sales

Monthly Sales

Month

Jan-

Feb-

Mar-

Apr-May-

Jun-


15/72

21000

A B C

1 25,000 17,000 9,000

11 22000 19000 8500

11 24000 19000 11000

11 28000 20000 10000 11 27000 23000 13000

11 29000 21000 14500

Monthly Sales


16/72

Project Start End 2002 2003 2004 2005 2006 2007

Project 1 2003 2006

Project 2 2007 2012

Project 3 2005 2010

Project 4 2008 2010

Project 5 2010 2016

Use Conditional Formatting to Create Gantt Chart


17/72

2008 2009 2010 2011 2012 2013 2014 2015 2016


18/72

Job Start End 9/15 9/16 9/17 9/18 9/19 9/20

Project 4 9/15 9/21

Project 3 9/20 9/26

Project 1 9/24 9/30

Project 5 9/22 10/2

Project 2 9/24 9/26


19/72

9/21 9/22 9/23 9/24 9/25 9/26 9/27 9/28 9/29


20/72

1/6

A Question:

A Use conditional Formatting to Highlight cell where value

A

B

B

C 0

C

C

D

D

E

E

F

F

F

F

G

GH

I

I

I

J

J

K


21/72

changes


22/72

Question:

1- Change the fill to shades of Purple

2- Add another same Shape for Taxes

3- change the shapes to some other option where we can use pictures too.


23/72


24/72

Question:

Create a header and footer as explained

Items to be used in Header- Logo, Name of Company, Name of Report

Items to be used in footer- Page No


25/72

Question:

Create Main sheet for your Report- Be Creative

3

4

5

6

18

87

87

105

39.375

9


26/72

Jan-11 Feb-11 Mar-11 Total Average Max Min

Product A 9,000 6,624 3,433

Product B 7,600 5,366 5,322

Product C 8,100 6,886 2,435

Product D 8,900 8,655 2,536

Product E 8,500 6,554 5,644

Product F 9,000 7,565 3,545Product G 7,900 5,676 2,324

Total

Average

Max

Min

Question:

1- Fill in all required Formulas without typing

2- Format the results to numbers with comas like filled in question


27/72

Question:

Separate the First Name and Family without using any function/formula

Original Text First Name Family Name

Miki,Ali Miki Ali

Isaac,Dino Isaac Dino

Tom,Sceana Tom Sceana

Afrikan,Ho Afrikan Ho

Tina,Ward Tina Ward

Gigi,Smith Gigi Smith

Pham,Ho Pham Ho

Miki,Smith Miki Smith

Tina,Nano Tina Nano

Chris,Mitchel Chris Mitchel

Sue,Radcoolinator Sue Radcoolinator

Sheliadawn,Ward Sheliadawn Ward

Afrikan,Dino Afrikan Dino

Isaac,Smith Isaac Smith


28/72

Question:

Apply Data Validation for each column as required

Name Department Date of Joining Years of Service

Only Text Drop down list Only Date Only Number

Marketing 3/13/2013 8

Marketing

Sales

COPS

O&M

NPS

Roll Out

HR

Admin

IT

Legal


29/72

CNIC Number Tax Slab

length of 13 Number only


30/72

Jan Feb Mar Apr

Kamal 179 126 190 187

Yousaf 172 155 144 197

Nadia 109 137 134 163

Hira 168 175 164 183

Ijaz 0 126 181 105

4


31/72

Jan Feb Mar Apr

Kamal 141 156 189 133

Yousaf 107 140 164 121

Nadia 100 143 142 153

Hira 104 114 109 111

Ijaz 0 150 123 175

Iqra 179 129 109 155Mateen 199 192 189 149

5


32/72

Jan Feb Mar Apr

Kamal 168 162 196 128

Yousaf 133 152 176 153

Nadia 172 186 151 113

Hira 134 163 181 142

6


33/72

Jan Feb Mar Apr

1-Exercises- Trainin 179 126 190 187



Kamal 488 444 575 448




34/72


Yousaf 412 447 484 471




Nadia 381 466 427 429




Hira 406 452 454 436

Ijaz 0 276 304 280

Iqra 179 129 109 155

Mateen 199 192 189 149


35/72

Data1 Data1 Data2 Empty cell above criteria cell is necessary for Advance

Criteria

Data1 Data2 Data1 Data2

1 8 4 1

4 1 3 7

12 10 5 2

8 3 4 8

3 7 7 10

5 2

10 5

9 3

10 7

4 8

7 10


36/72

ilter


37/72

S.No Data to work on

1 Invoice Due Date 25-Jun-13

Today's Date

Days Past Due

2 Loan Issue Date

Loan Term (Days)Due Date

1 Your Birthday

Today

Days Alive

Years Alive


38/72

Required

Enter today's date without typing date, use function

Enter fomula to get number of days past due

Enter Formula to calculate Due Date

Enter your date of Birth

Enter today's date without typing date, use function

Enter formula to check your age in Days

Enter formula to check your age in years


39/72

Day Month Year

25 3 2011

12 5 2011

4 1 2011

DOB 8/14/1940

Age in Years

& Months

& days

Dealine for project

Days left

Age is 72 years 9 Months 9 days

Use formula to write age in test format by usin

Calculate days remaining to complet


40/72

Result Question:

3/25/2011 combine all to form date

5/12/2011

1/4/2011

Result Question:

72 Age in years

9 Age in 2 past years

9 Age in days past month

25-dec-12

g below formula.

project


41/72

Date

14-Aug-47

Required Result Question:

Year Extract Year 1947

Month Extract Month 8

Day Extract Day 14

1947


42/72

Original Result

microsoft excel MICROSOFT EXCEL

MICROSOFT EXCEL microsoft excel

microsoft excel Microsoft Excel

miCroSoFT ExCEL Microsoft Excel

miCroSoFT ExCEL microsoft excel

miCroSoFT ExCEL MICROSOFT EXCEL


43/72

Question:

Convert all in Upper Caps

Convert in lower cap

only first letter capital

only first letter capital

Convert in lower cap

Convert all in Upper Caps


44/72

Original Result

Microsoft Excel Microsoft Excel

Microsoft Excel Microsoft Excel

M I C R O M I C R O


45/72

Date Sales Person Sales

1/1/2011 Maria 2,634 Citeria: Kamal

1/2/2011 Ahmar 2,181

1/3/2011 Ahmar 4,300

1/4/2011 Kamal 6,386 Sum If 7,549

1/7/2011 Maria 8,142 Count If 2

1/8/2011 Maria 5,833

1/9/2011 Hira 3,658

1/10/2011 Ahmar 4,613

1/11/2011 Hira 4,832

1/14/2011 Maria 8,618

1/15/2011 Ahmar 9,954

1/16/2011 Maria 6,181

1/17/2011 Hira 2,973

1/18/2011 Hira 6,226

1/21/2011 Kamal 1,163


46/72

sales greater than 5000 8000

Sum If 51,341

Count If 7

26,714


47/72

Year Sales COGS Profit SalesRep

2006 2,095 838 1,257 Iqbal

2006 4,022 2,128 1,894 Yasir

2006 5,542 2,217 3,325 Ali

2007 6,527 3,453 3,074 Usman

2005 9,392 3,757 5,635 Danial

2004 16,685 8,828 7,857 Iqbal

2004 6,054 2,422 3,632 Usman

2006 6,815 3,606 3,209 Danial

2006 14,894 5,958 8,936 Omer

2008 16,962 8,975 7,987 Ali

2006 5,160 2,064 3,096 Omer

2009 8,589 4,544 4,045 Iqbal

2008 5,754 2,302 3,452 Ali

2006 813 430 383 Yasir

2007 16,027 6,411 9,616 Usman

2006 6,874 3,637 3,237 Usman

2005 10,545 4,218 6,327 Usman

2006 4,027 2,131 1,896 Omer

2009 934 374 560 Iqbal

2007 16,326 8,638 7,688 Omer

2009 5,968 2,387 3,581 Usman

2006 3,840 2,032 1,808 Danial

2007 4,484 1,794 2,690 Danial

2006 6,317 3,342 2,975 Danial

2005 10,785 4,314 6,471 Usman

2004 1,961 1,038 923 Yasir

2005 9,765 3,906 5,859 Yasir

2005 11,304 5,981 5,323 Ali

2006 11,826 4,730 7,096 Usman2008 2,784 1,473 1,311 Iqbal

2005 2,322 929 1,393 Ali

2009 5,124 2,711 2,413 Omer

2008 6,472 2,589 3,883 Iqbal

2004 4,670 2,471 2,199 Yasir

2006 7,787 3,115 4,672 Yasir

2008 10,352 5,477 4,875 Yasir

2009 6,085 2,434 3,651 Ali

2008 6,432 3,403 3,029 Iqbal

2008 5,003 2,001 3,002 Omer

2009 1,372 726 646 Usman2007 3,013 1,205 1,808 Iqbal

2006 11,817 6,252 5,565 Danial

2007 6,450 2,580 3,870 Omer

2008 10,874 5,753 5,121 Usman

2006 4,778 1,911 2,867 Omer

2009 4,312 2,281 2,031 Danial

2009 15,358 6,143 9,215 Usman

2008 4,352 2,303 2,049 Usman


48/72

2004 15,905 6,362 9,543 Usman

2005 10,283 5,441 4,842 Iqbal

2005 16,052 6,421 9,631 Danial

2005 5,945 3,146 2,799 Iqbal

2007 5,220 2,088 3,132 Iqbal

2005 1,869 989 880 Ali

2007 6,229 2,492 3,737 Usman

2004 6,563 3,472 3,091 Usman

2008 4,692 1,877 2,815 Yasir

2008 983 520 463 Ali

2006 10,220 4,088 6,132 Omer

2007 15,669 8,290 7,379 Danial

2009 13,088 5,235 7,853 Yasir

2009 5,816 3,077 2,739 Danial

2006 7,475 2,990 4,485 Usman

2009 10,348 5,475 4,873 Omer

2009 862 345 517 Ali

2005 14,861 7,863 6,998 Usman

2008 6,752 2,701 4,051 Danial2007 11,193 5,922 5,271 Yasir

2006 2,428 971 1,457 Yasir

2008 11,122 5,885 5,237 Iqbal

2007 2,624 1,050 1,574 Danial

2004 5,000 2,646 2,354 Omer

2005 12,504 5,002 7,502 Yasir

2009 7,882 4,170 3,712 Iqbal

2007 11,473 4,589 6,884 Usman

2008 926 490 436 Danial

2006 16,346 6,538 9,808 Omer

2007 11,773 6,229 5,544 Omer2009 16,760 6,704 10,056 Omer

2004 866 458 408 Ali

2009 13,599 5,440 8,159 Usman

2009 13,724 7,261 6,463 Yasir

2009 2,245 898 1,347 Yasir

2008 3,233 1,711 1,522 Omer

2004 1,413 565 848 Ali

2008 5,323 2,816 2,507 Yasir

2005 1,927 771 1,156 Yasir

2009 4,432 2,345 2,087 Yasir

2006 6,437 2,575 3,862 Yasir2006 15,373 8,134 7,239 Iqbal

2006 13,446 5,378 8,068 Danial

2006 10,343 5,472 4,871 Ali

2007 905 362 543 Omer

2008 11,016 5,829 5,187 Usman

2009 11,238 4,495 6,743 Yasir

2005 9,512 5,033 4,479 Iqbal

2007 10,250 4,100 6,150 Omer


49/72

2005 5,149 2,724 2,425 Yasir

2007 1,141 456 685 Iqbal

2005 6,788 3,592 3,196 Yasir

2008 1,970 788 1,182 Yasir

2006 3,163 1,674 1,489 Iqbal

2006 6,861 2,744 4,117 Ali

2005 12,837 6,792 6,045 Omer

2009 16,760 6,704 10,056 Ali

2007 13,032 6,895 6,137 Danial

2008 7,271 2,908 4,363 Iqbal

2006 5,211 2,757 2,454 Usman

2004 6,847 2,739 4,108 Iqbal

2006 3,219 1,703 1,516 Omer

2008 6,570 2,628 3,942 Usman

2005 15,941 8,434 7,507 Ali

2005 2,961 1,184 1,777 Danial

2007 14,127 7,475 6,652 Iqbal

2006 16,013 6,405 9,608 Iqbal

2005 4,308 2,279 2,029 Yasir2009 5,754 2,302 3,452 Yasir

2008 3,858 2,041 1,817 Omer

2005 6,411 2,564 3,847 Iqbal

2005 14,424 7,632 6,792 Yasir

2009 1,197 479 718 Danial

2006 3,015 1,595 1,420 Iqbal

2008 11,608 4,643 6,965 Iqbal

2009 5,612 2,969 2,643 Yasir

2007 16,534 6,614 9,920 Usman

2009 9,857 5,215 4,642 Yasir

2004 6,641 2,656 3,985 Usman2006 14,361 7,598 6,763 Omer

2004 13,806 5,522 8,284 Iqbal

2007 2,717 1,438 1,279 Iqbal

2007 15,064 6,026 9,038 Ali

2006 4,094 2,166 1,928 Iqbal

2006 2,842 1,137 1,705 Yasir

2006 6,244 3,304 2,940 Iqbal

2007 10,514 4,206 6,308 Ali

2009 5,769 3,052 2,717 Usman

2006 15,321 6,128 9,193 Ali

2005 7,338 3,883 3,455 Omer2007 14,592 5,837 8,755 Usman

2004 5,795 3,066 2,729 Usman

2005 8,039 3,216 4,823 Iqbal

2008 13,458 7,121 6,337 Omer

2009 5,844 2,338 3,506 Usman

2008 915 484 431 Omer

2007 6,447 2,579 3,868 Yasir

2009 1,936 1,024 912 Iqbal


50/72

2006 12,213 4,885 7,328 Danial

2007 1,352 715 637 Danial

2009 6,849 2,740 4,109 Ali

2005 15,193 8,039 7,154 Omer

2006 4,780 1,912 2,868 Iqbal

2007 5,609 2,968 2,641 Ali

2005 2,622 1,049 1,573 Omer

2004 3,964 2,097 1,867 Yasir

2007 2,888 1,155 1,733 Iqbal

2004 16,878 8,930 7,948 Yasir

2004 11,891 4,756 7,135 Omer

2007 8,998 4,761 4,237 Yasir

2004 12,017 4,807 7,210 Usman

2004 1,914 1,013 901 Iqbal

2008 5,204 2,082 3,122 Ali

2009 16,362 8,657 7,705 Usman

2008 9,570 3,828 5,742 Danial

2005 3,069 1,624 1,445 Usman

2007 5,526 2,210 3,316 Iqbal2006 1,288 681 607 Iqbal

2007 5,282 2,113 3,169 Usman

2006 5,069 2,682 2,387 Usman

2007 7,225 2,890 4,335 Usman

2009 14,746 7,802 6,944 Iqbal

2006 15,400 6,160 9,240 Yasir

2008 6,522 3,451 3,071 Danial

2006 15,309 6,124 9,185 Usman

2006 6,565 3,474 3,091 Yasir

2009 6,802 2,721 4,081 Yasir

2004 4,140 2,190 1,950 Yasir2005 994 398 596 Usman

2005 1,601 847 754 Omer

2005 3,311 1,324 1,987 Ali

2005 2,212 1,170 1,042 Yasir

2004 8,364 3,346 5,018 Usman

2007 6,531 3,456 3,075 Ali

2004 4,769 1,908 2,861 Yasir

2006 1,527 808 719 Yasir

2009 8,818 3,527 5,291 Omer

2004 14,767 7,813 6,954 Ali

2004 5,124 2,050 3,074 Omer2006 4,190 2,217 1,973 Iqbal

2005 9,431 3,772 5,659 Ali

2008 12,073 6,388 5,685 Omer

2004 12,715 5,086 7,629 Omer

2004 5,222 2,763 2,459 Omer

2005 5,481 2,192 3,289 Omer

2004 3,196 1,691 1,505 Omer

2007 2,504 1,002 1,502 Ali


51/72

2009 5,060 2,677 2,383 Usman

2009 13,143 5,257 7,886 Danial

2005 11,236 5,945 5,291 Ali

2005 2,421 968 1,453 Usman

2004 1,377 729 648 Usman

2008 6,264 2,506 3,758 Usman

2005 8,256 4,368 3,888 Omer

2005 14,859 5,944 8,915 Ali

2005 9,870 5,222 4,648 Iqbal

2005 3,313 1,325 1,988 Iqbal

2009 5,765 3,050 2,715 Omer

2006 1,162 465 697 Iqbal

2009 6,983 3,695 3,288 Iqbal

2004 16,043 6,417 9,626 Danial

2007 9,106 4,818 4,288 Yasir

2004 1,373 549 824 Usman

2006 3,867 2,046 1,821 Iqbal

2008 1,776 710 1,066 Yasir

2005 3,407 1,803 1,604 Omer2008 13,287 5,315 7,972 Yasir

2004 8,473 4,483 3,990 Danial

2009 1,986 794 1,192 Usman

2005 10,688 5,655 5,033 Omer

2006 1,884 754 1,130 Iqbal

2005 6,645 3,516 3,129 Yasir

2008 5,968 2,387 3,581 Danial

2004 1,942 1,028 914 Danial

2006 15,014 6,006 9,008 Danial

2005 7,027 3,718 3,309 Ali

2006 6,247 2,499 3,748 Usman2005 4,192 2,218 1,974 Yasir

2005 16,129 6,452 9,677 Danial

2006 2,537 1,342 1,195 Danial

2004 6,335 2,534 3,801 Iqbal

2004 3,998 2,115 1,883 Iqbal

2004 7,569 3,028 4,541 Iqbal

2008 2,742 1,451 1,291 Usman

2006 6,034 2,414 3,620 Danial

2009 5,756 3,046 2,710 Ali

2004 11,280 4,512 6,768 Yasir

2005 15,610 8,259 7,351 Iqbal2007 7,226 2,890 4,336 Omer

2007 6,387 3,379 3,008 Danial

2004 15,425 6,170 9,255 Usman

2009 15,010 7,942 7,068 Usman

2009 12,373 4,949 7,424 Iqbal

2007 13,230 7,000 6,230 Yasir

2009 4,355 1,742 2,613 Danial

2007 5,936 3,141 2,795 Iqbal


52/72

2009 6,035 2,414 3,621 Danial

2008 1,466 776 690 Iqbal

2006 9,592 3,837 5,755 Danial

2008 5,328 2,819 2,509 Yasir

2009 3,650 1,460 2,190 Danial

2008 4,094 2,166 1,928 Ali

2009 16,616 6,646 9,970 Ali

2008 1,544 817 727 Iqbal

2007 15,193 6,077 9,116 Yasir

2004 3,771 1,995 1,776 Danial

2005 1,367 547 820 Yasir

2009 14,480 7,661 6,819 Iqbal

2008 2,888 1,155 1,733 Iqbal

2006 14,328 7,581 6,747 Iqbal

2009 6,654 2,662 3,992 Ali

2004 4,593 2,430 2,163 Danial

2004 4,558 1,823 2,735 Usman

2008 11,657 6,168 5,489 Omer

2007 10,675 4,270 6,405 Yasir2006 15,596 8,252 7,344 Omer

2006 5,689 2,276 3,413 Usman

2005 7,921 4,191 3,730 Ali

2005 4,139 1,656 2,483 Iqbal

2007 14,266 7,548 6,718 Usman

2009 2,059 824 1,235 Danial

2004 5,060 2,677 2,383 Usman

2008 1,213 485 728 Usman

2009 5,327 2,819 2,508 Ali

2006 6,450 2,580 3,870 Yasir

2005 10,596 5,606 4,990 Omer2006 15,966 6,386 9,580 Iqbal

2006 8,177 4,326 3,851 Yasir

2004 4,014 1,606 2,408 Omer

2006 2,250 1,190 1,060 Usman

2007 15,476 6,190 9,286 Iqbal

2007 10,166 5,379 4,787 Iqbal

2004 2,461 984 1,477 Omer

2009 5,557 2,940 2,617 Iqbal

2008 1,387 555 832 Yasir

2005 5,647 2,988 2,659 Yasir

2007 3,436 1,374 2,062 Iqbal2009 11,717 6,199 5,518 Ali

2004 6,686 2,674 4,012 Yasir

2007 3,713 1,965 1,748 Ali

2008 11,878 4,751 7,127 Yasir

2008 11,601 6,138 5,463 Danial

2008 3,700 1,480 2,220 Omer

2007 6,603 3,494 3,109 Iqbal

2004 16,599 6,640 9,959 Usman


53/72

2009 1,976 1,046 930 Yasir

2004 5,785 2,314 3,471 Danial

2007 5,491 2,905 2,586 Iqbal

2006 6,138 2,455 3,683 Danial

2004 12,901 6,826 6,075 Iqbal

2006 8,117 3,247 4,870 Yasir

2009 755 399 356 Usman

2007 2,565 1,026 1,539 Ali

2004 16,385 8,669 7,716 Omer

2004 2,477 991 1,486 Omer

2004 4,244 2,246 1,998 Omer

2005 12,998 5,199 7,799 Ali

2005 3,654 1,933 1,721 Usman

2008 3,214 1,286 1,928 Ali

2008 974 515 459 Yasir

2007 5,982 2,393 3,589 Ali

2008 4,663 2,467 2,196 Danial

Total 2,320,766 1,073,540 1,247,226


54/72

Use sumifs to Solve this

Sales Summary

Year/SalesRep Iqbal Yasir Ali Danial Omer Usman

2004 70,055 54,348 17,046 40,607 72,729 105,731

2005 73,122 74,928 98,219 44,534 78,019 46,329

2006 97,462 70,565 38,067 93,763 88,601 65,950

2007 81,740 74,842 52,482 43,548 52,930 98,155

2008 51,587 56,967 36,211 46,002 53,897 43,031

2009 73,480 74,728 76,626 40,567 46,815 87,083

COGS Summary


2004

2005

2006

2007

2008

2009

Profit


2004

2005

2006

2007

2008

2009


55/72

Year Sales COGS Profit SalesRep

2006 2,095 838 1,257 Iqbal

2006 4,022 2,128 1,894 Yasir

2006 5,542 2,217 3,325 Ali

2007 6,527 3,453 3,074 Usman

2005 9,392 3,757 5,635 Danial

2004 16,685 8,828 7,857 Iqbal

2004 6,054 2,422 3,632 Usman

2006 6,815 3,606 3,209 Danial

2006 14,894 5,958 8,936 Omer

2008 16,962 8,975 7,987 Ali

2006 5,160 2,064 3,096 Omer

2009 8,589 4,544 4,045 Iqbal

2008 5,754 2,302 3,452 Ali

2006 813 430 383 Yasir

2007 16,027 6,411 9,616 Usman

2006 6,874 3,637 3,237 Usman

2005 10,545 4,218 6,327 Usman

2006 4,027 2,131 1,896 Omer

2009 934 374 560 Iqbal

2007 16,326 8,638 7,688 Omer

2009 5,968 2,387 3,581 Usman

2006 3,840 2,032 1,808 Danial

2007 4,484 1,794 2,690 Danial

2006 6,317 3,342 2,975 Danial

2005 10,785 4,314 6,471 Usman

2004 1,961 1,038 923 Yasir

2005 9,765 3,906 5,859 Yasir

2005 11,304 5,981 5,323 Ali

2006 11,826 4,730 7,096 Usman2008 2,784 1,473 1,311 Iqbal

2005 2,322 929 1,393 Ali

2009 5,124 2,711 2,413 Omer

2008 6,472 2,589 3,883 Iqbal

2004 4,670 2,471 2,199 Yasir

2006 7,787 3,115 4,672 Yasir

2008 10,352 5,477 4,875 Yasir

2009 6,085 2,434 3,651 Ali

2008 6,432 3,403 3,029 Iqbal

2008 5,003 2,001 3,002 Omer

2009 1,372 726 646 Usman2007 3,013 1,205 1,808 Iqbal

2006 11,817 6,252 5,565 Danial

2007 6,450 2,580 3,870 Omer

2008 10,874 5,753 5,121 Usman

2006 4,778 1,911 2,867 Omer

2009 4,312 2,281 2,031 Danial

2009 15,358 6,143 9,215 Usman

2008 4,352 2,303 2,049 Usman


56/72

2004 15,905 6,362 9,543 Usman

2005 10,283 5,441 4,842 Iqbal

2005 16,052 6,421 9,631 Danial

2005 5,945 3,146 2,799 Iqbal

2007 5,220 2,088 3,132 Iqbal

2005 1,869 989 880 Ali

2007 6,229 2,492 3,737 Usman

2004 6,563 3,472 3,091 Usman

2008 4,692 1,877 2,815 Yasir

2008 983 520 463 Ali

2006 10,220 4,088 6,132 Omer

2007 15,669 8,290 7,379 Danial

2009 13,088 5,235 7,853 Yasir

2009 5,816 3,077 2,739 Danial

2006 7,475 2,990 4,485 Usman

2009 10,348 5,475 4,873 Omer

2009 862 345 517 Ali

2005 14,861 7,863 6,998 Usman

2008 6,752 2,701 4,051 Danial2007 11,193 5,922 5,271 Yasir

2006 2,428 971 1,457 Yasir

2008 11,122 5,885 5,237 Iqbal

2007 2,624 1,050 1,574 Danial

2004 5,000 2,646 2,354 Omer

2005 12,504 5,002 7,502 Yasir

2009 7,882 4,170 3,712 Iqbal

2007 11,473 4,589 6,884 Usman

2008 926 490 436 Danial

2006 16,346 6,538 9,808 Omer

2007 11,773 6,229 5,544 Omer2009 16,760 6,704 10,056 Omer

2004 866 458 408 Ali

2009 13,599 5,440 8,159 Usman

2009 13,724 7,261 6,463 Yasir

2009 2,245 898 1,347 Yasir

2008 3,233 1,711 1,522 Omer

2004 1,413 565 848 Ali

2008 5,323 2,816 2,507 Yasir

2005 1,927 771 1,156 Yasir

2009 4,432 2,345 2,087 Yasir

2006 6,437 2,575 3,862 Yasir2006 15,373 8,134 7,239 Iqbal

2006 13,446 5,378 8,068 Danial

2006 10,343 5,472 4,871 Ali

2007 905 362 543 Omer

2008 11,016 5,829 5,187 Usman

2009 11,238 4,495 6,743 Yasir

2005 9,512 5,033 4,479 Iqbal

2007 10,250 4,100 6,150 Omer


57/72

2005 5,149 2,724 2,425 Yasir

2007 1,141 456 685 Iqbal

2005 6,788 3,592 3,196 Yasir

2008 1,970 788 1,182 Yasir

2006 3,163 1,674 1,489 Iqbal

2006 6,861 2,744 4,117 Ali

2005 12,837 6,792 6,045 Omer

2009 16,760 6,704 10,056 Ali

2007 13,032 6,895 6,137 Danial

2008 7,271 2,908 4,363 Iqbal

2006 5,211 2,757 2,454 Usman

2004 6,847 2,739 4,108 Iqbal

2006 3,219 1,703 1,516 Omer

2008 6,570 2,628 3,942 Usman

2005 15,941 8,434 7,507 Ali

2005 2,961 1,184 1,777 Danial

2007 14,127 7,475 6,652 Iqbal

2006 16,013 6,405 9,608 Iqbal

2005 4,308 2,279 2,029 Yasir2009 5,754 2,302 3,452 Yasir

2008 3,858 2,041 1,817 Omer

2005 6,411 2,564 3,847 Iqbal

2005 14,424 7,632 6,792 Yasir

2009 1,197 479 718 Danial

2006 3,015 1,595 1,420 Iqbal

2008 11,608 4,643 6,965 Iqbal

2009 5,612 2,969 2,643 Yasir

2007 16,534 6,614 9,920 Usman

2009 9,857 5,215 4,642 Yasir

2004 6,641 2,656 3,985 Usman2006 14,361 7,598 6,763 Omer

2004 13,806 5,522 8,284 Iqbal

2007 2,717 1,438 1,279 Iqbal

2007 15,064 6,026 9,038 Ali

2006 4,094 2,166 1,928 Iqbal

2006 2,842 1,137 1,705 Yasir

2006 6,244 3,304 2,940 Iqbal

2007 10,514 4,206 6,308 Ali

2009 5,769 3,052 2,717 Usman

2006 15,321 6,128 9,193 Ali

2005 7,338 3,883 3,455 Omer2007 14,592 5,837 8,755 Usman

2004 5,795 3,066 2,729 Usman

2005 8,039 3,216 4,823 Iqbal

2008 13,458 7,121 6,337 Omer

2009 5,844 2,338 3,506 Usman

2008 915 484 431 Omer

2007 6,447 2,579 3,868 Yasir

2009 1,936 1,024 912 Iqbal


58/72

2006 12,213 4,885 7,328 Danial

2007 1,352 715 637 Danial

2009 6,849 2,740 4,109 Ali

2005 15,193 8,039 7,154 Omer

2006 4,780 1,912 2,868 Iqbal

2007 5,609 2,968 2,641 Ali

2005 2,622 1,049 1,573 Omer

2004 3,964 2,097 1,867 Yasir

2007 2,888 1,155 1,733 Iqbal

2004 16,878 8,930 7,948 Yasir

2004 11,891 4,756 7,135 Omer

2007 8,998 4,761 4,237 Yasir

2004 12,017 4,807 7,210 Usman

2004 1,914 1,013 901 Iqbal

2008 5,204 2,082 3,122 Ali

2009 16,362 8,657 7,705 Usman

2008 9,570 3,828 5,742 Danial

2005 3,069 1,624 1,445 Usman

2007 5,526 2,210 3,316 Iqbal2006 1,288 681 607 Iqbal

2007 5,282 2,113 3,169 Usman

2006 5,069 2,682 2,387 Usman

2007 7,225 2,890 4,335 Usman

2009 14,746 7,802 6,944 Iqbal

2006 15,400 6,160 9,240 Yasir

2008 6,522 3,451 3,071 Danial

2006 15,309 6,124 9,185 Usman

2006 6,565 3,474 3,091 Yasir

2009 6,802 2,721 4,081 Yasir

2004 4,140 2,190 1,950 Yasir2005 994 398 596 Usman

2005 1,601 847 754 Omer

2005 3,311 1,324 1,987 Ali

2005 2,212 1,170 1,042 Yasir

2004 8,364 3,346 5,018 Usman

2007 6,531 3,456 3,075 Ali

2004 4,769 1,908 2,861 Yasir

2006 1,527 808 719 Yasir

2009 8,818 3,527 5,291 Omer

2004 14,767 7,813 6,954 Ali

2004 5,124 2,050 3,074 Omer2006 4,190 2,217 1,973 Iqbal

2005 9,431 3,772 5,659 Ali

2008 12,073 6,388 5,685 Omer

2004 12,715 5,086 7,629 Omer

2004 5,222 2,763 2,459 Omer

2005 5,481 2,192 3,289 Omer

2004 3,196 1,691 1,505 Omer

2007 2,504 1,002 1,502 Ali


59/72

2009 5,060 2,677 2,383 Usman

2009 13,143 5,257 7,886 Danial

2005 11,236 5,945 5,291 Ali

2005 2,421 968 1,453 Usman

2004 1,377 729 648 Usman

2008 6,264 2,506 3,758 Usman

2005 8,256 4,368 3,888 Omer

2005 14,859 5,944 8,915 Ali

2005 9,870 5,222 4,648 Iqbal

2005 3,313 1,325 1,988 Iqbal

2009 5,765 3,050 2,715 Omer

2006 1,162 465 697 Iqbal

2009 6,983 3,695 3,288 Iqbal

2004 16,043 6,417 9,626 Danial

2007 9,106 4,818 4,288 Yasir

2004 1,373 549 824 Usman

2006 3,867 2,046 1,821 Iqbal

2008 1,776 710 1,066 Yasir

2005 3,407 1,803 1,604 Omer2008 13,287 5,315 7,972 Yasir

2004 8,473 4,483 3,990 Danial

2009 1,986 794 1,192 Usman

2005 10,688 5,655 5,033 Omer

2006 1,884 754 1,130 Iqbal

2005 6,645 3,516 3,129 Yasir

2008 5,968 2,387 3,581 Danial

2004 1,942 1,028 914 Danial

2006 15,014 6,006 9,008 Danial

2005 7,027 3,718 3,309 Ali

2006 6,247 2,499 3,748 Usman2005 4,192 2,218 1,974 Yasir

2005 16,129 6,452 9,677 Danial

2006 2,537 1,342 1,195 Danial

2004 6,335 2,534 3,801 Iqbal

2004 3,998 2,115 1,883 Iqbal

2004 7,569 3,028 4,541 Iqbal

2008 2,742 1,451 1,291 Usman

2006 6,034 2,414 3,620 Danial

2009 5,756 3,046 2,710 Ali

2004 11,280 4,512 6,768 Yasir

2005 15,610 8,259 7,351 Iqbal2007 7,226 2,890 4,336 Omer

2007 6,387 3,379 3,008 Danial

2004 15,425 6,170 9,255 Usman

2009 15,010 7,942 7,068 Usman

2009 12,373 4,949 7,424 Iqbal

2007 13,230 7,000 6,230 Yasir

2009 4,355 1,742 2,613 Danial

2007 5,936 3,141 2,795 Iqbal


60/72

2009 6,035 2,414 3,621 Danial

2008 1,466 776 690 Iqbal

2006 9,592 3,837 5,755 Danial

2008 5,328 2,819 2,509 Yasir

2009 3,650 1,460 2,190 Danial

2008 4,094 2,166 1,928 Ali

2009 16,616 6,646 9,970 Ali

2008 1,544 817 727 Iqbal

2007 15,193 6,077 9,116 Yasir

2004 3,771 1,995 1,776 Danial

2005 1,367 547 820 Yasir

2009 14,480 7,661 6,819 Iqbal

2008 2,888 1,155 1,733 Iqbal

2006 14,328 7,581 6,747 Iqbal

2009 6,654 2,662 3,992 Ali

2004 4,593 2,430 2,163 Danial

2004 4,558 1,823 2,735 Usman

2008 11,657 6,168 5,489 Omer

2007 10,675 4,270 6,405 Yasir2006 15,596 8,252 7,344 Omer

2006 5,689 2,276 3,413 Usman

2005 7,921 4,191 3,730 Ali

2005 4,139 1,656 2,483 Iqbal

2007 14,266 7,548 6,718 Usman

2009 2,059 824 1,235 Danial

2004 5,060 2,677 2,383 Usman

2008 1,213 485 728 Usman

2009 5,327 2,819 2,508 Ali

2006 6,450 2,580 3,870 Yasir

2005 10,596 5,606 4,990 Omer2006 15,966 6,386 9,580 Iqbal

2006 8,177 4,326 3,851 Yasir

2004 4,014 1,606 2,408 Omer

2006 2,250 1,190 1,060 Usman

2007 15,476 6,190 9,286 Iqbal

2007 10,166 5,379 4,787 Iqbal

2004 2,461 984 1,477 Omer

2009 5,557 2,940 2,617 Iqbal

2008 1,387 555 832 Yasir

2005 5,647 2,988 2,659 Yasir

2007 3,436 1,374 2,062 Iqbal2009 11,717 6,199 5,518 Ali

2004 6,686 2,674 4,012 Yasir

2007 3,713 1,965 1,748 Ali

2008 11,878 4,751 7,127 Yasir

2008 11,601 6,138 5,463 Danial

2008 3,700 1,480 2,220 Omer

2007 6,603 3,494 3,109 Iqbal

2004 16,599 6,640 9,959 Usman


61/72

2009 1,976 1,046 930 Yasir

2004 5,785 2,314 3,471 Danial

2007 5,491 2,905 2,586 Iqbal

2006 6,138 2,455 3,683 Danial

2004 12,901 6,826 6,075 Iqbal

2006 8,117 3,247 4,870 Yasir

2009 755 399 356 Usman

2007 2,565 1,026 1,539 Ali

2004 16,385 8,669 7,716 Omer

2004 2,477 991 1,486 Omer

2004 4,244 2,246 1,998 Omer

2005 12,998 5,199 7,799 Ali

2005 3,654 1,933 1,721 Usman

2008 3,214 1,286 1,928 Ali

2008 974 515 459 Yasir

2007 5,982 2,393 3,589 Ali

2008 4,663 2,467 2,196 Danial

Total 2,320,766 1,073,540 1,247,226


62/72

Use averageif to Solve this

Averages

Year Sales COGS Profit

2004 7,357 3,350 4,008

2005 7,833 3,736 4,097

2006 7,573 3,406 4,168

2007 8,239 3,793 4,446

2008 6,121 2,902 3,219

2009 7,679 3,554 4,125


63/72

Question 1:

*****CMPAK-Limited 7 Mauve Area, G-9/4, Islamabad Zip Code 755

*****TeraData 7 Mauve Area, G-9/4, Islamabad Zip Code 75500

Requirement:

1- Make cell borders visibile again in this worksheet without using Font options

2- Remove all unwanted spaces with one function

3- Remove all the * with help of function

4- separate zip code with help of function

5-separate name of company with help of function

6- tell me the number of charcters used in original text in both cases


CMPAK-Limited 7 Mauve Area, G-9/4, Islamabad Zip Code 75500


64/72

0


65/72

First Name Family Name Join two Names Separate First Name

Tina Ali Tina Ali Tina

Tom Winney Tom Winney Tom

Bob Smith Bob Smith Bob

Tina Ward Tina Ward Tina

Sue Sceana Sue Sceana Sue

Franz Nano Franz Nano Franz

Sue Nano Sue Nano Sue

Bob Bothell Bob Bothell Bob

Miki Nano Miki Nano Miki

Miki Winney Miki Winney Miki

Isaac Sceana Isaac Sceana Isaac

Gigi Mitchel Gigi Mitchel Gigi

Tina Ali Tina Ali Tina

Gigi Nano Gigi Nano Gigi

14

Question 1:


*****TeraData 7 Mauve Area, G-9/4, Islamabad Zip Code 75500

Requirement:

1- Make cell borders visibile again in this worksheet without using Font options

2- Remove all unwanted spaces with one function

3- Remove all the * with help of function

4- separate zip code with help of function5-separate name of company with help of function

6- tell me the number of charcters used in original text in both cases


CMPAK-Limited 7 Mauve Area, G-9/4, Islamabad Zip Code 75500


66/72

Separate Family Name Length of Name

Ali 8

Winney 10

Smith 9

Ward 9

Sceana 10

Nano 10

Nano 8

Bothell 11

Nano 9

Winney 11

Sceana 12

Mitchel 12

Ali 8

Nano 9


67/72

Make Month No Price

Toyota June 8 11,500,000

Toyota June 14 10,000,000

Toyota May 7 9,500,000

Suzuki June 6 6,300,000

Suzuki May 12 9,200,000

Corolla June 11 12,320,000

Corolla May 15 13,420,000

Corolla June 10 11,475,000

Month Make No Revenue

June Toyota 22 232,000,000

June Suzuki 6 37,800,000

June Corolla 21 250,270,000

Month Make No Revenue

May Toyota 7 66500000

May Suzuki 12 110400000

May Corolla 15 201300000


68/72

Year 2009 Question:

Sales Rep Nadia

Count 3 Use sum product to count number

Add 7 Use sum product to sum total numb

Date SalesRep # Phone Calls

1/2/2009 Hira 5

2/1/2009 Nadia 2

1/15/2008 Hira 5

2/1/2009 Hira 3

2/15/2009 Nadia 2

12/30/2008 Nadia 2

2/15/2009 Hira 3

1/28/2009 Nadia 3

Month Feb

Year 2009 Question:

Sales Rep Nadia

Count 2 Use sum product to count number

Add 4 Use sum product to sum total numb


69/72

f times nadia made calls in 2009

er of calls nadia made calls in 2009

f times nadia made calls in Feb 2009

er of calls nadia made calls in Feb 2009


70/72

ValuesRanking Position

High to Low

7 4

4 5

25 1

8 3

16 2

ValuesRanking Position

Low to High

7 2

4 1

25 5

8 3

16 4

Athlete Time Race Position

Ali 1:30 AM 4

Usman 1:45 AM 6

Kamal 1:02 AM 1

Irfhan 1:36 AM 5

Faraz 1:27 AM 3

Omer 1:03 AM 2


71/72

Match w 0 (Exact Match)

Value to look for Hira Tuba

Position 4 6

List 1 Ali Iqbal Nasir

MATCH with 1 or Empty & Sorted Ascending

Value to look for 1000 650 20000

Position 4 3 7

List 1

0

100

500

1000

2500

5000

10000

MATCH with -1 & Sorted Descending

Value to look for 1000 -100 650

Position 4 7 4

List 1

10000

5000

2500

1000

500

100

0


72/72

Hira Nadia Tuba Yasir

20000

#N/A

1-exercises- training.xlsx

Documents