(a) What is a function in MS Excel? (b) List two categories of functions in MS Excel. (c) What is the cell referencing in MS Excel. (d)(i) State two types o...

Answer Details

This question tests understanding of functions and cell referencing in Microsoft Excel.

(a) A function in MS Excel. A function is a built-in, predefined formula that performs a specific calculation on given values (called arguments) and returns a result, for example \( =SUM(A1:A10) \) adds the values in the range.

(b) Two categories of functions. Examples include mathematical/arithmetic functions (such as SUM, PRODUCT) and statistical functions (such as AVERAGE, MAX, MIN, COUNT). Other acceptable categories are logical (IF, AND), date and time, and text functions.

(c) Cell referencing. Cell referencing is the way of identifying and using a cell (or range of cells) in a formula by its address, that is its column letter and row number, such as B4 or A1:A10, so that the formula uses the value stored there.

(d)(i) and (ii) Two types of cell referencing, with an example of each.

• Relative referencing: the reference changes automatically when the formula is copied to another cell. Example: \( =A1+B1 \).
• Absolute referencing: the reference is fixed with dollar signs and does not change when copied. Example: \( =\$A\$1+B1 \).

(A third type, mixed referencing, fixes only the row or only the column, e.g. \( =A\$1 \) or \( =\$A1 \).)

Examination reminder: the dollar sign is what makes a reference absolute; without it a reference is relative and will shift when the formula is filled or copied.