How to Make a Decision Tree Algorithm in Excel – 3 Easy Examples

“Product Price of ABC Beverage Limited” is the sample dataset.

Example 1 – Creating a Decision Tree for 4 Events

Step 1: Construct Essential Shapes

Specific shapes are necessary to draw a decision tree:

• Go to Insert > Shapes >Oval.

• The oval shape is labelled as Chance Node in the decision tree.

• Choose Blue on the Font ribbon to fill the shape .

• Draw a Rectangular box below the Oval shape and fill it with Blue. Label it  Decision Node.

This is the output.

• Fill another cell with Blue accent 5 lighter 40% and label it “Decision to Be Made”.

• Go to Insert > Shapes > Line command and draw a line.

This is the output.

• Fill a box with Gold Accent 4, 60% Lighter and label it probability box.

This is the output.

Step 2: Make a Basic Outline of the Tree

• Use CTRL+C & CTRL+V to recreate the figure.

Step 3: Label & Input Values in the Decision Tree

• Input the corresponding value of the dataset in the recreated tree.

• Enter the following formula in T22 to return event value 820.

• Enter the following formula to bring the value of U16 into T17.

• Insert the following formula in L15.
• =IF(ABS(1-SUM(O9,O16))<=0.00001,SUM(O9 * P12,O16 * P19),NA())

 Formula Breakdown:

• SUM(O9 * P12,O16 * P19) → The SUM function returns the product of O9*P12 and O16*P19.
  • Output → 533
• ABS(1-SUM(O9,O16))→ The ABS function returns the absolute value of 1-SUM(O9,O16)
  • Output → 0
• IF(ABS(1-SUM(O9,O16))<=0.00001,SUM(O9 * P12,O16 * P19),NA()) → checks whether a condition is met and returns one value if TRUE and another value if FALSE. Here in the IF function, ABS(1-SUM(O9,O16))<=0.00001 is the logical_test argument that checks if the value of the sum of O5 and O16 cells is close to If. So, the function will execute the (value_if_true argument) SUM(O9 * P12,O16 * P19) or return NA (value_if_false argument).
  • Output → 533