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

Logic (5): How to calculate price of Black balls made of Hard Wood in China and White balls made of Cast Iron in India?

On CONCATENATE—(CONCATENATE functions in MS EXCEL)

Question: 

How to calculate price of Black balls made of Hard Wood in China and White balls made of Cast Iron in India?

Answer:

Prerequisite:

1.     Create a new excel file.

2.     Identify the attributes of the ball like unique id, colour, 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.     Note down the pricing detail of the balls.

6.     Save the excel file onto your local system.

      Now you have a digitized version of the balls (Ball Matrix) and its pricing details (Pricing Matrix). That is the prerequisite. Please find below screen-shot of 5 columns with unique id of the ball, color of the ball, type of the material, maker of the ball and weight in sequence along with the pricing detail.

Solution:

1. Both in the pricing matrix as well as in the ball matrix, details of the attributes to be accounted are in separate cells, whereas pricing values are unique for the combination of two different attributes (Material and Maker). Hence additional column is introduced to combine attributes in two different cells into one cell so that formula can be applied directly. In the pricing matrix, column K and L are combined. In the ball matrix column C and D are combined. Since this combination is required for generating uniqueness, we can either give a new value or add text values of both the attributes. Please find screen-shot below:

  

2. Once the attributes that create pricing variations are concatenated they can be looked up (Refer Logic (4)) for the right combination thus pricing of that combination can be easily calculated. Refer below screen-shot. 

 Here, AND function is used to identify the right combination, OR to select either of the right combinations, LOOKUP to apply the pricing value and IF to calculate the actual pricing. 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 G2 (Ctrl C), selecting all the cells from G2 (Ctrl +Shift+Down Arrow) and pasting the formula onto selection G2 to G21 (Ctrl V). Refer below screen-shot. 

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 screen-shot.

Date of Writing: 02/11/2015.

Logic (4): How to calculate the price of each Black and White ball made of Cast Iron?

On LOOKUPs—(LOOKUP functions in MS EXCEL)

Question:

How to calculate the price of each Black and White ball made of Cast Iron?

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. Note down the pricing detail of the balls.

6. Save the excel file onto your local system.

      Now you have a digitized version of the balls and it’s pricing details. 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 along with the pricing detail.

Solution:

1. Pricing is calculated for Black and White balls made of Cast Iron, hence this combination is identified first and then pricing value is applied on the weight of the ball. Refer screen-shot below.

Other than the LOOKUP function, the above formula includes AND function to group ball color (Black and White) and material type (Cast Iron). OR function is used to choose either Black or White of all colors. IF function is used to apply the condition for the grouping thus identified. Columns and rows of the VLOOKUP table is frozen by using $ this is done to ensure that look up function uses the same table at all cells especially when you copy paste the formula.

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.

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

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

Date and Time of Writing: 02/03/2015 9:35 AM—12:26 PM

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

Logic (2): How much do all the Red balls made of Cast Iron weigh?

On Summing—(SUM functions in MS EXCEL)

Question: 

How much do all the Red balls made of cast iron weigh?

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 the unique id of the ball, color of the ball, type of the material and it's weight, in sequence.

Solution:

1. Sum the weight of all the balls using the function “SUM”. Select the region that needs to be summed. Refer screen-shot below.

 2. Sum the weight of the RED balls alone using the function “SUMIFS”. The region to be summed will be the same, in addition select the criteria region and apply the selection criteria in a separate cell. Refer screen-shot below.

 3. The same “SUMIFS” function can be used to sum the weight of all the Red balls made of cast iron alone, by including additional criteria. Refer screen-shot below.

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

 

 Date and Time of Writing: 02/01/2015 10:30 AM—12:23 PM

Logic (1): How to count total number of Black balls made in China?

On Counting—(COUNT functions in MS EXCEL)

Question: 

How to count total number of Black balls made in China?

Answer:

Prerequisite:

1. Create  a new excel file.
2. Identify the attributes of the ball like colour, 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 in the basket. That is the prerequisite. Please find below screen-shot of 2 columns, Column A with unique id of the ball and Column B with color of the ball.

Solution:

1. Count the total number of balls using the function “COUNTA” and select the region that needs to be counted. Refer screen-shot below.

2. Count the total number of Black balls using the function “COUNTIF”. Select the region that needs to be counted from and apply the selection criteria either by keying the criteria in a separate cell or in the formula itself. Refer screen-shot below.

3. Count the total number of Black balls made by China using the function “COUNTIFS”. Select the region that needs to be counted from and apply the first selection criteria, similarly select the next criteria region and apply the second criteria. First is to select Black balls, on which you apply the criteria “Made by China” to get the count of Black balls made by China. Refer screen-shot below. Column C has details of the Maker.

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

Date and Time of Writing: 01/28/2015 13:30 PM—16:15 PM