# Spreadsheet: Functions and Charts

## State whether the following statements are True/False

#### Question 1

A range of cells is a group of cells that have been selected and which form a shape of a rectangular box.
True

#### Question 2

A spreadsheet can only contain numeric data.
False

#### Question 3

You can insert a chart but not clipart in a worksheet.
False

#### Question 4

The cell in which the cell pointer is located in a worksheet is the active cell.
True

#### Question 5

You can select a range of data as per your choice in a spreadsheet.
True

## Fill in the blanks

#### Question 1

Charts are the pictorial representation of data values stored in the worksheet.

#### Question 2

When the corresponding cell address changes with reference to a new cell address, it is known as relative reference.

#### Question 3

By default, MS Excel provides three worksheets in a workbook.

#### Question 4

The built-in formulae for specific numeric/non-numeric processing are called functions.

#### Question 5

A range of cells is a rectangular block consisting of a few cells, an entire row, an entire column or the whole worksheet.

## Write the format of the functions used in MS Excel to perform the following tasks

#### Question 1

To calculate the average of 82, 67, 80, 74 and 95.

=AVERAGE(82,67,80,74,95)

#### Question 2

To find the highest value of the cell references from D3 to K3.

=MAX(D3:K3)

#### Question 3

To calculate the sum of the first five multiples of 7.

=SUM(7,14,21,28,35)

#### Question 4

To determine the lowest value of the cell references from A4 to A12.

=MIN(A4:A12)

#### Question 5

To find the sum of all the prime numbers from 1 to 10.

=SUM(2,3,5,7)

#### Question 6

To find the arithmetical mean of the cell references from E4 to K4.

=AVERAGE(E4:K4)

## Explain the meaning of the following functions

#### Question 1

=SUM (C5:H5)

This function calculates the sum of the cell references from C5 to H5.

#### Question 2

=AVERAGE (K12:K50)

This function calculates the average of the cell references from K12 to K50.

#### Question 3

=COUNT (B15:B35)

This function counts the number of cells in the range of cells from B15 to B35.

#### Question 4

=MAX (A4:A14)

This function finds the highest value among the cell references from A4 to A14.

## Define the following

#### Question 1

Relative Referencing

In Relative Referencing, the address of the cells is specified in a way that when the formula is copied to a new cell, the corresponding cell address changes with reference to the new cell address.=A1+A2 is an example of Relative Referencing.

#### Question 2

Absolute Referencing

In Absolute Referencing, the address of the cells is specified in a way that it remains constant when the formula is copied to a new cell. To keep the cell value absolute apply the '\$' sign. =\$A\$1+\$A\$2 is an example of Absolute Referencing.

#### Question 3

Mixed Referencing

Mixed Referencing is a combination of Relative and Absolute reference. In this type of reference, either row or column has to remain fixed. =\$A1+A\$2 is an example of Mixed Referencing.

## Explain the following functions

#### Question 1

SUM ( )

This function calculates the total of all the values of the specified cells and returns the result to the cell where the cell pointer is located.

Its syntax is:
=SUM(number1, number2, .......) where argument type is Number and return type is Number.

To find the sum of the cell values ranging from A1, A2, A3, ........ to An, the function will be written as:
=SUM(A1, A2, A3, ......., An)
OR
=SUM(A1:An)

#### Question 2

AVERAGE ( )

This function takes all the values of the specified cells and returns the average of the cell values in the active cell.

Its syntax is:
=AVERAGE(number1, number2, .......) where argument type is Number and return type is Number.

To find the average of the cell values ranging from A1, A2, A3, ........ to An, the function will be written as:
=AVERAGE(A1, A2, A3, ......., An)
OR
=AVERAGE(A1:An)

#### Question 3

MAX ( )

This function returns the highest value from among the specified cells in the active cell.

Its syntax is:
=MAX(number1, number2, .......) where argument type is Number and return type is Number.

To know the highest of the cell values ranging from A1, A2, A3, ........ to An, the function will be written as:
=MAX(A1, A2, A3, ......., An)
OR
=MAX(A1:An)

#### Question 4

MIN ( )

This function returns the lowest value from among the specified cells in the active cell.

Its syntax is:
=MIN(number1, number2, .......) where argument type is Number and return type is Number.

To know the lowest of the cell values ranging from A1, A2, A3, ........ to An, the function will be written as:
=MIN(A1, A2, A3, ......., An)
OR
=MIN(A1:An)

## Short Answer Questions

#### Question 1

What are the points to be taken care of while writing the format of a function in MS Excel?

The following points must be taken care of while writing the format of a function in MS Excel:

1. Each function must begin with an 'equal to' (=) sign.
2. Parenthesis is used to indicate the opening and closing of a function.
3. Arguments are written within the parenthesis.
4. Commas are used to separate the arguments.

#### Question 2

What is meant by cell reference?

The cell address that we use in the formula is known as the cell reference. The address of the cell consists of the column letter followed by the row number. For example, cell address A1 denotes the column name as A and the row number as 1.

#### Question 3

Define Sheet Tab.

The current worksheet that is displayed on the screen is called the 'Sheet Tab'.

#### Question 4

Explain the term 'Range of Cells' in a spreadsheet.

A range of cells is a group of cells that have been selected/highlighted and which form a shape of a rectangular box. A range is specified by giving the addresses of the first cell in the range and the last cell in the range.

## Write all the steps to perform the following tasks in MS Excel

#### Question 1

Naming a Sheet Tab

Step 1: Select the Sheet Tab that you want to name.
Step 2: Double click on the sheet tab and it will become darkened.
Step 3: Enter an appropriate name.
Step 4: Press the 'Enter' key.

#### Question 2

Renaming Sheet Tab

Step 1: Select the Sheet Tab that you want to rename.
Step 2: Right-click on the sheet tab. Choose the 'Rename' option from the pop-up list.
Step 3: Type a name in the 'Sheet Tab' box.
Step 4: Press the 'Enter' key.

#### Question 3

Deleting a Sheet Tab

Step 1: Select the Sheet Tab that you want to delete.
Step 2: Right-click on the Sheet Tab.
Step 3: Select the option 'Delete' from the pop-up list.
Step 4: Press the 'Enter' key.

#### Question 4

Selecting partial range in a row

Step 1: Select the row.
Step 2: Bring the cell pointer to the desired location from where you want to start your selection.
Step 3: Click the left mouse button and keep dragging to your right till you reach the last cell to be selected.
Step 4: Release the mouse button.

#### Question 5

Selecting partial range in a column

Step 1: Select the column.
Step 2: Bring the cell to the desired location from where you want to start your selection.
Step 3: Click the left mouse button and keep dragging downwards till you reach the last cell to be selected.
Step 4: Release the mouse button.

## MS Excel Tasks

You have purchased some textbooks from a book store as given below. Perform the following tasks in MS Excel from the given table and write all the steps. #### Question 1

Find the average price of the books.

Step 1: Select a new cell
Step 2: Write the formula =AVERAGE(C3:C9)

#### Question 2

If the bookseller offers 10% discount, then calculate the amount to be paid to the shopkeeper.

Step 1: Select cell D3.
Step 2: Write the formula =C3-(C3*0.1) and press enter key.
Step 3: Select cell D3 again and drag the cell handle from D3 till D9.
Step 4: Select cell D10.
Step 5: Write the formula =SUM(D3:D9) and press enter key.
Step 6: Cell D10 shows the amount payable to the shopkeeper.

#### Question 3

Determine the highest price of the book purchased by you.