Understanding the MS-Excel Problem

The problem asks us to determine the number of cells that will contain the value 0 within a specific range (D2:F9) of an MS-Excel spreadsheet after a formula is entered into cell D2 and then copied to the entire range.

The given spreadsheet contains data about cities, their states, and population. The formula provided is IF($B2 = D$1, $A2, 0).

Deconstructing the Excel Formula and References

The formula uses the IF function, which has the structure IF(logical_test, value_if_true, value_if_false).

• Logical Test: $B2 = D$1
• Value if True: $A2
• Value if False: 0

Let's analyze the cell references used in the formula and how they behave when copied:

• $B2: This is a mixed reference. The column B is fixed (absolute) due to the $ sign, while the row 2 is relative. When this formula is copied to other cells, the column will always remain B, but the row number will change relative to the row the formula is copied into. For example, in row 3 it will be $B3, in row 4 it will be $B4, and so on.
• D$1: This is also a mixed reference. The column D is relative, while the row 1 is fixed (absolute). When copied, the row number will always remain 1, but the column letter will change relative to the column the formula is copied into. For example, in column E it will be E$1, in column F it will be F$1, and so on.
• $A2: This is a mixed reference. The column A is fixed (absolute), while the row 2 is relative. When copied, the column will always remain A, but the row number will change relative to the row the formula is copied into. For example, in row 3 it will be $A3, in row 4 it will be $A4, and so on.

Evaluating the Formula Across the Range D2:F9

The formula IF($B_{current\_row} = Current\_column\$1$, $A_{current\_row}$, 0) is copied into the range D2:F9. This range includes cells in columns D, E, and F, and rows 2 through 9. There are 3 columns and 8 rows in this range, making a total of $3 \times 8 = 24$ cells.

The condition $B_{current\_row} = Current\_column\$1$ checks if the State listed in column B of the current row matches the State abbreviation found in row 1 of the current column (D1, E1, or F1). If the condition is TRUE, the cell will contain the City name from column A (e.g., $A2, A3, ...$); if the condition is FALSE, the cell will contain 0.

We need to count how many times the condition is FALSE in the range D2:F9.

Let's list the states in column B for rows 2 through 9 and the states in row 1 for columns D, E, and F:

• Column B (Rows 2-9): Panjab, Haryana, UP, MP, HP, MP, Haryana, MP
• Row 1 (Columns D-F): D1='Haryana', E1='MP', F1='UP'

Evaluating for Column D (Comparing $B_{row}$ with D$1 = 'Haryana'$)

The condition is `$B_{row} = 'Haryana'$` for rows 2 through 9.

• D2: $B2 ('Panjab') = 'Haryana'$ is FALSE (Result: 0)
• D3: $B3 ('Haryana') = 'Haryana'$ is TRUE
• D4: $B4 ('UP') = 'Haryana'$ is FALSE (Result: 0)
• D5: $B5 ('MP') = 'Haryana'$ is FALSE (Result: 0)
• D6: $B6 ('HP') = 'Haryana'$ is FALSE (Result: 0)
• D7: $B7 ('MP') = 'Haryana'$ is FALSE (Result: 0)
• D8: $B8 ('Haryana') = 'Haryana'$ is TRUE
• D9: $B9 ('MP') = 'Haryana'$ is FALSE (Result: 0)

Number of cells containing 0 in Column D: 6 (D2, D4, D5, D6, D7, D9)

Evaluating for Column E (Comparing $B_{row}$ with E$1 = 'MP'$)

The condition is `$B_{row} = 'MP'$` for rows 2 through 9.

• E2: $B2 ('Panjab') = 'MP'$ is FALSE (Result: 0)
• E3: $B3 ('Haryana') = 'MP'$ is FALSE (Result: 0)
• E4: $B4 ('UP') = 'MP'$ is FALSE (Result: 0)
• E5: $B5 ('MP') = 'MP'$ is TRUE
• E6: $B6 ('HP') = 'MP'$ is FALSE (Result: 0)
• E7: $B7 ('MP') = 'MP'$ is TRUE
• E8: $B8 ('Haryana') = 'MP'$ is FALSE (Result: 0)
• E9: $B9 ('MP') = 'MP'$ is TRUE

Number of cells containing 0 in Column E: 5 (E2, E3, E4, E6, E8)

Evaluating for Column F (Comparing $B_{row}$ with F$1 = 'UP'$)

The condition is `$B_{row} = 'UP'$` for rows 2 through 9.

• F2: $B2 ('Panjab') = 'UP'$ is FALSE (Result: 0)
• F3: $B3 ('Haryana') = 'UP'$ is FALSE (Result: 0)
• F4: $B4 ('UP') = 'UP'$ is TRUE
• F5: $B5 ('MP') = 'UP'$ is FALSE (Result: 0)
• F6: $B6 ('HP') = 'UP'$ is FALSE (Result: 0)
• F7: $B7 ('MP') = 'UP'$ is FALSE (Result: 0)
• F8: $B8 ('Haryana') = 'UP'$ is FALSE (Result: 0)
• F9: $B9 ('MP') = 'UP'$ is FALSE (Result: 0)

Number of cells containing 0 in Column F: 7 (F2, F3, F5, F6, F7, F8, F9)

Calculating the Total Count of Cells Containing 0

The total number of cells in the range D2:F9 that contain 0 is the sum of the counts from each column:

Total 0s = (0s in Col D) + (0s in Col E) + (0s in Col F)

Total 0s = 6 + 5 + 7 = 18

Thus, 18 cells in the range D2:F9 will contain the value 0 after the formula is copied.

Total cells containing 0 = 6 + 5 + 7 = 18.

Revision Table: Key Concepts

Additional Information: Mixed References in Excel

Mixed references are very useful when you need part of a formula reference to change and part to stay fixed as you copy it across a spreadsheet. The dollar sign ($) is the key indicator.

• $A1: Column A is fixed, row 1 is relative. Copying down changes the row (e.g., $A2, $A3), copying across keeps the column fixed (e.g., $A1, $A1).
• A$1: Column A is relative, row 1 is fixed. Copying down keeps the row fixed (e.g., A$1, A$1), copying across changes the column (e.g., B$1, C$1).

In this problem, $B2 locks onto column B but moves down rows, D$1 locks onto row 1 but moves across columns, and $A2 locks onto column A but moves down rows. This setup effectively allows the formula in each cell of D2:F9 to check if the state in its row (from column B) matches the state header in its column (from row 1).