CBSE Class 7 Computer Science Formulas and Functions in MS Excel Worksheet Set 01

Answer: (c) MAX(). MAX() returns the largest value in the range.

Teacher's Note:
1. Look at each option and choose the one that picks the biggest value.
2. MIN is its opposite.

Answer: (a) =. The equals sign tells Excel that a calculation follows.

Teacher's Note:
1. Formulas and functions always start with the same sign.
2. Typing text first makes Excel treat the entry as plain text.

Answer: (c) Function. The Function Library is on the Formulas tab.

Teacher's Note:
1. The name of the group tells you what it contains.
2. It lives on the Formulas tab.

Answer: (c) cell reference. A reference such as B3 tells Excel where to find the data.

Teacher's Note:
1. A reference points to a place, not a number.
2. Relative, absolute and mixed are its three types.

Answer: (d) &. The ampersand joins text values, for example =A1&B1.

Teacher's Note:
1. This symbol is also used for 'and' in writing.
2. It works on text, not only numbers.

Answer: (a) Alt + =. Alt + = quickly inserts the SUM function.

Teacher's Note:
1. This is a keyboard shortcut for quick totals.
2. It saves a trip to the Formulas tab.

Answer: (b) =2+3*4. Multiplication is done before addition, so 2 + 12 = 14.

Teacher's Note:
1. Apply the order of operations before adding.
2. Brackets would change the answer.

Answer: (b) the current date. TODAY() returns the date and refreshes each day.

Teacher's Note:
1. The function name suggests its output.
2. It needs no arguments, only empty brackets.

Answer: (b) SUM(). SUM() adds all the numbers in the range.

Teacher's Note:
1. Think about what the word 'total' means.
2. AVERAGE gives the mean instead.

Answer: (c) ####. Widening the column removes the hash signs.

Teacher's Note:
1. The symbol is a clue about space, not about a wrong formula.
2. Double-click the column border to auto-fit.

Answer: MIN()

Teacher's Note:
1. MIN() scans the range and returns the lowest value.
2. MAX() is its opposite.

Answer: Alt + = (AutoSum)

Teacher's Note:
1. It inserts the SUM function for the selected cells.
2. You can also use the AutoSum button.

Answer: Relative reference

Teacher's Note:
1. Unless you add dollar signs, Excel treats the reference as relative.
2. It adjusts when the formula is copied.

Answer: Absolute reference, for example $A$1.

Teacher's Note:
1. The dollar signs lock both parts of the address.
2. It does not change when copied.

Answer: .xlsx

Teacher's Note:
1. Older versions used .xls, but modern Excel saves as .xlsx.
2. The extension tells the computer which program opens the file.

Answer: Ctrl + C

Teacher's Note:
1. Copy is followed by Ctrl + V to paste.
2. The same shortcut works in other programs.

Answer: Three: relative, absolute and mixed.

Teacher's Note:
1. Each type behaves differently when copied.
2. Mixed locks only one part.

Answer: AVERAGE()

Teacher's Note:
1. It adds the values and divides by how many there are.
2. For 2, 4 and 6 it gives 4.

Answer: A group of neighbouring cells forming a rectangle, such as C1:C10.

Teacher's Note:
1. The colon joins the first and last cell of the block.
2. A range can be a single column, row or a block.

Answer: The data entered in a cell, such as a number or text.

Teacher's Note:
1. Values can be constants typed in or results of formulas.
2. Dates are also values.

Answer: The address of a cell used in a formula, such as A1, where A is the column and 1 is the row. The three types are relative, absolute and mixed references.

Teacher's Note:
1. A reference tells Excel where to find the data.
2. The types differ in what happens when a formula is copied.

Answer: A formula is an instruction beginning with = that performs calculations such as addition, subtraction, multiplication and division using numbers and cell references.

Teacher's Note:
1. A formula links the cells it refers to.
2. Its result changes when those cells change.

Answer: A function is a ready-made formula in Excel that does a simple or complex calculation using the arguments given to it, for example =SUM(A1:A5).

Teacher's Note:
1. Functions save time and reduce mistakes.
2. Every function needs brackets for its arguments.

Answer: Joining two or more text values into one is called concatenation. It uses the ampersand (&). Example: if A1 contains Sunny and B1 contains Day, the formula =A1&B1 in C1 displays SunnyDay. Use =A1&" "&B1 to show Sunny Day with a space.

Teacher's Note:
1. The answer should name the ampersand and give a working formula.
2. Mentioning the space trick earns extra credit.

Answer: 1. Right-click the sheet tab (for example Sheet1).
2. Choose Rename from the shortcut menu.
3. The name is highlighted; type the new name.
4. Press Enter and the tab shows the new name.

Teacher's Note:
1. Write the steps in order.
2. The same right-click menu also contains Delete.

Answer: 1. #### : the column is too narrow for the value.
2. #NAME? : Excel does not recognise a name or function in the formula.
3. #VALUE! : the formula uses an invalid type of data.
4. #REF! : the formula refers to a cell that does not exist.
5. #DIV/0! : a number is divided by zero.

Teacher's Note:
1. Give the code and its cause together.
2. Any four of the five are enough.

Answer: 1. Sum: =SUM(B2:B11)
2. Average: =AVERAGE(B2:B11)
3. Highest: =MAX(B2:B11)
4. Lowest: =MIN(B2:B11)

Teacher's Note:
1. Each function takes the same range as its argument.
2. Check that each formula begins with =.

Answer: With relative references C2 contains =A2*B2. With the absolute reference to B1, C2 contains =A2*$B$1, because only the relative part moves.

Teacher's Note:
1. Relative parts move down by one row; absolute parts stay fixed.
2. This is the key difference between the two types.