How to Make Decision Tree Algorithm in Excel (3 Easy Examples)

Suppose you are in a situation where you need to make a certain decision based on some specific condition. This is a probabilistic analysis thus you have to possess a considerable amount of mathematical knowledge to obtain the task. Your primary goal should be to maximize accuracy thus you have to rely on various algorithms if you want to avoid mathematical calculation. This article will demonstrate to you how to make a decision tree algorithm in Excel.

What Is Decision Tree Algorithm?

A decision tree employs a tree-shaped model to represent options and their potential outcome of specific events, cost minimization, and thus utilization of limited resources to gain maximum output. In this method, an algorithm is built that solely uses conditional control statements.
Three main components of a decision tree can be summarized as decision nodes (available choices), chance nodes (which denote probability), and end nodes (which represent the outcomes).

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

Let’s assume we have a dataset, namely “Product Price of ABC Beverage Limited”. You can use any dataset suitable for you.

Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.

Example 01: Creating Decision Tree for 4 Events

Now we will create a decision tree for four different events. Here we have attached a dataset that describes two decision compressing of four different events.

Step 01: Construct Essential Shapes

To draw a decision tree, first of all, we need to have some specific shape. Look at the image attached below.

Now, those shapes which we will use later to make our work easy, can be generated as below.
Go to Insert > Shapes >Oval.

This oval shape will denote the Chance Node in the decision tree. So, make sure to label it as like given below.

We need to fill the shape to make it more discernible. So, choose the Blue color from the Font ribbon box.
Visual instruction has been made below the figure.

Similar manner, draw a Rectangular box below the Oval shape, and fill it with Blue Color, afterward label it as the Decision Node.

See the output as given below.

Make another cell with a Blue accent 5 lighter 40% color and label it as “Decision to Be Made”

From Insert > Shapes > Line command, draw a line as We will use this connector to make a bridge between the decision tree node.

So the figure will look like the one below.

The last thing we need to do is, fill a box with Gold Accent 4, 60% Lighter color and referring it as a probability box.

Finally, the image attached below depicts the required shapes that we will use later to draw the tree. Though this is not essential to draw those shapes at first, this is a shortcut way to do the task more efficiently.

Step 02: Make Basic Outline of the Tree

Use CTRL+C & CTRL+V shortcut keys and recreate the figure as given below in your Excel workbook.

Step 03: Label & Input Values in the Decision Tree

Now it’s time to input our data into the tree. Input the corresponding value of the dataset in the recreated tree. The background color of the individual cell will surely help you to track the data.

Enter the following formula into the T22 cell to bring the value of event value 820 in this cell.

=U21

Similarly, bring the value of the U16 cell into T17 by inputting the following formula.

=U16

Insert the following formula in the L15 cell.

=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) otherwise it returns NA (value_if_false argument).
- Output → 533