Masum Mahdy
This article will illustrate how to calculate area under a curve using integration in Excel with instructional images and detailed discussion.
Download Practice Workbook
You can download the following practice workbook for your exercise or any kind of use.
Necessary Formulas to Find the First Integral of Polynomial Trendline Equation in Excel
To find area under curve in Excel, we use the trendline equation generated by Excel. Polynomial trendline type is the best in this case.
The following is a generic equation of a polynomial line.
The generic equation for the first integral is-
For a 2nd degree polynomial, the formulas will be-
and,
Where I1 is a constant.
For a 3rd degree polynomial, the formulas will be-
and,
Where I2 is a constant.
Steps to Calculate Area Under Curve Using Integration in Excel
The following dataset shows some coordinates of a random curve.
Now you will learn how to find the area under the curve these coordinates create step-by-step.
📌 Step 1: Set Data Properly and Create Scatter Chart
- Set your data in order and select any cell of your data. Then go to the Insert tab and from the Charts group, select a suitable chart type.
- Here we have selected the Scatter with Smooth Lines and Markers option.
- As a result, a graph like the following will appear.
📌 Step 2: Enable Trendline and Its Equation
- Now, click on the Chart Area.
- Then click the Chart Elements button.
- Then form the Trendline dropdown, and select More Options.
The Format Trendline window will appear at the right.
- Click on the Polynomial button. Then mark the Display Equation on chart checkbox.
The trendline equation will appear on the chart area. It is as follows:
Y = 7.331X2 + 19.835X + 82.238
📌 Step 3: Find the First Integral and Calculate Area Under Curve
- Create a table like the following and insert the following formula in cell F24.
=F23-F22
- Now, copy the trendline equation and paste it into cell E19.
- Calculate the first integral with this equation using the formulas we have discussed earlier in this article.
- The generic formula for this 2nd-degree polynomial-first integral will be as follows.
Hence, the first integral of Y is-
Y1 = 7.331X3/3 + 19.835X2/2 + 82.238X+C
- Now, input the following formula (or match it with your data) in cell F22 and copy it with the fill handle in cell F23.
=7.331*E22^3/3+19.385*E22^2/2+82.238*E22- As we see, the area is there in cell E24.
💬 Note:
This area under the curve is with respect to the X axis. If you want to find the area under the curve with respect to the Y axis then just flip the data horizontally, switch the axes, and apply all those steps described already.
Read More: How to Make First Derivative Graph on Excel (With Easy Steps)
How to Calculate Area Under Curve in Excel Using Trapezoidal Rule
Doing integration is not an easy task for those who do not have basic knowledge of calculus. Here we come up with an easier way to find the area under any curve, the Trapezoidal Rule.
📌 Steps:
- First off, put the following formula in cell D5 and hit the Enter button.
=((C6+C5)/2)*(B6-B5)- Now drag the fill handle icon to cell D14. Leave the last as it is.
- Insert the following formula in cell D16.
=SUM(D5:D15)- Press the Enter key.
- You will see the output!
💬 Note:
More coordinates in the same range with smaller intervals will give a more accurate result.
Read More: How to Do Trapezoidal Integration in Excel (3 Suitable Methods)
Conclusion
So we have discussed how to calculate the area under a curve in Excel using integration. Moreover, we also have shown the use of the trapezoidal rule. Please leave us your feedback in the comment box.
For more such articles, visit our blog ExcelDemy.
