How to Find Area under a Curve in Excel – 2 Easy Ways
Did you ever go through the Area Under Curve (AUC) term?
It is basically a widely used concept in data sciences that helps in calculating the net area covered between the curve and axis. However, you cannot calculate it directly in Excel, but don’t worry, it is not that difficult.
In this post, you will learn how to find Area Under a Curve in Excel by using 2 simple ways.
- Calculate Area Under the Curve with Trapezoidal Rule in Excel
- Use Excel Chart Trendline to Get Area Under Curve
Calculating Area Under Curve by Trapezoidal Rule
The technique used in this method is the Trapezoidal rule similar to the region under the graph as a trapezoid and calculating the area. Below you can see the rule:
You can change the formula given above into a format that Excel calculates each trapezoid’s area because you have the data that makes charts.
You may start by adding a helper column in the data set.
You will see the adjacent columns easily help in copying the formula. The column will calculate the area of each trapezoid between data points (x).
Now, add the formula starting from the second row. The formula refers to the data points in the same (k) and the earlier row (k-1).
=(C6+C5)/2*(B6-B5)
Now, sum all the values of areas to calculate the net area under the curve.
That’s it!
Use Excel Chart Trendline to Get Area Under Curve
With Excel Chart Trendline, you can have an equation for the curve. The equation you will get can be used to find the area under the curve.
For instance, using the same dataset with multiple points on the X & Y axes in columns B & C, you can use the chart trendline to have the equation from which you get the area under the curve. Let’s follow the steps:
- First, select the chart that was plotted from:
- Range B4:C11 > and then click on the Insert tab.
- Later on, click on the Insert Scatter (X, Y) drop-down menu.
- In the end, choose Scatter with Smooth Lines and Markers option.
- Now, open the Chart Design tab.
- Click on the Add Chart Element drop-down menu from the Chart Layouts area.
- From the drop-down menu, open the Trendline option.
- Choose More Trendline Options.
- Click on the Plus (+) sign given on the right side of the chart.
- The Chart Elements section will pop up.
- Navigate the cursor from that section over the Trendline section and choose More Options.
- The Format Trendline window will pop up.
- Click on the Polynomial option from the Trendline Options.
- Check the Display Equation on the chart option.
- You will notice the polynomial equation is on the chart, which is:
y = 0.0155×2 + 2.0126x – 0.4553
- Now, you will have the definite integral of this polynomial equation:
F(x) = (0.0155/3)x^3 + (2.0126/2)x^2 – 0.4553x+c
- Add the value x = 1 in the definite integral. In cell F8, you can see the calculation:
F(1) = (0.0155/3)*1^3 + (2.0126/2)*1^2 – 0.4553*1
- Press ENTER key.
- Once again add the x = 10 in the definite integral. In cell F9 the calculation is as:
F(10) =(0.0155/3)*10^3 + (2.0126/2)*10^2 – 0.4553*10
- Press ENTER and the results will be in front of you.
- Now, calculate the difference between the calculations of F(1) & F(10) to find the area under the curve.
- Put the formula in cell F10:
=F9-F8
- Press ENTER to get the results.
That’s it!
Bottom Line:
In this post, you have learned how to find Area Under a Curve in Excel by using two simple ways. Hopefully, you will find these methods helpful as both of them are easy to follow.
