Unit – 4 Electronic Spreadsheet (Practical)

Unit – 4 Electronic Spreadsheet

(Practical)

Q1. Create the following worksheet in calc and write the formula for the task given below.

1. Enter the formula in G2 to calculate the total marks scored by Harman

2. Enter the formula in H2 to calculate the average scored by Harman.

3. Enter the formula in B9 to find out the highest score obtained for science.

4. Enter the formula in B10 to find out the lowest score obtained by students in each subject.

5. Enter the formula in B11 to find out the number of students present for each subject.

6. Enter the formula in B12 to find out the average score of each subject.

Ans.

1. =sum(B2 : F2)

2. =average(B2 : F2)

3. =max(C2 : C8)

4. Write formula =min(B2 : B8) in B10 and then drag horizontally till F10.

5. Write formula =count(B2 : B8) in B11 and then drag horizontally till F11.

6. Write formula =average(B2 : B8) in B12 and then drag horizontally till F12.

Q2. Create the following worksheet in calc and write the formula for the task given below.

1. Write formula in D5 to calculate TA of Firoz Khan.

2. Write formula in E3 to calculate DA of Deepak Gautam.

3. Write formula in F8 to calculate HRA of Nikki Khanna.

4. Write formula in G7 to calculate CPF of Meena Kumari.

5. Write formula in C12 to display the highest Basic salary.

6. Write formula in I10 to calculate the gross salary of Vinay Kumar.

7. Write formula in H12 to calculate the average of Gross Salary of all employees.

8. Write formula in B12 to display the total number of employees.

9. Write formula in I11 to calculate average of TA, DA and HRA of Yusuf.

10. Write formula in C13 to calculate the sum of Basic Salary of all employees.

Ans.

1. = 6/100 * C5

2. = 14/100 * C3

3. = 10/100 * C8

4. = 3/100 * C7

5. = max (C2 : C11)

6. = sum (D10 : G10)

7. = average (H2 : H11)

8. = count (B2 : B11)

9. = average (D11 : F11)

10. = sum (C2 : C11)

Q3. Create the following worksheet in calc and write the formula for the task given below.

1. Write formula in H2 to calculate total marks of Amit.

2. Write formula in C12 to calculate minimum marks in Hindi.

3. Write formula in H6 to display lowest marks of Gagan.

4. Write the cell range which has marks of Nikki (of all subjects).

5. Write formula in H7 to calculate percentage of Meena.(Assume all marks are out of 100)

6. Write formula in B12 to calculate total number of students.

7. Write formula in H9 to calculate the 10% of total marks of Tejpal.

8. Write formula in I12 to calculate average marks of Science subject.

9. Write formula in H2 to calculate total marks of subject English, Maths and Biology of Amit.

10. Write formula in H4 to calculate the average of best three marks of Chetna.

Ans.

1. = sum (C2 : G2)

2. = min (C2 : C11)

3. = min (C6 : G6)

4. Cell range is C8 : G8

5. = sum (C7 : G7)/500 * 100

6. = count (B2 : B11)

7. = (C9 + D9 + E9 + F9 + G9) * 10/100

8. = average (E2 : E11)

9. = D2 + F2 + G2

10. = average(C4 + D4 + G4)

Q4. Create the following worksheet in calc and write the formula for the task given below.

1. Write formula in E2 to calculate the Total price (Quantity * Price) of Pencil.

2. Write formula in C7 to calculate total of Price values

3. Write formula in D7 to calculate maximum value of column ‘QTY’.

4. Write formula in C8 to calculate average of Price values.

5. Write formula in C9 to find lowest value of column ‘PRICE’.

6. = C4 * D4 will return ____________

7. Write the cell range of all the numerical values of column ‘QTY’

8. Write the cell address which stores largest/maximum value of column ‘PRICE’.

9. How many values are stored in range C2 : D6?

Ans.

1. = C2 * D2

2. =sum (C2 : C6)

3. = max (D2 : D6)

4. = average (C2 : C6)

5. = min (C2 : C6)

6. 750

7. D2 : D6

8. C5

9. 10

Q5. Write answers for the following queries using functions.

1. Write the formula in E2 to find the total marks scored by HARMAN.

Ans. =sum(B2 : D2)

2. Write the formula in F2 to find the average marks scored by HARMAN.

Ans. =average(B2 : D2)

3. Write the formula in cell B7 to find the highest score in Hindi.

Ans. =max(B2 : B6)

4. Write the formula in cell B8 to find the total number of students who appeared in Hindi?

Ans. =count(B2 : B6)

5. Write the formula in cell B9 to find the lowest score in Hindi.

Ans. =min(B2 : B6)

6. How will you find the lowest score in English and Maths?

Ans. We can find the lowest score in English and Maths by any of the following:

1. Drag the formula from B9 to D9

2. Copy paste the formula from B9 to C9 and D9.

3. Write formula =min(C2 : C6) in C9 and =min(D2 : D6) in D9

7. How will you find the highest score in Hindi, English and Maths?

Ans. We can find the highest score in Hindi, English and Maths by writing the following formula:

for Hindi : =max(B2 : B6)

for English: =max(C2 : C6)

for Maths : =max(D2 : D6)

Q6. Identify the following components of OpenOffice Calc Interface.

Ans. Name of Components are

1. A – Name Box

2. B – Vertical Scroll Bar

3. C – Zoom Control

4. D – Quick Access Tool Bar

5. E – Status Bar

6. F – Formula Bar

Q7. Answer the questions based on the following worksheet.

1. What is the address of the first cell represented by Range1?

2. What is the address of the last cell represented by Range1?

3. Write the cell range represented by Range1.

4. Write the cell range represented by Range 2.

5. What is the name of the cell range along a row?

6. What is the name of the cell range along a column?

7. Write the cell range represented by Range 3.

8. Give the number of cells in the cell range represented by Range 3.

Ans.

1. B5

2. D5

3. B5 : D5

4. F5 : F11

5. Range 1

6. Range 2

7. B7 : C12

8. 12

Q8. Write the output of the following on the basis of the given screenshot.

1. =SUM (A1,B1,C1)

2. =SUM(A1:C1)

3. =SUM(A1:C1,B2)

4. =SUM(B1:C2)

5. =SUM(A1:A3,C1:C3)

6. =AVERAGE (A1,B1,C1)

7. =AVERAGE (A1:C1)

8. =AVERAGE (A1:C1,B2)

9. =AVERAGE (B1:C2)

10. =AVERAGE (A1:A3,C1:C3)

11. =MAX(A1,B2,C1)

12. =MAX(A2:C2,B3)

13. =MAX(A1:C1)

14. =MAX(A1,B1:C2)

15. =MIN(A1,B2,C1)

16. =MIN(A2:C2,B3)

17. =MIN(A1:C1)

18. =MIN(A1,B1:C2)

19. =COUNT(A1,B1)

20. =COUNT(A1:C1)

21. =COUNT(A1:A4)

22. =COUNT(A1:C1,B2)

23. =COUNT(B1:C3)

24. =COUNT(A1:A3,C1:C3)

Ans.

1. 17

2. 17

3. 24

4. 23

5. 37

6. 5.66

7. 5.66

8. 6.33

9. 5.75

10. 6.16

11. 7

12. 8

13. 7

14. 7

15. 5

16. 4

17. 5

18. 4

19. 2

20. 3

21. 3

22. 4

23. 6

24. 6

Q9. Evaluate the following equations using operator precedence and then test the result in the spreadsheet.

1. 8-4/2

2. 5*5+8

3. 3+5*4

4. 2^5+8

5. 3+2^2

6. 5+6*2^2

7. 8/4*4

8. -4/2+2

9. 1+2^2-2

10. 4*3/2

Ans.

1. 6

2. 33

3. 23

4. 40

5. 7

6. 29

7. 8

8. 0

9. 3

10. 6

Search This Blog

Class:9 Unit-4 Electronic Spreadsheet