##### Class 9 Information Technology Book Solution

### Unit 4 Electronic Spreadsheet Class 9 Book Solution

#### A. Multiple Choice Questions

**1. Which of the following technique can be used to allow only date value in cell?**

(a) Data formatting

(b) Data sorting

(c) Data filtering

(d) Data validation

**Answer:** (d) Data validation

**2. Which of the following options when selected deletes all data validation?**

(a) Delete formatting

(b) Delete all

(c) Delete formula

(d) Delete me

**Answer:** (b) Delete all

**3. We can replace multiple occurrences of a word using which of the following facilities of Calc?**

(a) Find and replace

(b) By replace only

(c) By copy command

(d) By preview command

**Answer:** (a) Find and replace

**4. What is the name of mechanism to arrange the data in a particular order?**

(a) Sorting

(b) Searching

(c) Filtering

(d) Validating

**Answer:** (a) Sorting

**5. What is the name of mechanism to filter out unnecessary data?**

(a) Sorting

(b) Searching

(c) Filtering

(d) Validating

**Answer:** (c) Filtering

**6. Which of the following type of package does Calc refer to?**

(a) Spreadsheet

(b) Double sheet

(c) Multi-sheet

(d) Cannot determine

**Answer:** (a) Spreadsheet

**7. Which of the following is an extension of a worksheet created in Calc?**

(a) .ods

(b) .odd

(c) .xls

(d) .obj

**Answer:** (a) .ods

##### Unit 4 Electronic Spreadsheet Class 9 Book Solution

**8. How can one calculate the total of values entered in a worksheet column of?**

(a) By manual entry

(b) By auto-sum

(c) By formula

(d) By sum function

**Answer:** (b) By auto-sum

**Note: All options are possible.**

**9. If we move a cell containing a formula having reference to another cell in the worksheet what will happen to the cell numbers used in the formula?**

(a) The cell row and columns are changed at destination.

(b) The cell row change at destination.

(c) The cell columns are changed at destination.

(d) No change will scour.

**Answer:** (a) The cell row and columns are changed at destination.

**10. What is the correct way to enter a function in Calc?**

(a) Directly typing function name in a cell

(b) Using function wizard or selecting from toolbar

(c) Both (a) and (b)

(d) Depends on the function

**Answer:** (c) Both (a) and (b)

##### Unit 4 Electronic Spreadsheet Class 9 Book Solution

**11. A function should start with__________________.**

(a) ‘=’ sign

(b) alphabets

(c) numbers

(d) All of the these

**Answer:** (a) ‘=’ sign

**12. Which of the following option is used to print a chart?**

(a) Insert → Chart

(b) File → View

(c) File → Print

(d) View → Chart

**Answer:** File -> Print

**13. How many axes does charts in Calc have?**

(a) Two

(b) Three

(c) Two or three

(d) Four

**Answer:** Two

**14. The chart preview can be seen in________________.**

(a) Page preview

(b) Chart preview

(c) Export chart

(d) All of these

**Answer:** Page preview

##### Unit 4 Electronic Spreadsheet Class 9 Book Solution

#### B. Fill in the blanks

**1. The column immediately next to column “Z” is _______.**

**Answer:** AA

**2. The default extension of a workbook created using a LibreOffice Calc spreadsheet is ______.**

**Answer:** .ods

**3. The spreadsheet feature used to continue the series is called as __________.**

**Answer:** Fill Handle

**4. The formula “=MIN(C1:C5)” stored in cell C6 when copied to cell D6 changes to _______.**

**Answer:** = MIN(D1:D5)

**5. The formula in cell A2 is =B2+C3. On copying this formula to cell C2, C2 will change to _______.**

**Answer:** =D2 + E3

**6. The cell address of the cell formed by the intersection of the ninth column and the eighth row will be __________.**

**Answer:** I8

**7. $A1$B2 is an example of _________ referencing in spreadsheet software.**

**Answer:** Mixed

**8. Numbers entered into a cell are automatically ________ aligned.**

**Answer:** right

**9. If A1:A5 contain the numbers 16, 10, 3, 25 and 6 then =Average(A1:A5;60) will display______________.**

**Answer:** 20

**10. In _______________referencing, the reference changes rows and columns automatically when it is copied to a new cell.**

**Answer:** Relative

#### C. State whether the following statements are True or False

**1. A cell is a combination of row and column.**

**Answer:** False

**2. A spreadsheet is also called as worksheet.**

**Answer:** True

**3. There are ‘n’ number of sheets in a spreadsheet.**

**Answer:** False

**4. In a spreadsheet, we can change the column width and row height.**

**Answer:** True

**5. $A1$B2 is an example of mixed referencing. **

**Answer:** True

#### D. Solve the following in a spreadsheet

**1. Cell A1 contains the number 10 and B1 contains 5. What will be the contents of cell C1, if the formula =A1+B1*2^3 is entered in cell C1?**

**Answer:** 50

**2. The contents of Cell A1, B1, C1 and D1 are 5, –25, 30 and –35, respectively. What will be the value displayed in cell E1 which contains the formula =MIN(A1:D1).**

**Answer:** -35

**3. Cell D5 contains the formula =$B$5+C5 and this formula is copied to cell E5, what will be the copied formula in cell E5?**

**Answer:** = $B$5 + D5

**4. Cell D5 contains the formula =$B5 + C5 and this formula is copied to cell E5, what will be the copied formula in cell E5?**

**Answer:** = $B5 + D5

**5. Cell D5 contains the formula =$B5 + C$5 and this formula is copied to cell E6, what will be the copied formula in cell E6? **

**Answer:** = $B6 + D$5

#### E. Short answer questions (50 words)

**1. What do you call the document created in a spreadsheet application?**

**Answer:** The document created in a spreadsheet application is called Workbook.

**2. What are the steps to create a new spreadsheet?**

**Answer:** Press Ctrl + N

OR

Click on New icon in standard toolbar

OR

Click on File > New > Spreadsheet

**3. What is the difference between spreadsheet, worksheet and sheet?**

**Answer:** **Spreadsheet:- **Spreadsheet is a long sheet of rows and columns on the computer screen to do data analysis and calculation. In other words, a spreadsheet is a grid which interactively manages and organises data in rows and columns. It is also called as Electronic Spreadsheet.

**Worksheet:** The collection of Sheet is known as Worksheet. It is also known as spreadsheet.

**Sheet:- **Sheet is the actual page/document where you suppose to work. A sheet can have many individual cells arranged in rows and columns.

**4. What is the default name of the worksheet? How can it be renamed?**

**Answer:** The default name of worksheet are Sheet1, Sheet2, Sheet3, …

**To rename a sheet : **

Right click on sheet tab > Select Rename option from context menu > Type the name and press Enter.

**Method 2 :**

Select the menu Sheet -> Rename Sheet > Type new name and press enter key.

**5. Write the steps to insert and delete the worksheet in Calc.**

**Answer:** To insert a sheet:

- Right click on Sheet tab and select Insert Sheet OR Select Sheet -> Insert Sheet option
- Specify the position before or after current sheet, and number of sheets in insert sheet dialog box.
- Click on Ok

**6. What is an active cell? How to delete the contents of an active cell?**

**Answer:** The cell which is ready to take data from the user is called the Active Cell.

This selected or activated cell is always highlighted, with a thick border. The address of the active cell is displayed in the name box.

**To delete content** from the active cell, press the delete button.

**7. What is relative and absolute cell address in the spreadsheet?**

**Answer:** Referencing is the way to refer the formula or function

from one cell to the next cell along the row or column.

In Relative Referencing, when you drag any formula in any row or column in any direction, the formula gets copied in the new cell with the relative reference i.e it changes accordingly. Syntax of Relative referencing: **ColumnName_RowNumber**

Example: A1, B9, D156

**8. Explain any two operations performed on data in a spreadsheet.**

**Answer:** Operations performed on data in spreadsheet are – Addition, Subtraction, Multiplication, Division, Comparision, Concatenation, etc.

**Comparison **means comparing two values to find which value is equal, greater, smaller, etc.

**Addition **– means adding two numerical values.

**Concatenation** means adding two strings, by concatenating one string at the ends of another.

**9. How do formulae work in a spreadsheet?**

**Answer:** Any expressions that begins with an equals ‘=’ is treated as formula.

In the expression, the ‘=’ followed by values, cell address and functions are called as formula.

When a formula is entered in a cell in a worksheet the value of the

equation is displayed in the cell and the formula is shown in the formula bar.

**10. Can you include more than one mathematical operators in a formula?**

**Answer:** Yes

**11. How to make visible the desired toolbar a spreadsheet?**

**Answer:** Select View -> Toolbars -> Required Toolbar name, to open the toolbar in a spreadsheet.

**11. Give the syntax and example of any three mathematical functions in spreadsheet.**

**Answer:** Three mathematical functions

Function | Syntax | Example |

SUM ( ) :- to add tow or more than two values. | =SUM(number1, number2, …) | =SUM(A1, B2, C3) =SUM(A1:C3) |

ABS() : returns absolute value of a number | =ABS(NUMBER) | =ABS(-65) 65 |

PRODUCT() : returns the product of numbers | = PRODUCT(number1, number2, …) | =PRODUCT(5,10) 50 |

**12. Give the syntax and example of any three statistical functions in spreadsheet.**

**Answer:** Three statistical functions

Function | Syntax | Example |

MAX( ) :- returns the maximum number from the given numbers | =MAX(number1;number2; …) | =MAX(5; 2; 6) 6 =MAX(A1:C3) |

AVERAGE() : returns average of samples. | =AVERAGE(number1; number2; ..) | =AVERAGE(5; 10; 15) 10 |

COUNT() : count the how many numbers are in the arguments. | = COUNT(value1; value2, ..) | =COUNT(A1:A9) 8 One cell has non-numeric data. |

**13. Give the syntax and example of any three decision-making functions in spreadsheet.**

**Answer:** Decision-Making Functions

Function | Syntax | Example |

IF( ):- give the results on the basis of condition | =IF(criteria; than Value; otherwise values) | =IF(b2>5,”Yes”, “No”) Yes =IF(b2<5,”Yes”, “No”) No |

SUMIF( ) : Sum the value of sum_range, if the criteria meet the criteria range value. | =SUMIF(range; criteria; sum-range) | =SUMIF(A2:A6;”>10″;A2:A6) 15 |

COUNTIF( ) : count the value if values meet the criteria. | = COUNTIF(range; criteria) | =COUNTIF(A2:A6; “>8”) 2 |

**14. Give the syntax and example of any three date and time functions in spreadsheet.**

**Answer:** Three data and time functions

Function | Syntax | Example |

DAY( ):- Returns the day of given date value. The day is returned as an integer between 1 and 31. | =DAY(date_value) | =DAY(“2022-09-12”) 12 |

DAYSINYEAR( ) : Calculates the number of days of the year in which the date entered occurs. | =DAYSINYEAR(DATE) | =DAYSINYEAR(“2020-09-12”) 366 =DAYSINYEAR(“2022-09-12”) 365 |

NOW( ) : Returns the computer system date and time. | = NOW( ) | =NOW() 12/09/2022 17:40 |

**15. Give the syntax and example of any three logical functions in spreadsheet.**

**Answer:** Three logical functions are

Function | Syntax | Example |

AND( ):- returns TRUE if all the conditions are true. | =AND(logical value1; logical value 2; logical value; …) | =AND(2 < 5; 4 > 3) TRUE =AND(2 > 5; 4 > 3) FALSE |

returns FALSE if all the conditions are FALSE.OR( ):- | =OR(logical value1; logical value 2; logical value; …) | =OR(2 < 5; 4 < 3) TRUE =OR(2 > 5; 4 > 3) FALSE |

NOT( ):- returns TRUE if all the conditions are FALSE. | =NOT(logical value) | =NOT(2 > 6) TRUE = NOT(6 > 2) FALSE |

**16. Give the syntax and example of any three string functions in spreadsheet.**

**Answer:** Three String functions

Function | Syntax | Example |

LEN( ):- returns the length of the string. | =LEN(string) | =LEN(“Hello”) 5 |

LEFT( ) : returns the specified number of characters from left side. | =LEFT(string, number of chars) | =LEFT(“mycstutorial”, 4) mycs |

FIND( ) : find the substring and returns the first occurrence of substring from given position. | = FIND(search_text;text; position) | =FIND(“h”, “hello hi”) 1 =FIND(“h”, “hello hi”, 2) 7 |

** 17. Explain the advantages of drawing a chart in Calc.**

**Answer:** Advantages of drawing a chart in calc are :

- (a) Easy to present and comprehend.
- (b) Easy to compare
- (c) Easy to analyse
- (d) An effective tool to communicate.

**18. Explain in one line each the various types of charts.**

**Answer:** Types of Charts

.

Charts Types | Purpose |

Column Chart | A vertical bar chart is sometimes called a column chart. Comparing classes of data items in group. It is suitable for Group comparison. |

Bar Chart | It presents categorical data with rectangular bars with heights or lengths proportional to the values that they represent. The bars can be plotted vertically or horizontally. Comparing classes of data items in group. It is suitable for Group comparison. |

Line Chart | Line chart displays information as a series of data points called ‘markers’ connected by straight line segments. Comparing classes of data items in group. It is suitable for Group comparison. |

Pie Chart | A pie chart is a circular statistical graphic, which is divided into slices to illustrate numerical proportion. In a pie chart, the arc length of each slice is proportional to the quantity it represents. Comparing classes of data items as percentage. |

XY Scatter Chart | Scatter (X Y) charts are typically used for showing and comparing numeric values, like scientific, statistical, and engineering data. A Scatter Chart has two value axes −. Horizontal (x) value axis; Vertical (y) value axis; It combines x and y values into single data points and shows them in irregular intervals, or clusters. Comparing data in pairs |

**19. Write the steps to insert a chart in Calc.**

**Answer:** Steps to insert a chart in Calc are :

- Select the range of data.
- Click on Insert menu → Chart
- Select the type of chart.
- Click Finish

**20. Name and explain any five components of a chart in a spreadsheet package.**

**Answer:** **Components of a chart in a spreadsheet are : (Write any five)**

- Chart Area of the Chart
- Plot Area of the Chart
- Data Points that are plotted in the chart
- Horizontal and Vertical Axis in the chart
- Legend of the chart
- Chart and Axis Title used in the chart
- Data Label for identifying details of data point in the chart