Logic (6): How to group Black and Red balls made of Cast Iron in China and India; White balls made of Cast Iron and Hardwood in China and Brazil; Black and Red balls made of Hardwood in Brazil, China and India?

On Nested IFs—(Nested IF functions in MS EXCEL)

Question: 

How to group Black and Red balls made of Cast Iron in China and India; White balls made of Cast Iron and Hardwood in China and Brazil; Black and Red balls made of Hardwood in Brazil, China and India?

Answer:

Prerequisite:

1.     Create a new excel file.

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

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

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

5.     Group unique attributes of the ball in a separate table.

6.     Save the excel file onto your local system.

      Now you have a digitized version of the balls (Ball Matrix) and its unique details (Attributes Matrix). That is the prerequisite. Please find below screenshot of 4 columns with unique id of the ball, the color of the ball, type of the material and maker of the ball along with the attributes listed in columns J, L and N. 

 Solution:

1. The requirement is divided into four groups as follows:

• Group 1—Black and Red balls made of Cast Iron in China and India.
• Group 2—White balls made of Cast Iron and Hardwood in China and Brazil.
• Group 3—Black and Red balls made of Hardwood in Brazil, China and India.
• Group 4—Ungrouped balls.

Apply conditions in the formula to group the balls as per the requirement. Use Nested IF as per the below screenshot. 

Here, AND function is used to identify the right combination, OR to select either of the right combinations, and nested IF function to group balls by their criteria. Columns and rows are frozen by $ wherever it is applicable.

3. 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). As you have seen in the above screenshot.

4. Conditional formatting is applied to visually differentiate the balls that belong to the condition. To know about conditional formatting, please refer the posting Logic (3).

5. Results are presented in the below screenshot. 

Date of Writing: 02/20/2015