EPFO EO/AO 2023 General Ability Test · Question 84 of 120

Correct — B, (b) Real-time referencing. A worksheet has exactly three ways of writing a cell reference — relative, absolute and mixed — and 'real-time referencing' is not one of them. It is not a term a spreadsheet uses at all; it borrows the familiar phrase 'real time' from data feeds and live dashboards and attaches it to a place where nothing about time is involved. The word 'not' is printed in bold italics in the booklet, so the ask is which of the four does not exist, and (b) is the invention. What the three genuine kinds do is easiest to see by asking what happens when a formula is copied to another cell. A relative reference such as B4 records not the cell but the direction and distance to it, so copying the formula one row down turns B4 into B5 and copying it one column right turns it into C4. That is what makes a spreadsheet worth using: one formula written in the first row of a column can be dragged down a thousand rows and each copy points at its own row. An absolute reference such as $B$4 locks both the column and the row with dollar signs, so every copy still points at B4 — the form used for a single rate, a conversion factor or a total that all the rows must divide by. A mixed reference locks one half and leaves the other free: $B4 keeps the column fixed while the row moves, and B$4 keeps the row fixed while the column moves. Mixed references are what make a multiplication table or a two-way rate grid possible from a single formula copied across a rectangle. So the four options are three real things and one fabrication, and the fabrication is the answer. In Excel the function key F4, pressed with the cursor on a reference in the formula bar, cycles through the four written forms — B4, $B$4, B$4, $B4 — which is a quick way of confirming that the family has three members and no fourth. One point of vocabulary that a careful candidate should hold separately: Microsoft Excel also uses the phrase 'reference style' for the choice between the ordinary A1 notation, in which a cell is named by its column letter and row number, and the R1C1 notation, in which both the row and the column are given as numbers. That is a notation setting rather than a kind of reference, and it does not affect this item, because 'real-time' is not one of those two either.

• (a)Relative referencing — This is a genuine and indeed the default kind of cell reference, so it cannot answer a stem asking which is not one. A reference written plainly as B4, with no dollar signs, is relative: the spreadsheet stores it as an offset from the cell holding the formula, so every copy of the formula adjusts to its own position. It is the behaviour that lets a single formula in the top row of a table be filled down an entire column, and a candidate who has ever dragged a fill handle has used it.
• (c)Absolute referencing — Also a real kind, and the direct opposite of relative, so it is not the odd one out. Writing dollar signs before both the column letter and the row number, as in $B$4, freezes the reference so that it survives copying unchanged. It is the form used whenever many rows must refer to one constant — a rate of interest, an exchange rate, a grand total in a percentage-of-total column. Omitting the dollar signs in that situation is the classic spreadsheet bug in which the formula slides off the constant and starts reading blank cells.
• (d)Mixed referencing — The third genuine member of the family, so it too is wrong here. A mixed reference locks one coordinate and leaves the other free: $B4 pins the column while allowing the row to change, and B$4 pins the row while allowing the column to change. It is the least used of the three by beginners and the most powerful, because it is what allows one formula to be copied across both directions of a rectangular grid — a multiplication table, a rate-against-tenure matrix, a monthly figure divided by a column of totals — without being rewritten for each cell.

A spreadsheet formula does not contain values, it contains addresses, and the whole behaviour of a worksheet under copying depends on how those addresses are written. The dollar sign is the only piece of syntax involved: it means 'do not adjust this coordinate when the formula is copied'. No dollar sign at all gives a relative reference, one before each coordinate gives an absolute reference, and one before either coordinate alone gives a mixed reference. Two further address forms build on these rather than competing with them: a three-dimensional reference reaches into another sheet by prefixing the sheet name and an exclamation mark, and an external reference reaches into another workbook. Naming a cell or a range gives a fourth way of writing the same thing, and a defined name behaves like an absolute reference wherever it is used. Understanding the three kinds is what separates a worksheet that can be extended by dragging from one that has to be retyped, and it is also the single commonest source of silent errors in office spreadsheets, because a formula with the wrong kind of reference produces a number rather than an error message.

Spreadsheet questions in the EO/AO computers block test the vocabulary an officer actually uses — cell referencing, common functions, chart types, freeze panes, filters — rather than anything programmatic. This item follows a shape the paper uses repeatedly: list three members of a genuine closed set and add a fourth invented term that sounds plausible, then ask which is not real. The habit rewarded is knowing that the set is closed and has exactly three members, so an unfamiliar fourth name is immediately suspect. Note the printing: the word 'not' is set in bold italics, and this booklet places a space before the question mark throughout.

• A worksheet has three kinds of cell reference: relative, absolute and mixed.
• A relative reference such as B4 adjusts when the formula is copied, because it is stored as an offset from the formula's own cell.
• An absolute reference such as $B$4 locks both coordinates with dollar signs and never changes on copying.
• A mixed reference locks one coordinate only — $B4 fixes the column, B$4 fixes the row.
• In Excel the F4 key cycles a selected reference through the four written forms B4, $B$4, B$4 and $B4.
• Mixed references are what allow one formula to be copied across a two-dimensional grid.
• Excel separately uses the phrase 'reference style' for the choice between A1 notation and R1C1 notation, which is a display setting, not a kind of reference.
• A three-dimensional reference addresses another sheet by prefixing the sheet name and an exclamation mark, as in Sheet2!B4.

• Reading the stem affirmatively and picking relative referencing because it is the one that is most familiar.
• Assuming any four-option list contains four real terms; here only three are real.
• Confusing the three kinds of reference with Excel's A1 against R1C1 notation setting, which the software also calls a reference style.
• Believing an absolute reference is needed everywhere; over-anchoring breaks a fill-down just as thoroughly as under-anchoring does.

Spreadsheet items in EO/AO papers ask what a symbol does, what a function returns, or which term in a list is not real. Cell referencing is the most frequently examined single topic in that group. Learn the dollar-sign rule and the three names together, and practise saying aloud what happens to each form when a formula is copied one cell down and one cell right.

No directly related past PYQ was found.

• practice — not a real PYQ

  In a worksheet, the reference $B4 is an example of :

  • (a)A relative reference
  • (b)An absolute reference
  • (c)A mixed reference
  • (d)A three-dimensional reference

  Answer(c) A mixed reference

• practice — not a real PYQ

  A formula in cell C1 containing the reference A1 is copied to cell C3. The reference becomes :

  • (a)A1
  • (b)A3
  • (c)C1
  • (d)C3

  Answer(b) A3