Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Calculate Second Derivative in Excel (2 Suitable Examples)

Looking for ways to know how to calculate the second derivative in Excel? We use the second derivative of a function for different purposes in our day-to-day life. Here, you will find 2 ways to calculate the second derivative in Excel.


Download Practice Workbook


2 Ways to Calculate Second Derivative in Excel

Here, you will find how to calculate the second derivative of a function by manually calculating and using Scatter Plot.


1. Manually Calculating Second Derivative

In the first method, we will show you how to calculate the second derivative of a function Manually.

Here, we have a function of x. The equation of the function is given below.

f(x)= sin(x^2)+10 

The 1st derivative of the function,

f’(x)= 2xcox(x^2)

Finally, the 2nd derivative of the function,

f’’(x)= 2cos(x^2)-4x^2sin(x^2)

The dataset provides some values of x. Now, we will show you how to manually calculate the second derivative for these values of x.

Manually Calculating Second Derivative in Excel

Follow the steps given below to do it on your own.

Steps:

  • Firstly, select Cell C5.
  • Then, insert the following formula.
=2*COS(B5^2)-4*B5*B5*SIN(B5^2)

Inserting Formula to Calculate Second Derivative in Excel

Here, in the formula, we inserted Cell B5 as x.

  • Now, press ENTER.
  • After that, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

  • Finally, you will get all the values of the second derivative of the function by calculating manually.

Read More: How to Calculate Derivative from Data Points in Excel


2. Using Scatter Plot to Calculate 2nd Derivative

We can also calculate the second derivative using Scatter Plot in Excel.

Here, we have a function of x. The equation of the function is given below.

f(x)= 2x^2+x

The 1st derivative of the function,

f’(x)= 4x+1

The dataset provides some values of x. Now, we will show you how to calculate the second derivative for these values of x using Scatter Plot.

Using Scatter Plot to Calculate Second Derivative in Excel


Step-01: Calculating First Derivative

Firstly, we will show you how to calculate the first derivative of the function in Excel. Go through the steps given below to do it on your own.

  • In the beginning, select Cell C5.
  • Then, insert the following formula.
=4*B5+1

Calculating First Derivative to Calculate Second Derivative in Excel

Here, in the formula, we inserted Cell B5 as x.

  • Now, press ENTER.
  • After that, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

Dragging Formula to Calculate first derivative in Excel

  • Finally, you will get all the values of the first derivative of the function.

Read More: How to Do Differentiation in Excel (With Easy Steps)


Step-02: Inserting Scatter Plot

Now, we will show you how to insert a scatter plot in Excel to calculate the second derivative of a function. Follow the steps given below to do it on your own.

  • Firstly, select Cell range B4:C11.
  • After that, go to the Insert tab >> click on Insert Scatter or Bubble Chart >> select Scatter.

Inserting Scatter Plot to Calculate Second Derivative in Excel

  • Now, a Scatter Plot will be inserted.
  • Then, click on the “+” sign to open Chart Elements.
  • Next, turn on Axis Titles.

  • After that, select the x-axis Title and type x.

  • Then, select the x.
  • Now, select Font Size as 14 and click on Bold.

  • Similarly, change the y-axis Title as f’(x) and format it.

  • Next, select the Chart Title.

  • After that, type Determining f’’(x) as the Chart Title.

  • Then, click on the “+” sign to open Chart Elements.
  • Next, click on Trendline >> select More Options.

Adding Trendline to Calculate Second Derivative in Excel

  • Now, the Format Trendline box will open.
  • After that, select the Display Equation on chart option.

  • Again, click on the “+” sign to open Chart Elements.
  • Next, turn off Gridlines to clearly see the equation.

  • Then, select the equation.
  • After that, set 14 as Font Size and click on Bold.

Here, we get y= 4x+1 as the equation of the Trendline.

Now, if we compare the equation with y= mx+c, we get m=4 which is the slope of the graph. We know that the slope of the 1st derivative and value of x of a function is equal to its second derivative. So, from the scatter plot, we get 4 as the second derivative of the function.


Step-03: Cross-Checking Value of Second Derivative Using SLOPE Function

Finally, we will show how to cross-check the value of the second derivative of a function using the SLOPE Function. Go through the steps given below to do it on your own.

  • Firstly, select Cell C15.
  • Then, insert the following formula.
=SLOPE(C5:C11,B5:B11)

Cross-Checking Value of Second Derivative Using SLOPE Function

Here, in the SLOPE Function, we used Cell range C5:C11 as known_ys and Cell range B5:B11 as known_xs.

  • After that, press ENTER.

  • Finally, you will get the value of the Slope using the SLOPE Function.

Now, you can see that the value of the slope is 4. So, the value of the second derivative of this function is 4.

Read More: How to Make First Derivative Graph on Excel (With Easy Steps)


Practice Section

In this section, we are giving you the dataset to practice on your own and learn to use these methods.

Practice Section


Conclusion

So, in this article, you will find 2 ways to calculate the second derivative in Excel. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here. And, visit ExcelDemy for many more articles like this. Thank you!


Related Articles

Arin

Arin

Hello, I'm Arin. I graduated from Khulna University of Engineering and Technology (KUET) from the Department of Civil Engineering. I am passionate about learning new things and increasing my data analysis knowledge as well as critical thinking. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo