Logic (3): How to identify Red balls, made of Cast-Iron that weigh less than or equal to 1.0 Kg?

On IFs—(IF functions in MS EXCEL)

Question: 

How to identify Red balls, made of Cast-Iron that weigh less than or equal to 1.0 Kg?

Answer:

Prerequisite:

1.     Create  a new excel file.

2.     Identify the attributes of the ball like color, unique id, maker, weight, density, material etc.

3.     Take a ball enter it's id in the first column followed by it's attributes in the specific columns.

4.     Do it for all the balls one by one in a systematic manner.

5.     Save the excel file onto your local system.

      Now you have a digitized version of the balls. That is the prerequisite. Please find below, screen-shot of 4 columns with unique id of the ball, color of the ball, type of the material and weight, in sequence.

Solution:

1. The selection has to fulfill all three conditions, color being red, make being china and weight being less than or equal to 1.0 Kg. This can be formulated as per below screen-shot.

 2. Copy and paste the formula for all 20 balls. Copy and paste can be done in one stretch by copying once E2 (Ctrl C), selecting all the cells from E2 (Ctrl +Shift+Down Arrow) and pasting the formula onto selection E2 to E21 (Ctrl V). Refer below screen-shot.

 

 3. To visually identify the selection apply conditional formatting rules. Select the range E2 to E21, go to home tab, click “Conditional Formatting” followed by “Highlight Cells Rules” and“Equal To...” as per below screen-shot.

 

4. On clicking “Equal To...” you will get a dialog box, wherein you need to fill in the text that needs to be highlighted. In this case, you need to fill in “SELECT”. Refer below screen-shot.

 

5. Results are presented in the below screen-shot.

Date and Time of Writing: 02/02/2015 11:35 AM—14:12 PM