An analytics manager, Lawrence, wants to clean the following dataset using Excel's Conditional Formatting tool. What steps should he take if he wants to make sure that any suppliers that have more than 8% returns should not receive a quality rating of 5? Highlight those cells using red. A) Select D6:D17; Rule type: Formula, enter =AND(F6=5,H60.08); Color red. B) Select D6:F17; Rule type: Formula, enter =AND(F6=5,H60.08); Color red. C) Select D6:D17; Rule type: Formula, enter =AND(F6=5,H60.08); Color red. E) Select D1:D17; Rule type: Formula, enter =AND(F6=5,H6=0.08); Color red.

Question

An analytics manager, Lawrence, wants to clean the following dataset using Excel's Conditional Formatting tool. What steps should he take if he wants to make sure that any suppliers that have more than 8% returns should not receive a quality rating of 5? Highlight those cells using red. 
A) Select D6:D17; Rule type: Formula, enter =AND(F6=5,H6>0.08); Color red. 
B) Select D6:F17; Rule type: Formula, enter =AND(F6=5,H6>0.08); Color red. 
C) Select D6:D17; Rule type: Formula, enter =AND(F6=5,H6<0.08); Color red. 
D) Select D6:F17; Rule type: Formula, enter =AND(F6=1,H6>0.08); Color red. 
E) Select D1:D17; Rule type: Formula, enter =AND(F6=5,H6=0.08); Color red.

Quizplus · Accepted Answer

The correct selection is D6:D17 because we want to apply the conditional formatting only to the Quality Rating column. Rule type should be Formula because we want to add a condition. The formula should check if the quality rating is 5 and if the returns percentage is greater than 8%. If this is true, we want to highlight those cells in red. So, the correct formula is =AND(F6=5,H6>0.08).

An Analytics Manager, Lawrence, Wants to Clean the Following Dataset