Mohammad Shah Miran
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 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
- 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
- SUM(O9 * P12,O16 * P19) → SUM function returns the product of O9*P12 and O16*P19.
