While the concept of Area Under Curve is quite simple – which is to calculate the total area covered between the curve/line and the axis – there is no direct way to calculate this in Excel. But it isn’t too difficult as well! In this short tutorial, I will show you two ways you can use to calculate Area Under Curve in Excel. So let’s get started!
Formula to Calculate Area Under Curve in Excel
As I mentioned, there is no direct formula to calculate AUC, but we can calculate it using a helper column and a simple formula. Below I have a dataset and I have created a line chart using this data. While I can not calculate the area under the curve for this whole chart, I can break it down into small trapezoids (as shown below), and then calculate the area of each trapezoid. In the above chart image, I have broken each interval into a separate section (indicated with a different color), and each of these sections resembles a trapezoid. While I can not calculate the area under the line curve directly, I can calculate the area of these individual trapezoids. Once I have the area for all of these trapezoids, I can just add them all. This will give me a very close value of the total area under the chart. Below is the formula to calculate the area of a trapezoid where:
a is the base lengh of one sideb is the base length of the other sideh is the height
Below is the formula that I can use (in the adjacent column) to calculate the area of a trapezoid in the chart for my dataset: Apply the above formula for all the cells in the column (except the last one). Now that I have the trapezoid value (which is also the area under the curve value) for the x-axis intervals in the chart, I can now add all these to get the overall area under the chart. I can use a simple SUM formula to do this. So this is how I can calculate the total area under the curve for a simple line chart. Note that the result of this method would be very close to the actual area under curve value, it could be slightly off. This is because the area between the line and axis is not a perfect trapezoid, but close to it.
Using the Trend line Equation for Area Under Curve
Let me show you another method to calculate the Area Under Curve (AUC) for a chart in Excel. This method uses adding a trendline in the Excel chart and then utilizing the equation that the Excel chart automatically creates for the trendline. Below is a dataset for which I have created the line chart, and now I want to calculate the area under the curve for this chart. Below are the steps to get the Trendline equation for our dataset: Now that we have the polynomial equation, we can use this to calculate the area for this line chart. But before doing that, there is one more step that you need to do. You need to get the definite integral for the polynomial equation. In my case, the equation is So the definite integral of this equation would be While I am not an expert in calculus, based on my limited understanding, you can convert an equation to a definite integral by increasing the power of x by 1, and diving it by that same value power. For example, x2 will become x3/3 and x will become x2/2 and any constant (such as 1.85) will become 1.85x Now that I have the equation, I can calculate the area under the curve by finding the value of f(10)-f(1) In our example, I have the equation – f(x) = (1.0038/3)*x3 + (2.1826/2)*x2 – 1.85x + c So F(1) can be calculated using the below formula: and f(10) can be calculated using the below formula: To get the area under the curve, we need to find the difference between these two values [f(10) – f(1)] You will notice that the value is very close to the one we got from our previous method (by using the trapezoid formula). While both these methods work fine, remember these are still a very close approximation of the area under the curve and are not accurate. So while Microsoft Excel does not have a direct way to calculate the area under the curve, you can use any of these two methods to do it. I hope you found this tutorial useful! Other Excel tutorials you may also like:
How to Create an Area Chart in Excel (explained with Examples)How to Find Slope in Excel? Using Formula and ChartHow to Make a Scatter Plot in Excel (XY Chart)How to Add a Secondary Axis in Excel Charts (Easy Guide)How to Make a Bell Curve in Excel
