Auc Area Under the Curve Made Easy

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
• Using the Trend line Equation for Area Under Curve

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

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

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)

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:

1. Select the chart
2. In the Chart Design tab, click on the Add Chart Element option

3. 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

4. In the 'Trendline Options', select Polynomial

5. Check the 'Display Equation on chart' option. This will show a polynomial equation of the trendline in the 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, x² will become x³/3 and x will become x²/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)*x³ + (2.1826/2)*x² – 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)]

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 Chart
• How 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

burrjamathand.blogspot.com

Source: https://trumpexcel.com/calculate-area-under-curve-excel/

Share this post