A company keeps sales data in column B and wants to categorise sales into three levels:

Question

A company keeps sales data in column B and wants to categorise sales into three levels:

"High" if sales = 1000

"Medium" if sales = 500 but < 1000

"Low" if sales < 500

A student writes the formula in cell C2 as:

=IF(B2=1000,"High",IF(B2=500,"Medium","Low"))

Which of the following statements is/are correct?

1. A. The formula will always give an error.
2. B. The formula will give an error if B2 is blank.
3. C. The formula will give an error for non-numeric values in B2.
4. D. The formula will work correctly for all numeric values in B2. (Correct answer)

Correct Answer

Option D — D

Detailed Solution & Explanation

The correct answer is Option D.

Key Points

• the formula will work correctly for all numeric values in B2.
• The nested IF `=IF(B2>=1000,"High",IF(B2>=500,"Medium","Low"))` evaluates in order: if B2 is at least 1000 → 'High'; otherwise if at least 500 → 'Medium'; otherwise 'Low'.
• For any numeric value this returns the correct category.

Additional Information

• (A) It does not always error.
• (B) A blank B2 is treated as 0 in the comparison, so it returns 'Low' — no error.
• (C) A non-numeric text value would compare as greater in Excel's sort order and return 'High' — arguably a logic surprise, but still not an error message.

Exam Tip

• nested IFs evaluate left to right and stop at the first TRUE — always order the thresholds from highest to lowest, exactly as done here.