M S EXCEL (SPREADSHEET) I CLASS 9

-



MS Excel is the spreadsheet Software developed by Microsoft. Spreadsheet software are used to perform any type of calculations and analysis.
Spreadsheets are used in different fields such as business, science and engineering for performing different calculations.
MS Excel is the most popular software that helps us to:

    1. Arrange Data in an organized way
    2. Type less and get more
    3. Perform calculations
    4. Auto recalculation
    5. Auto Format Data
    6. Compare Data through Chart
    7. Sort Data for better analysis
    8. Compare two worksheets side by side
    9. Spreadsheet in MS-Excel is also known as Worksheet.

      Worksheet

      It is a grid made up of rows and columns.
      The MS Excel worksheet has 1,048,576 rows and 16,384 columns. 

      Cell Address Any cell can be referenced by its cell address. Cell address is referred as the column name followed by row number. For example, the cell address of 5th row, 7th column is G5.

Active Cell : The active cell in a worksheet is the one in which you can enter data. 

STATUS BAR
It appears at the bottom of the spreadsheet. It displays various messages regarding the task being carried out on the worksheet.

NAME BOX
It displays the cell address of the active cell. Active cell is the one on which the cursor is pointing.

RANGE OF CELLS

A range of cells in Excel is defined as a specific set of cells, represented by a combination of letters and numbers (e.g., A1:B5). Ranges in Excel can be used for data entry, manipulation, applying formulas, formatting, and more.


Question 1:

Define the term workbook.
Answer:
A workbook is a collection of worksheets. By default, there are three worksheets in every workbook.

Question 2

Mayur could not complete the assignment given by his teacher which is shown below. Help him to fill the blank cells marked with yellow colour.

Mayur can use formulas to calculate the values of the blank cells. Let us assume that "R. No." has the cell reference A1 and write the formulas accordingly.

  1. To calculate Total, he can write the formula = C2 + D2 in cell E2. This formula will calculate the sum of cells C2 and D2 in cell E2. The formula can be copied in range E3 : E6 to fill the Total column.

  2. To calculate Percentage, he can write the formula = (E2 / 100) * 100 in cell F2. This formula will calculate the percentage of marks in cell F2. The formula can be copied in range F3 : F6 to fill the Percentage column.

  3. To fill the Remarks column, Mayur can write the formula = IF (F2 >= 40, "Pass", "Fail") in cell G2. This formula will return "Pass" if the percentage is greater than or equal to 40, else return "Fail". The formula can be copied in range G3 : G6 to fill the Remarks column.

Question 3

The class teacher asked Ravi to observe the following worksheet carefully, and answer the questions based on it:

i) Identify the nature of formula in the cell E9.

(ii) Copy the formula applied in cell E9 to all the cells from E5 to E11.

(iii) Find the maximum and the minimum value among the cells E5 to E11.

(iv) Calculate the average of both Oral and Written marks.

Answer

(i) The formula in E9 is = AVERAGE (C9 : D9). It calculates the average of the marks in cells C9 and D9. It uses the AVERAGE() function.

(ii) When the formula is copied to cells from E5 to E11, the cell references adjust themselves relatively as follows:

E5 = AVERAGE (C5 : D5)

E6 = AVERAGE (C6 : D6)

E7 = AVERAGE (C7 : D7) and so on.

(iii) The maximum value can be calculated by using the formula =MAX(E5:E11). The maximum value is 93.

The minimum value can be calculated by using the formula =MIN(E5:E11). The minimum value is 66.5.

(iv) Ravi can use the formula =AVERAGE(C5:C11) in the cell C12 to calculate the average of Oral marks.

He can use the formula =AVERAGE(D5:D11) in the cell D12 to calculate the average of Written marks.



Question 4

Sahil has been given the hard copy of the following worksheet by his teacher

His teacher has asked him to:

(i) Calculate the total percentage of each student.

(ii) Find the Grade of each student by keeping a simple rule in view, i.e., students scoring below 90% must get B+ while those above this baseline must be given an A+.

(iii) Also find the Maximum marks and calculate the average Marks of both Practical and Theory.

Answer

(i) Sahil can calculate the Total Percentage of Veena by using the formula =(C3+D3)/20*100 in cell E3. Then, he can copy the formula in cell range E4 : E7 to calculate the Total Percentage of all students.

(ii) Sahil can calculate the Grade of Veena by using the formula =IF(E3<90, "B+", "A+") in cell F3. Then, he can copy the formula in cell range F4 : F7 to calculate the Grade of all students.

(iii) To find the maximum marks in Practical, Sahil can use the formula =MAX(C3:C7) in cell C10.

To find the maximum marks in Theory, Sahil can use the formula =MAX(D3:D7) in cell D11.

To find the average marks of Practical, he can use the formula =AVERAGE (C3:C7) in cell E10.

To find the average marks of Theory, he can use the formula =AVERAGE (D3:D7) in cell F11.



             Question 5 What is Cell Reference? Explain its types?
              Answer 5 A cell reference is the unique address of each cell in a worksheet which helps to get results in a formula. There are three types of cell reference.
Types of cell referencing are as follows:-
1. Relative- Every relative cell reference in formula automatically changes when the formula is copied down a column or across a row. As the example illustrated here shows, when the formula is entered (= A1+B1) in Cell C1 then this formula copied in C2 then it will change into (= A2+B2) related to the cell. 


 

A

B

C

1

5

6

=A1+B1

2

3

6

=A2+B2

3

4

2

=A3+B3

4

7

4

=A4+B4


 2. Absolute An absolute cell reference is fixed. Absolute references do not change if you copy a formula from one cell to another. Absolute references have the dollar sign ($) like $A$9. 

 

A

B

C

1

5

6

=$A$1+B1

2

3

6

=$A$1+B2

3

4

2

=$A$1+B3

4

7

4

=$A$1+B4


3. Mixed A mixed cell reference has either an absolute column and a relative row, or an absolute row and a relative column, e.g. $A1 is an absolute reference to column A and a relative reference to row 1. As a mixed reference is copied from one cell to another, the absolute reference stays the same but the relative reference changes.


 

A

B

C

1

5

6

=A$1+B1

2

3

6

=A$1+B2

3

4

2

=A$1+B3

4

7

4

=A$1+B4





Comments

Post a Comment

Popular posts from this blog

CLASS 8 DATA TYPES IN PYTHON

-
  DATA TYPES IN PYTHON A. Tick the correct option 1.   Which of the following is not a valid variable name? ANS1. a. Print 2.   Which of the following is not a string?   ANS2. a. 89 3.   How many types of number data type are there in Python? ANS C. 4 4.   Which of the following is a data type? ANS A. TUPLE 5.   Which of the following will you use to access a value stored in a dictionary? ANS C. [ ] B. Fill in the blanks 1.   A multiline string spreads across multiple lines. 2.   Data type is used to represent data in the memory of the computer. 3.   A tuple can consist of different types of values. 4.     A conditional statement can also be called a decision making statement. 5.     The IF conditional statement has code for the condition being true only. C  Write the output of the following code snippets. 1. >>> number_of_pages=200 >>>print( number_of_pag...
Read more

CHAPTER 4 RAISING QUERIES CLASS 8 COMPUTER

-
CLASS 8 CHAPTER 4 RAISING QUERIES A. Fill in the blanks with the help of the given words. 1. Connecting tables in a database is the concept of relational database. 2. A foreign key is the primary key of another table which creates a link between two tables based on a common column. 3. If you modify the primary key value, Cascade update related fields  allows you to update all the related foreign key values. 4. A query   allows you to pull information from one or more tables based on a set of defined search conditions. 5. The query design view allows you to customise the query.     B. Tick the correct answer. 1. Which of the following options allows you to delete the related records from the foreign key table, if you delete records from the primary key table? ANS B. Cascade delete related records 2. Which option in Edit Relationships dialog box prevents you from entering a value in the foreign key field? ANS B. Enforce referential integrity 3. Press.......... to...
Read more

HTML 1 TEST ONLINE 20 MAY 2025

-
Image
                   QUESTION 3. Which HTML Tag is used to underline the text?                                 a. <U>   b. <I>    c. <TT>   d. <B>                               QUESTION 4. HTML Tags are written in                                 a. Capital letters                                b. Small letters                                c. Both Capital and Small letters                                d. No...
Read more