Question Details

Consider the following MS-Excel spreadsheet in which the population column represents the city's population in millions of people:



A B C D E F
1) City State Population Haryana MP UP
2) Patiala Punjab 8.34


3) Sonipat Haryana 3.86


4) Noida UP 2.71


5) Indore MP 2.16


6) Mandi HP 1.49


7) Sagar MP 1.38


8) Panipat Haryana 1.39
 


9) Gwalior MP 1.24


Suppose the formula-IF ($B2-D$1, SA2,0) is entered into cell D2 and then the cell D2 is copied and pasted to D2:F9. How many cells in the range D2:F9 0?

Options

A

0

B

6

C

12

D

18

Show Answer
Correct Answer
D 18

Solution :

The correct option is 18.

Analysis of the Formula and Typographical Correction:
The formula specified in the question is IF ($B2-D$1, SA2,0). In standard spreadsheet syntax, the characters - (minus) and S are common typographical errors for = (equals) and $ (dollar sign). Thus, the intended logical formula is:
=IF($B2=D$1, $A2, 0)

Understanding Reference Types:
1. $B2 (Mixed Reference): The column is absolute (locked to column B), but the row is relative. When copied across columns, it still references column B. When copied down rows, the row number changes accordingly.
2. D$1 (Mixed Reference): The column is relative, but the row is absolute (locked to row 1). When copied across columns, the column references columns D, E, or F. When copied down, it always references row 1 (which contains the state names "Haryana", "MP", and "UP").
3. $A2 (Mixed Reference): The column is locked to column A (City), while the row number adjusts dynamically as it is copied down.

Cell Evaluation for Range D2:F9:
We evaluate the formula =IF($B[row] = [column]$1, $A[row], 0) for each of the 24 cells in the range D2:F9:

Row 2 (B2 = "Punjab"):
- D2: IF("Punjab" = "Haryana", "Patiala", 0) → 0
- E2: IF("Punjab" = "MP", "Patiala", 0) → 0
- F2: IF("Punjab" = "UP", "Patiala", 0) → 0
(3 cells contain 0)

Row 3 (B3 = "Haryana"):
- D3: IF("Haryana" = "Haryana", "Sonipat", 0) → "Sonipat"
- E3: IF("Haryana" = "MP", "Sonipat", 0) → 0
- F3: IF("Haryana" = "UP", "Sonipat", 0) → 0
(2 cells contain 0)

Row 4 (B4 = "UP"):
- D4: IF("UP" = "Haryana", "Noida", 0) → 0
- E4: IF("UP" = "MP", "Noida", 0) → 0
- F4: IF("UP" = "UP", "Noida", 0) → "Noida"
(2 cells contain 0)

Row 5 (B5 = "MP"):
- D5: IF("MP" = "Haryana", "Indore", 0) → 0
- E5: IF("MP" = "MP", "Indore", 0) → "Indore"
- F5: IF("MP" = "UP", "Indore", 0) → 0
(2 cells contain 0)

Row 6 (B6 = "HP"):
- D6: IF("HP" = "Haryana", "Mandi", 0) → 0
- E6: IF("HP" = "MP", "Mandi", 0) → 0
- F6: IF("HP" = "UP", "Mandi", 0) → 0
(3 cells contain 0)

Row 7 (B7 = "MP"):
- D7: IF("MP" = "Haryana", "Sagar", 0) → 0
- E7: IF("MP" = "MP", "Sagar", 0) → "Sagar"
- F7: IF("MP" = "UP", "Sagar", 0) → 0
(2 cells contain 0)

Row 8 (B8 = "Haryana"):
- D8: IF("Haryana" = "Haryana", "Panipat", 0) → "Panipat"
- E8: IF("Haryana" = "MP", "Panipat", 0) → 0
- F8: IF("Haryana" = "UP", "Panipat", 0) → 0
(2 cells contain 0)

Row 9 (B9 = "MP"):
- D9: IF("MP" = "Haryana", "Gwalior", 0) → 0
- E9: IF("MP" = "MP", "Gwalior", 0) → "Gwalior"
- F9: IF("MP" = "UP", "Gwalior", 0) → 0
(2 cells contain 0)

Calculating Total Cells with 0:
Adding up the count of zero-valued cells across all rows:
Total Zeros = 3 + 2 + 2 + 2 + 3 + 2 + 2 + 2 = 18

Transparent Access on Mindyard

Built for Learners & Educators Alike

Completely free for students. Educators & coaching institutes get 2 months free Pro trial to create tests.

FREE FOR STUDENTS

Practice & Learn Free for Students

Access unlimited practice questions, detailed step-by-step solutions, and exam study materials — completely free for students.

  • Unlimited mock tests & question bank practice
  • Full access to curated study library & formula notes
  • 100% free for students • No credit card required
2 MONTHS FREE TRIAL

Educators & Coaching Institutes

Create and conduct professional online assessments with AI question generation, tab proctoring, and comprehensive batch management.

  • AI question uploader & rapid test generator
  • Anti-cheating tab proctoring & performance reports
  • Full Pro access free for 60 days • No upfront commitment
Discover more resources

You may also like

Mock Tests

View All
  • SSC
  • intermediate
  • 1 hour
  • english, general awareness, general intelligence and reasoning, logical reasoning, quant

SSC CHSL Tier-1 Shift 1 2 July 2024 question paper with answers and detailed solutions. This practice paper covers questions from General Intelligence & Reasoning, General Awareness, Quantitative Aptitude, and English Language as asked in the SSC CHSL Tier-1 examination. Practice the SSC CHSL 2 July 2024 Shift 1 paper to understand the exam pattern, question types, difficulty level, and important topics for upcoming SSC CHSL exams.

  • SSC
  • intermediate
  • 1 hour
  • english, general awareness, general intelligence and reasoning, logical reasoning, quant

SSC CHSL Tier-1 Shift 2 held on 2 July 2024 question paper with answers and detailed solutions. This previous year paper covers General Intelligence & Reasoning, Quantitative Aptitude, General Awareness, and English Language. The SSC CHSL Tier-1 exam was conducted in CBT mode with 100 questions for 200 marks and a 60-minute duration.

Ask AI Tutor
5 left
Q1 View Question & Options
AI Tutor is solving this question...
Reading question context & options...
Ask your own doubt or choose a quick prompt: