Calculate Area Under Curve in Excel (2 Easy Ways)

Area Under Curve (AUC) is a widely used concept in data sciences in multiple fields.

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.

Dataset and line chart for area under curve

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.

Area Under Curve Chart Logic explained

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

A = (a+b)/2 * h

where:

  • a is the base lengh of one side
  • b is the base length of the other side
  • h is the height
Trapezoid Formula

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:

=((B2+B3)/2)*(A3-A2)
Formula to get trapezoid area for each value

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.

SUM formula to get the total area under curve

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.

Dataset and line chart

Below are the steps to get the Trendline equation for our dataset:

  1. Select the chart
  2. In the Chart Design tab, click on the Add Chart Element option
Click on plus icon add chart element icon
  1. Hover the Cursor over the Trendline option, and in the options that show up, click on ‘More Trendline Options’. This will open the Format Trendline pane
Click on More Options in Trendline
  1. In the ‘Trendline Options’, select Polynomial
Select the Polynomial option
  1. Check the ‘Display Equation on chart’ option. This will show a polynomial equation of the trendline in the chart
Check the display equation on chart

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

y = 1.0038x2 + 2.1826x - 1.85

So the definite integral of this equation would be

y = (1.0038/3)*x3 + (2.1826/2)*x2 - 1.85x + c

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:

=(1.0038/3)*(1^3) + (2.1826/2)*(1^2) - 1.85*1 

and f(10) can be calculated using the below formula:

  =(1.0038/3)*(10^3) + (2.1826/2)*(10^2) - 1.85*10  

To get the area under the curve, we need to find the difference between these two values [f(10) – f(1)]

Formula to calculate the area under curve in Excel

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:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Picture of Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

2 thoughts on “Calculate Area Under Curve in Excel (2 Easy Ways)”

  1. Yes, good job – especially with the polynomial. Maybe a method of getting the exact area would be to average up each pair of Y axis values (1st pair would be the average of 0 and 3) and average those averages, then multiply with the x axis length (10 in this case). The exact area for this set is 428.

    Reply
  2. Good job. clear methods. Both correspond to integrating the curve graphical and analytical. Just a note: for the first ,method if you have negative values you need to use absolute values

    Reply

Leave a Comment

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster