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**.

## Download Practice Workbook

You can download and practice the dataset that we have used to prepare this article.

## 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).

## 3 Examples to Make a Decision Tree Algorithm in Excel

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 to have 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) →****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 theargument that checks if the value of the sum of*logical_test***O5**and**O16**cells is close to If so, the function will execute the (argument)*value_if_true***SUM(O9 * P12,O16 * P19)**otherwise it returns**NA**(argument).*value_if_false***Output → 533**

- See the output as given below.

- Type
**560**into the**L27**cell as this is the value of the**U26**cell

- Enter the following formula in the
**H21**cell.

`=MAX(L15,L27)`

Here we use the

**MAX**function to get the maximum value between**L15**and**L27**cells.- From the result, we can see the best decision we can get from our dataset is
**560**.

**Read More:****Artificial Intelligence in Excel (4 Examples)**### Example 02: Making Decision Tree for 6 Events

As from our dataset attached below, we have 2 decisions and 2 different chances for

**Product 2**. So our steps will be almost the same as in the previous example. Though, unlink the previous example, one more decision node will be added.**Step 01: Create Basic Outline of the Decision Tree**- Use
**CTRL+C**&**CTRL+V**shortcut keys and recreate the figure as given below in your Excel workbook.

**Step 02: Label Decision Tree and Input Values**- Input the corresponding data and label the chart accordingly.

- Enter the following formula in cell
**O33.**

`=MAX(S31,S36)`

- Move the value of cell
**T25**into cell**O26**.To do so just type**560**into the**O26**cell.

- Enter the following formula in cell
**O19**.

`=MAX(S17,S22)`

- Type the data in
**T10**cell,**410**into the**O12**cell.

- Now write the following formula into the
**K15**cell, as we did in the previous example.

`=IF(ABS(1-SUM(N9,N16))<=0.00001,SUM(N9 * O12,N16 * O19),NA())`

- Similarly, write the following formula into the
**K29**cell.

`=IF(ABS(1-SUM(N23,N30))<=0.00001,SUM(N23 * O26,N30 * O33),NA())`

- To find the maximum value of the given two decisions, write the following formula in the
**G22**cell.

`=MAX(K15,K29)`

- Now see the complete decision tree based on the given data as follows.

**Read More:****How to Perform Employee Scheduling Algorithm in Excel**### Example 03: Generating Decision Tree with Equal Branches

In our last example, we have chosen a dataset that contains two decision nodes and two chance nodes each. The process that we need to follow to accomplish this task is as same as we described previously. So to avoid redundancy we will make these steps a bit short.

- First, see the dataset we have provided below.

- Here is what the dataset suggests, we need to create two decision nodes and two chance nodes later. Upon completing the steps we mentioned earlier, you will get the output below.

**Read More:****How to Build Lottery Prediction Algorithm in Excel**## Practice Section

To practice decision tree algorithm in excel, we have provided a

**Practice**section on the right side of each sheet. Please make sure to do it yourself.## Conclusion

In this article, we have discussed how to make a

**decision tree algorithm in Excel.**As you have already understood, there is plenty of ways to do this task. So before going through a specific example, ensure that aligns with your work. Further, If you have any queries, feel free to comment below and we will get back to you soon. Also, you may follow our website,**ExcelDemy**, a one-stop Excel solution provider to explore more.## Related Articles