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

Get FREE Advanced Excel Exercises with Solutions!

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.

How to Make a Decision Tree Algorithm in Excel

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.

Draw the Necessary Shapes

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

Create a Decision Tree for 4 Events

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

decision tree algorithm in excel


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.

decision tree algorithm in excel

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

=U21

decision tree algorithm in excel

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

=U16

decision tree algorithm in excel

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

    decision tree algorithm in excel

    • 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())

    decision tree algorithm in excel

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

    decision tree algorithm in excel

    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.

    How to Make a Decision Tree Algorithm in Excel

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

    decision tree algorithm in excel

    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.

    Do it yourself decision tree algorithm in excel


    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

Mohammad Shah Miran
Mohammad Shah Miran

Miran is a highly motivated individual with a strong educational background in engineering. He is interested in technology and passionate about creating engaging and informative content. After graduation, Miran decided to pursue a career in content development and has been working in the field for some time. He is eager to continue learning and growing as a professional.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo