Consider an MS-Excel spreadsheet where column A lists cities, column B lists their states, and the header cells D1, E1 and F1 hold Haryana, MP and UP. The rows are Patiala (Punjab), Sonipat (Haryana), Noida (UP), Indore (MP), Mandi (HP), Sagar (MP), Panipat (Haryana) and Gwalior (MP). The formula IF($B2 equals D$1, $A2, 0) is entered in cell D2 and copied across the range D2 to F9. How many cells in the range D2 to F9 contain 0?
A0
B6
C12
D18 ✓ Correct
Correct answer: (D) 18
Explanation
With 24 cells and only 6 matching city-state pairs, 18 cells return 0.
The formula prints the city name when its state equals the column header, else 0.
The range D2 to F9 spans 8 rows by 3 columns, giving 24 cells.
Sonipat, Panipat, Indore, Sagar, Gwalior and Noida each match one header, giving 6 non-zero cells.
Patiala in Punjab and Mandi in HP match no header column.
Subtracting the 6 matches from 24 leaves 18 zero cells.
Want more like this? Create a free account to practise a full test, track your progress, and get spaced-repetition review.
Practise on Mcqkart →🎓 Book an expert →