Microsoft Excel 2010 - I

(i) D2 will display the sum of the values in cells B2 and C2.

D2 = B2 + C2
D2 = 130 + 2300
D2 = 2430

(ii) When the formula in D2 is copied over to D6, the relative references in the formula (B2 and C2) will adjust based on the relative position. So, the formula will be modified as = B6 + C6. Thus, D6 will display the sum of the values in cells B6 and C6.

D6 = B6 + C6
D6 = 295 + 3040
D6 = 3335

(iii) The dollar signs $(\text{\textdollar})$ indicate absolute references, meaning that the column (B and C) will remain fixed when the formula is copied or filled to other cells horizontally. The row (6) will still adjust based on the relative position.

(iv) When the formula in D6 is copied to D3, The absolute references (B and C) will remain fixed and only row (6) will adjust according to the relative position.

Thus, the formula in D3 will be $=\text{\textdollar B}3 + \text{\textdollar C}3$, reflecting the change in the row number while keeping the column references fixed.

D3 = $\text{\textdollar B}3 + \text{\textdollar C}3$
D3 = 490 + 2190
D3 = 2680

(v) If the value of C3 is deleted, the new value of D3 will be the sum of the remaining values in B3 and C3 (now treated as 0).

D3 = $\text{\textdollar B}3 + \text{\textdollar C}3$
D3 = 490 + 0
D3 = 490

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.

(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.

(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.

MS Excel

Reason — MS Excel lets us perform calculations and manipulate the numeric data.

Cell

Reason — Cell is the element which is identified by a unique row number and column number.

Range

Reason — Range is a group of contiguous cells which form the shape of a rectangle.

Colon

Reason — Colon symbol separates the address of the starting cell address from the ending cell address in a range.

Title Bar

Reason — Title Bar lies at the top of the document window in MS Excel.

Active Cell

Reason — Active Cell is the current cell which has a cell boundary around it.

XFD

Reason — XFD is the last valid column header in MS Excel.

Book1

Reason — The default name of a workbook in MS Excel is Book1.

Ctrl + S

Reason — Ctrl + S key combination is used to save a workbook in MS Excel.

Parenthesis

Reason — A pair of parenthesis are used to denote a negative values in MS Excel.

Right

Reason — The default alignment for numbers in MS Excel is right.

Formula

Reason — Formula is the expression which includes numbers, cell addresses, operators, and parenthesis.

Equal (=)

Reason — The symbol Equal (=) marks the beginning of a formula.

Text

Reason — Text is neither a number nor a formula.

3

Reason — An excel workbook displays 3 worksheets.

Colons

Reason — Colons separate time elements in Excel.

True

Reason — If we type the apostrophe symbol before a number, Excel assumes it as a text entry.

False

Reason — In Excel, the colon symbol (:) is used to create a range of cells. Date entries in Excel are typically recognized using date formats, such as "mm/dd/yyyy" or "dd-mm-yyyy".

False

Reason — The F2 function key is used to edit the contents of the active cell. Pressing F2 key places the cursor at the end of the cell contents.

True

Reason — The Clear All option clears all the contents from the selected range of cell.

True

Reason — The shortcut key combination to cut a specific range of cells is Ctrl + X.

True

Reason — The name given to a range of cells is added in the drop-down list of the Name Box.

True

Reason — The small black square present in the lower right corner of the active cell is called the Cell Handle.

True

Reason — The Insert dialog box has four options to choose from which are as follows:

1. Shift cells right
2. Shift cells down
3. Entire row
4. Entire column

True

Reason — The number of blank cells inserted in a worksheet will be same as the number of cells selected initially.

False

Reason — An expression which includes cell addresses, numbers, operators, and parenthesis is called a Formula.

True

Reason — Formulas always begin with the '=' symbol followed by cell references and operators.

True

Reason — We use the Compound Formulas when we need more than one operator.

True

Reason — The other name of cell reference in MS Excel is cell address.

False

Reason — Numeric, alphanumeric, non-numeric, or formula can be entered in an Excel worksheet. Alphanumeric data refers to text data.

Functions are pre-designed formulas to perform simple and complex calculations.

Functions are made up of two parts:

1. Arguments — Arguments are input values to functions. Arguments can be numbers, text, logical values like True or False, range of cell reference, or formulas that are enclosed within parenthesis.
2. Structure — The Structure of a function defines its basic skeleton, which forms the base of writing general syntax of functions. The structure begins with the equal sign(=), function name and then followed by the list of arguments separated by a comma within the parenthesis.

The syntax of writing a function is as follows:

= FUNCTION NAME (argument1, argument2, argument3... )

Yes, we can find the average of more than one range of cells by using the AVERAGE function.

The structure of the AVERAGE function used is as follows:

= AVERAGE (Range1 , Range2, ...)

For example, to find out the average of range B2 : D2 and B3 : D3, we will write the formula as follows:

= AVERAGE (B2 : D2 , B3 : D3)

The COUNT() function counts the number of numeric entries, formula, and date entries in a given range of cells. It is particularly useful for quickly determining the count of numerical entries in a dataset.

The COUNT() function is significant for statistical analysis and data validation. It helps us understand the size of a dataset, especially when dealing with numerical values.

The COUNT() function recognizes and counts cells that contain numerical values, including integers, decimals, and dates that Excel recognizes as numerical values. It excludes non-numeric entries such as text, errors, and blank cells.

In Excel, logical values are represented by the TRUE and FALSE values. These logical values are widely recognized by most functions for conditional testing and logical operations. The corresponding numeric values are:

TRUE — corresponds to the numeric value 1.

FALSE — corresponds to the numeric value 0.

When we use TRUE or FALSE in a numeric context, Excel automatically treats them as 1 and 0, respectively.

(a) = AVERAGE ()

(b) = MAX ()

(c) = SUM ()

(d) = MIN ()