advance excel.xls

7/29/2019 Advance Excel.xls

1/68

Salesman Area Product Total Sales

Himanshu Chembur Microwave 4Himanshu Dadar VCR 5

Deepak Dadar I-Pod 3

Deepak Vashi I-Pod 6

Ritesh Bandra Microwave 7

Pankaj Juhu Mobile 4

Bhushan Kurla Mobile 5

Sachin Chembur VCR 6

Hasan Vashi I-Pod 7

Krunal Panvel I-Pod 8

Krunal Andheri VCR 5


2/68

10

Write a External reference formula to access the data from

External Reference 230

Using paste links to refer a cell 1240

Copying Range 45 23 45

45 23 45

78 23 45

34 23 45

666 23 45

344 23 45

533.4 23 45

628.0857143 23 45

722.7714286 23 45817.4571429 23 45

912.1428571 23 45

1006.828571 23 45

1101.514286 23 45

1196.2 23 45

1290.885714 23 45

1385.571429 23 45

1480.257143 23 45

1574.942857 23 45

1669.628571 23 45

1764.314286 23 451859 23 45

1953.685714 23 45

2048.371429 23 45

2143.057143 23 45

2237.742857 23 45

2332.428571 23 45

2427.114286 23 45

2521.8 23 45


3/68

2616.485714 23 45

2711.171429 23 45

2805.857143 23 45

2900.542857 23 45

2995.228571 23 45

3089.914286 23 45

3184.6 23 453279.285714 23 45

3373.971429 23 45

3468.657143 23 45

3563.342857 23 45

3658.028571 23 45

3752.714286 23 45

3847.4 23 45

3942.085714 23 45

4036.771429 23 45

4131.457143 23 45

4226.142857 23 45

4320.828571 23 45

4415.514286 23 45

4510.2 23 45

4604.885714 23 45

4699.571429 23 45

4794.257143 23 45

4888.942857 23 45

4983.628571 23 45

5078.314286 23 45

5173 23 455267.685714 23 45

5362.371429 23 45

5457.057143 23 45

5551.742857 23 45

5646.428571 23 45

5741.114286 23 45

5835.8 23 45

5930.485714 23 45

6025.171429 23 45

6119.857143 23 45

6214.542857 23 456309.228571 23 45

6403.914286 23 45

6498.6 23 45

6593.285714 23 45

6687.971429 23 45

6782.657143 23 45

6877.342857 23 45

6972.028571 23 45


4/68

7066.714286 23 45

7161.4 23 45

7256.085714 23 45

7350.771429 23 45

7445.457143 23 45

7540.142857 23 45

7634.828571 23 457729.514286 23 45

7824.2 23 45

7918.885714 23 45

8013.571429 23 45

8108.257143 23 45

8202.942857 23 45

8297.628571 23 45

8392.314286 23 45

8487 23 45

8581.685714 23 45

8676.371429 23 45

8771.057143 23 45

8865.742857 23 45

8960.428571 23 45

9055.114286 23 45

9149.8 23 45

9244.485714 23 45

9339.171429 23 45

9433.857143 23 45

9528.542857 23 45

9623.228571 23 459717.914286 23 45

9812.6 23 45

9907.285714 23 45

10001.97143 23 45

10096.65714 23 45

10191.34286 23 45

10286.02857 23 45

10380.71429 23 45

10475.4 23 45

10570.08571 23 45

10664.77143 23 4510759.45714 23 45

10854.14286 23 45

10948.82857 23 45

11043.51429 23 45

11138.2 23 45

11232.88571 23 45

11327.57143 23 45

11422.25714 23 45


5/68

11516.94286 23 45

11611.62857 23 45

11706.31429 23 45

11801 23 45

11895.68571 23 45

11990.37143 23 45

12085.05714 23 4512179.74286 23 45

12274.42857 23 45

12369.11429 23 45

12463.8 23 45

12558.48571 23 45

12653.17143 23 45

12747.85714 23 45

12842.54286 23 45

12937.22857 23 45

13031.91429 23 45

13126.6 23 45

13221.28571 23 45

13315.97143 23 45

13410.65714 23 45

13505.34286 23 45

13600.02857 23 45

13694.71429 23 45

13789.4 23 45

13884.08571 23 45

13978.77143 23 45

14073.45714 23 4514168.14286 23 45

14262.82857 23 45

14357.51429 23 45

14452.2 23 45

14546.88571 23 45

14641.57143 23 45

14736.25714 23 45

14830.94286 23 45

14925.62857 23 45

15020.31429 23 45

15115 23 4515209.68571 23 45

15304.37143 23 45

15399.05714 23 45

15493.74286 23 45

15588.42857 23 45

15683.11429 23 45

15777.8 23 45

15872.48571 23 45


6/68

15967.17143 23 45

16061.85714 23 45

16156.54286 23 45

16251.22857 23 45

16345.91429 23 45

16440.6 23 45

16535.28571 23 4516629.97143 23 45

16724.65714 23 45

16819.34286 23 45

16914.02857 23 45

17008.71429 23 45

17103.4 23 45

17198.08571 23 45

17292.77143 23 45

17387.45714 23 45

17482.14286 23 45

17576.82857 23 45

17671.51429 23 45

17766.2 23 45

17860.88571 23 45

17955.57143 23 45

18050.25714 23 45

18144.94286 23 45

18239.62857 23 45

18334.31429 23 45

18429 23 45

18523.68571 23 4518618.37143 23 45

18713.05714 23 45

18807.74286 23 45

18902.42857 23 45

18997.11429 23 45

19091.8 23 45

19186.48571 23 45

19281.17143 23 45

19375.85714 23 45

19470.54286 23 45

19565.22857 23 4519659.91429 23 45

19754.6 23 45

19849.28571 23 45

19943.97143 23 45

20038.65714 23 45

20133.34286 23 45

20228.02857 23 45

20322.71429 23 45


7/68

20417.4 23 45

20512.08571 23 45

20606.77143 23 45

20701.45714 23 45


8/68

ome other workbook for calculation

342 0 44 55 55 55 55 0 22

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4


9/68

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4


10/68

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4


11/68

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4


12/68

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4


13/68

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4 3847.4


14/68

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4


15/68

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4


16/68

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4


17/68

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4


18/68

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.43847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4


19/68

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4

3847.4 3847.4 3847.4 3847.4


20/68

Name Sales Target Result

Alan 1000 5000 Not Achieved N\A Not Ach

Bob 6000 5000 Achieved A Ach

Carol 2000 4000 Not Achieved N\A Not Ach

John 5000 6000 Not Achieved N\A Not Ach

Sam 4500 5000 Not Achieved N\A Not Ach

Daniel 2000 1000 Achieved A Ach

Name Sales Target Bonus

Alan 1000 5000 50 50

Bob 6000 5000 600 600

Carol 2000 4000 100 100

John 5000 6000 250 250

Sam 4500 5000 225 225Daniel 2000 1000 200 200

Name Sales Bonus

Alan 1000 50

Bob 6000 1200

Carol 2000 100

John 5000 1000

Sam 4500 675

Daniel 2000 100

Gender Age Eligibility

F 13 NE

F 25 E

F 15 NE

M 60 E

M 12 NE

M 65 E

What Does It Do?-This function tests a condition.-If the condition is met it is considered to be TRUE.-If the condition is not met it is considered as FALSE.-Depending upon the result, one of two actions will be carried out.

SyntaxIF(logical_test,value_if_true,value_if_false)


21/68

F 18 E


22/68

Student Name English Hindi Marathi Science

Alan 90 50 65 85

Bob 85 80 55 65

Carol 65 65 60 45

John 40 20 58 44

Sam 54 98 90 85

Daniel 60 50 45 40

Salesman Name VCR(QTY) I-Pod(QTY) Mobiles(QTY) Remark

Alan 50 54 80 Applicable

Bob 65 25 50 Applicable

Carol 10 55 85 Applicable

John 58 55 60 Applicable

Sam 40 80 52 Applicable

Daniel 40 45 10 Not Applicable

Use criteria range F1:H2

to find records that

meet ALL THREE criteria

What Does It Do?-Returns TRUE if all its arguments are TRUE-Returns FALSE if one or more argument is FALSE.

Syntax

IF(AND(logical1,logical2, ...))

What Does It Do?-Returns TRUE if any argument is TRUE;-Returns FALSE if all arguments are FALSE.

SyntaxIF(OR(logical1,logical2,...))


23/68

Customer Product Total

MegaMart Cookies >500

Date Customer Product Total

1/1/2003 MegaMart Ice $302.00 Yes

1/3/2003 FoodMart Produce $530.00 Yes1/4/2003 MegaMart Cookies $223.00 Yes

1/11/2003 MegaMart Cookies $363.00 Yes


1/20/2003 MegaMart Milk $191.00 Yes

1/21/2003 FoodMart Bread $684.00 Yes


2/17/2003 MegaMart Bread $380.00 Yes





3/4/2003 FoodMart Bread $561.00 Yes


3/12/2003 FoodMart Produce $746.00 Yes

3/12/2003 MegaMart Ice Cream $752.00 Yes

3/19/2003 MegaMart Produce $399.00 Yes



4/4/2003 FoodMart Milk $275.00 No




24/68

Maths Total Average Remark Grade

90 380 76 PASS Dist

95 380 76 PASS Dist

60 295 59 PASS Second Class

58 220 44 FAIL Fail

60 387 77.4 PASS Dist

40 235 47 PASS Second Class

Applicable

Applicable

Applicable

Applicable

Applicable

Not

Use criteria range J1:L4

to find records that

meet AT LEAST ONE of the criteria

Customer Product Total


25/68

MegaMart

Cookies

>500


26/68

Example-1

Name Jan

Alan 10

Bob 20

Carol 30

David 40

Eric 50

Francis 60

Gail 70

Example-2

RangeToLookIn

Alan

Bob

Carol

DavidEric

Fred

The Feb value for

Type a Name i

What Does It Do?

-This function looks for a piece of information in a list, and then pi

Syntax

-LOOKUP(What To Look For,Range To LookIn,Range ToPick From -The What ToLookF or should be a single item.

-The Range ToLook in can be either horizontal or vertical.

-The RangeToPick From must have the same number of cells in it

Note-Be careful not to include unnecessary heading in the ranges


27/68

Name Sales

Sam 5000

Sony 2500

Maddy 4500

Sales Bonus Raj 5600

1000 500 Rohan 2000

5000 1000 Guddi 6000

8000 1500 Priya 7500

10000 2000 Sonam 1000

Diya 9000

Rohan 2500

Rahul 4550

Amar 2550

Prem 6550

Ritu 10000

Bonus Details

Employe


28/68

Feb Mar

80 97

90 69

100 45

110 51

120 77

130 28

140 73

Eric

120

5 10 15 20 25 30

Carol15

this person is :

in this cell :

RangeToPickFrom

Type a name :Value :

ks an item from a second range of cells.

s the RangeToLookin.

s these will cause errors.


29/68

Bonus

1000

500

500

1000

500

1000

1000

500

1500

500

500

500

1000

2000

Bonus


30/68


31/68

Example-2

Jan 10 20 30 40 50

Feb 80 90 100 110 120

Mar 97 69 45 51 77

Jan

2

10

Example-2

Item Units Unit Cost

Brick 100 2

Brick 2 Wood 200 1

Wood 1 Wood 150 1

Glass 3 Brick 225 2

Wood 50 1

Glass 500 3

Example-3

Date Prod1 Prod2 Prod3 Prod4

2-Feb-06 447 358 358 500

Sales of Product Sold on particular Dates

Type a month to look for :

Which column needs to be picked out :

The result is :

Unit Cost Table

Orders Table

What Does It Do?-This function scans down the row headings at the side of a table to find a speciWhen the item is found, it then scans across to pick a cell entry.

Syntax=VLOOKUP(ItemToFind,RangeToLookIn,ColumnToPickFrom,SortedOrUnsorte-The ItemToFind is a single item specified by the user.-The RangeToLookIn is the range of data with the row headings at the left hand-The ColumnToPickFrom is how far across the table the function should look to-The Sorted/Unsorted is whether the column headings are sorted. TRUE for yes


32/68

13-Feb-06 457 566 455 250

28-Feb-06 547 458 587 640

1-Mar-06 758 785 256 655

5-Mar-06 127 328 200 300

10-Mar-06 569 564 588 458

11-Mar-06 566 300 650 800

1-Apr-06 458 258 400 555

Date Prod1 Prod2 Prod3 Prod4

10-Mar-06 569 564 588 458


33/68

ied item.

)

side.pick from., FALSE for no.


34/68

Example-1

Jan Feb Mar

10 80 97

20 90 69

30 100 45

40 110 51

50 120 77

Feb

4

100

Type a month to look for :

Which row needs to be picked out :

The result is :

What Does It Do?-This function scans across the column headings at the top of a table to find a specifie-When the item is found, it then scans down the column to pick a cell entry.

Syntax

=HLOOKUP(ItemToFind,RangeToLookIn,RowToPickFrom,SortedOrUnsorted)-The Item To Find is a single item specified by the user.-The Range To LookIn is the range of data with the column headings at the top.-The Row To Pick From is how far down the column the function should look to pick f-The Sorted/Unsorted is whether the column headings are sorted. TRUE for yes, FAL


35/68

item.

rom.E for no.


36/68

Following are Different destination available to visit

Accordingly, to travel their, different modes are ava

We need to create a System where used will input t

On the basis of selection, he gets the Fare amount

Destination Rail Road

Agra 500 400

Delhi 750 500

Madras 800 650

Banglore 650 550

Area Mode Amount

Banglore Air 5600


37/68

in India

ilable along with their prices.

e destination city, and select the mode of travel

Air

4500

5500

5000

5600


38/68

Drinks Brands Price

Pepsi 10

Coke 12

Miranda 8

Thums up 6

Fanta 8

Hot Coke

Tea 5

Nestle 6

Horlicks 15

Coffee 50

Given a list of items along with the price tag

Customer can buy any of the given CATEGORY. Hot ORNow we will design the structure in this following ma

Category Brands Qty Price

There will be drop down in Category Column. Hot and

Based on Category Selection the list has to be displaye

You could enter any value for Qty

Price has to be calculated on the rate for the brand pu

Take a ZIP

Cold

Hot


39/68

Category Brands Qty Price

hot Fanta 10 80

Cold.. No idea and that too how much quantityner

cold

d under Brands Column.

chased with the qty bought.


40/68

Amount 45000

Discount 5%

Name Amount Bonus

Alan

Bob

Carol

David

Eric

Francis

Gail


41/68

Count

10 20 30 3

10 0 30 3

10 -20 30 3

10 1-Jan-88 30 3

10 21:30 30 3

10 0.489084 30 3

10 30 2

10 Hello 30 2

10 #DIV/0! 30 2

Entries To Be Counted

What Does It Do?-This function counts the number of numeric entries in a list.-It will ignore blanks, text and errors.

Syntax

=COUNT(Range1,Range2,Range3... through to Range30)


42/68

Count

10 20 30 3

10 0 30 3

10 -20 30 3

10 1-Jan-88 30 3

10 21:30 30 3

10 0.824272 30 3

10 30 2

10 Hello 30 3

10 #DIV/0! 30 3


What Does It Do?-This function counts the number of numeric entries in a list.

Syntax=COUNT(Range1,Range2,Range3... through to Range30)


43/68

Count

10 20 30 0

10 0 30 0

10 -20 30 0

10 1-Jan-88 30 0

10 21:30 30 0

10 0.126659 30 0

10 30 1

10 Hello 30 0

10 #DIV/0! 30 0


What Does It Do?-This function counts the number of blank cells in a range.

Syntax=COUNTBLANK(RangeToTest)


44/68

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

2

3

5

service 2

How many Brake Shoes Have been bought.

How many Tyres have been bought.

How many items cost 100 or above.

Type the name of the item to count.

What Does It Do?-This function counts the number of items which match criteria set by theuser.

Syntax=COUNTIF(RangeOfThingsToBeCounted,CriteriaToBeMatched)


45/68

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

160

325

1000Total of items costing 100 or above.

Total cost of all Tyres bought.

Total cost of all Brakes bought.

What Does It Do?

-This function adds the value of items which match criteria set by

the user.

Syntax

=SUMIF(RangeOfThingsToBeExamined,CriteriaToBeMatched,RangeOfValuesToTotal)


46/68

Name 1 Name 2 Concatenated Text

Alan Jones AlanJones

Bob Williams BobWilliams

Carol Davies CarolDavies

Alan Jones Alan Jones

Bob Williams Williams, Bob

Carol Davies Davies, Carol

What Does It Do?

-This function joins separate pieces of text into one item.

Syntax

=CONCATENATE(Text1,Text2,Text3...Text30)Note - Up to thirty pieces of text can be joined.


47/68

=CONCATENATE(C4,D4) Jones Alan

=CONCATENATE(C5,D5) Williams Bob

=CONCATENATE(C6,D6) Davies Carol

=CONCATENATE(C7," ",D7) Jones Alan

=CONCATENATE(D8,", ",C8) Williams Bob

=CONCATENATE(D9,", ",C9) Davies Carol


48/68

Dirty Text Clean Text

Hello Hello Hello

He llo Hello Hello

Hello Hello Hello

What Does It Do?

-This function removes any nonprintable characters from text.

-These nonprinting characters are often found in data which has been

imported from other systems such as database imports from mainframes.

Syntax=CLEAN(TextToBeCleaned)


49/68

Product Wattage Life Hours Brand Unit Cost

Bulb 200 3000 Horizon 4.50

Neon 100 2000 Horizon 2.00

Spot 60

Other 10 8000 Sunbeam 0.80


Spot 100 unknown Horizon 1.25

Spot 200 3000 Horizon 2.50

Other 25 unknown Sunbeam 0.50

Bulb 200 3000 Sunbeam 5.00

Neon 100 2000 Sunbeam 1.80

Bulb 100 unknown Sunbeam 0.25






Example-1

Brand

Type the brand name : sunbeam

The Average cost of is : 1.24

To calculate the Average cost of a particular Brand of bulb.

What Does It Do?

-This function examines a list of information and produces and average.

Syntax

=DAVERAGE(DatabaseRange,FieldName,CriteriaRange)

***The DatabaseRange is the entire list of information you need to examine, including the field

***The FieldName is the name, or cell, of the values to be averaged, such as "Unit Cost"

***The CriteriaRange is made up of two types of information.

*The first set of information is the name, or names, of the Fields(s) to be used as the basis for

*The second set of information is the actual record, or records, which are to be selected.


50/68

Example-2

Product BrandBulb Horizon

The average of Horizon is : 1.16

Example-3

Product Wattage

Bulb 100

Average of Bulb 100 is : 0.53

Region Product Units Sold Sales (In Lakhs) TargetMumbai DVD 500 26 30

Chennai DVD 800 17 25

Pune MP3 800 40 25

Mumbai Digital Cam 860 25 20

Chennai Digital Cam 400 34 25

Banglore DVD 890 78 90

Banglore Ipod 345 4 6

Chennai CD 1200 12 15

Mumbai DVD 670 22 25Pune Ipod 890 45 50

Mumbai CD 675 70 75

Chennai CD 890 90 75

Banglore MP3 345 17 15

Chennai MP3 1000 20 10

Mumbai Ipod 670 90 100

The average Unit Cost of a particular Product of a particular Brand.

The average Unit Cost of a Bulb equal to a particular Wattage.

Sales Data For April 2009


51/68

Region

Mumbai

Average Sales For Each City 46.6


52/68

BoxQuantity

Boxes InStock

Value OfStock

4 3 54.00

15 2 60.00

0.00

25 6 120.00

40 3 24.00

10 4 50.00

15 0 0.00

10 3 15.00

3 2 30.00

20 5 180.00

10 5 12.50

25 2 10.00

25 0 0.00

30 2 12.00

10 5 40.00

20 5 10.00

names at the top of the columns.

electing the records


53/68


54/68

Product Wattage Life Hours Brand

Bulb 200 3000 Horizon

Neon 100 2000 Horizon

Spot 60

Other 10 8000 Sunbeam


Spot 100 unknown Horizon

Spot 200 3000 Horizon

Other 25 unknown Sunbeam

Bulb 200 3000 Sunbeam

Neon 100 2000 Sunbeam

Bulb 100 unknown Sunbeam

Bulb 10 800 Horizon





Example-1

Brand

Type the brand name : Horizon

The stock value of Horizon is : 248.00

To calculate the total Value Of Stock of a particular Brand

What Does It Do?

-This function examines a list of information and produces the total.

Syntax=DSUM(DatabaseRange,FieldName,CriteriaRange)

***The DatabaseRange is the entire list of information you need to examine, including t

***The FieldName is the name, or cell, of the values to be averaged, such as "Unit Cost"

***The CriteriaRange is made up of two types of information.

*The first set of information is the name, or names, of the Fields(s) to be used as the ba

*The second set of information is the actual record, or records, which are to be selecte


55/68

Example-2

Product Brand

Bulb Sunbeam

Total stock value is : 54.5

Example-3

Region Product Units Sold Sales (In Lakhs)Mumbai DVD 500 26

Chennai DVD 800 17

Pune MP3 800 40

Mumbai Digital Cam 860 25

Chennai Digital Cam 400 34

Banglore DVD 890 78

Banglore Ipod 345 4

Chennai CD 1200 12Mumbai DVD 670 22

Pune Ipod 890 45

Mumbai CD 675 70

Chennai CD 890 90

Banglore MP3 345 17

Chennai MP3 1000 20

Mumbai Ipod 670 90

RegionMumbai

Total Sales for the Mumbai 233

Region Product

Mumbai CD

The total Value Of Stock of a particular Product of a particular



56/68

Mumbai MP3

70Total Sales for CD and MP3 in

Mumbai


57/68

Unit Cost

Box

Quantity

Boxes In

Stock

Value Of

Stock

4.50 4 3 54.00

2.00 15 2 60.00

0.00

0.80 25 6 120.00

0.20 40 3 24.00

1.25 10 4 50.00

2.50 15 0 0.00

0.50 10 3 15.00

5.00 3 2 30.00

1.80 20 5 180.00

0.25 10 5 12.50

0.20 25 2 10.00

0.15 25 0 0.00

0.20 30 2 12.00

0.80 10 5 40.00

0.10 20 5 10.00

f bulb.

he field names at the top of the columns.

sis for selecting the records


58/68

Target30

25

25

20

25

90

6

1525

50

75

75

15

10

100

rand.


59/68


60/68


61/68

Following is the Monthly Sales Data For M/s. Dhuri Pvt. Ltd.

Management requires some information to take necessary d

You are asked to calculate the figures.


Chennai DVD 800 17

Pune MP3 800 40



Banglore DVD 890 78Banglore Ipod 345 4

Chennai CD 1200 12

Mumbai DVD 670 22

Pune Ipod 890 45

Mumbai CD 675 70

Chennai CD 890 90

Banglore MP3 345 17

Chennai MP3 1000 20

Mumbai Ipod 670 90



62/68

ecisions from the growth perspective.

Target30

25

25

20

25

906

15

25

50

75

75

15

10

100


63/68

Following is the Monthly Sales Data For M/s. Dhuri Pvt. Ltd.

Management requires some information to take necessary d

You are asked to calculate the figures.


Chennai DVD 800 17

Pune MP3 800 40



Banglore DVD 890 78Banglore Ipod 345 4

Chennai CD 1200 12

Mumbai DVD 670 22

Pune Ipod 890 45

Mumbai CD 675 70

Chennai CD 890 90

Banglore MP3 345 17

Chennai MP3 1000 20

Mumbai Ipod 670 90



64/68

ecisions from the growth perspective.

Target Region Product Units Sold30 Chennai >=1000

25

25

20

25

906

15

25

50

75

75

15

10

100


65/68

Sales (In Lakhs) Target


66/68

1 2000

1200 800

pa int

Loam Amount 25000

2000

No. of Years 4 48 1600 400

Rate of interest 4%

Monthly Payment564.48 PMT

1($481.14) PPMT

48 ($562.60)

1($83.33) IPMT

48 ($1.88)

Calculates the payment for a loaSyntaxPMT(rate,nper,pv)

**Rate is the interest rate for t**Nper is the total number of**Pv is the present value, or th

Returns the payment on the priinterest rate.

SyntaxPPMT(rate,per,nper,pv)

**Rate is the interest rate for t**Per specifies the period and**Nper is the total number of**Pv is the present value, or th

Returns the payment on the intconstant interest rate.Syntax

IPMT(rate,per,nper,pv)

**Rate is the interest rate for t**Per specifies the period and**Nper is the total number of p**Pv is the present value, or th


67/68

n based on constant payments and a constant interest rate.

he loan.ayments for the loan.

e total amount that a series of future payments is worth now; also known as the principal.

ncipal for a given period for an investment based on periodic, constant payments and a constant

he loan.must be in the range 1 to nper.ayments for the loan.


rest principal for a given period for an investment based on periodic, constant payments and a

he loan.must be in the range 1 to nper.ayments for the loan.



68/68

istext Accept only text

Anubhav

Anubhav

Anubhav

AnubhavAnubhav

Anubhav

advance excel.xls

Documents