Nazmul Hossain Shovon
In this tutorial, I am going to share with you step-by-step procedures to make first derivative graph on excel. Also, throughout this tutorial, you will learn some valuable functions and techniques which will be helpful in other excel related tasks. In the last section, we will see how to find the slope of a given dataset.
Download Practice Workbook
You can download the practice workbook from here.
Step-by-Step Procedures to Make First Derivative Graph on Excel
We have taken a concise dataset for this tutorial to explain the steps clearly. This dataset has 5 columns and 6 rows. The main inputs for this dataset are the Price and the Demand columns. Here, the Price will be in Dollars and the Demand will be in the number of units.
Step 1: Inserting Input Data
In this first step, we will insert the necessary data to calculate the first derivative and generate the graph on excel. Let us see how to do this.
- First, go to cell B5 and insert the Price data as in the image below in cells B5 to B10.
- Then, format the cells in column B as Accounting.
- Similarly, insert the Demand data in cells C5 to C10.
Step 2: Creating Variations Columns
In order to calculate the first derivative, we need to find the variation in the Price and Demand data. For this, we will use some basic formulas. Follow the steps below.
- To begin with, go to cell D5 and type 0.
- Next, type in the following formula in cell D6:
=B6-B5- Now, press the Enter key and copy the formula to the cells below.
- As a result, this will give the Price Variation.
- Similarly, insert the below formula in cell E6:
=C6-C5- Then, press Enter and copy this formula to the below cells.
Read More: How to Do Differentiation in Excel (With Easy Steps)
Step 3: Finding First Derivative
Once we have calculated the variations, now we can proceed to find the first derivative using another simple formula. Let us see how we can do this.
- To begin this step, type 0 in cell F5.
- Then, insert the following formula in cell F6:
=E6/D6- Now, press Enter and copy this formula to the cells below using Fill Handle.
Read More: How to Calculate Derivative from Data Points in Excel
Step 4: Generating First Derivative Graph
Now, as we have all the required data, we can proceed to generate a graph. In Excel, there are many options to create a graph, We will the Scatter plot to clearly visualize the curve.
- First, select the cells from B5 to B10 and F5 to F10 holding the Ctrl key.
- Then go to the Insert tab and from the Scatter drop-down, select Scatter with Smooth Lines and Markers.
- Consequently, this will generate the derivative graph reflecting the change in Demand with respect to Price.
Read More: How to Calculate Second Derivative in Excel (2 Suitable Examples)
Derivative Function in Excel to Find Slope
The SLOPE function in excel returns the slope of a regression line based on some y and x values. This slope is actually the measure of the steepness of the data variation. In Mathematics, we use the formula as rise over run which is the change in y values divided by the change in x values.
Steps:
- First of all, navigate to cell C10 and type in the following formula:
=SLOPE(C5:C9,B5:B9)
- Finally, press the Enter key and you will get the slope for the input data.
Things to Remember
- If there is only one set of points, the SLOPE function will return #DIV/0!
- If the number of y and x values are not equal, the formula will return #N/A.
- To copy a formula to other cells, you can double-click on the Fill Handle instead of dragging.
Conclusion
I hope that you were able to apply the steps that I showed in this tutorial on how to make first derivative graph on excel. Although we have worked with a small dataset, you can follow these steps to generate graphs from more large datasets. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.
