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