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.