Calculate Area Between Two Curves Excel

In this article, we will learn how to calculate the area between two curves in Excel. We will divide the total steps into three sections:

1. Plotting curves from the equations
2. Calculating the intersecting point of two curves
3. Find the integral of the equations of two curves
4. Calculate the area between two curves

The theory you should know before jumping into the steps:

Section A. Plotting Curves from the Equations

Suppose you need to calculate the areas between two curves given below:

Step 1:  Take some random value of x to plot the curves. 

Step 2: Write the equations of curves in Excel and enter them into the cells C2 and D2

• For curve  y1

=B2^2+3

• For curve y2

=-(B2^2)+20

Where B2 contains the values of x,

Step 3: AutoFill the remaining cells in columns C and D to get the values for y1 and y2 which correspond to the values of x

Step 4: Select the range B1:D12, go to the  Insert tab

Step 5: From the chart section, choose the Scatter chart

The result is

Section B. Calculating the Intersecting Point of Two Curves in Excel

We will use Excel’s built-in option named “Goal Seek” to find the intersection point.

Goal Seek has three options

1. Set cell: The reference cell containing the formula
2. To value: The target value 
3. By changing cell:  the reference cell acts as a variable for the formula. Changing the value of the “By changing cell’s” reference cell, Goal Seek will check for which value the formula will result in the target value.

The Goal seek will run a check by tanking random values starting from the initially entered value to the the “By changing cell’s” reference cell and terminate if the formula entered in the “set cell’s” reference meets the target value.

Step 1: It’s clear from the chart we created in Section A, the intersection points will be any points closer to -2 and 2. 

So, enter -2 and 2 in cells C16 and C17, respectively.

Step 2: Enter the following formula in cell D16 and AutoFill to the cell D17

=-(C16^2)+20-(C16^2+3)       //returns the value of (y2-y1)

where, cell C16 contains the value of x  

Step 2: Select cell D16 and go to the Data tab.

Step 3: From the Forecast Section, Click on What-If Analysis 

Step 4: Click on the Goal Seek option.

Step 5: Enter the values in the fields of the Goal Seek window, as shown below. And click on OK

Note: At the intersection point, the value of y2-y1 (cell D16) will be zero for any particular value of x (cell C16). So, we set the target value in the Goal Seek to Zero

Step 6: After clicking Ok, we got the value of x for the  intersection point1

Similarly, find the value of x for the  intersection point2

Section C. Find the integral of the equations of two curves

Step 1: Enter the values of x at intersection points (obtained from section B) in cell C21, C22

Step 2: Theoretically the integration of the curve equation is 

Step 3: Write the integrated equations in Excel and input them in cells D21 and E21, respectively. 

=(C21^3)/3+3*C21

=-(C21^3)/3+20*C21

Where C21 contains the value of x for the intersection point1,

Step 4: Select D21, E21 and AutoFill to the row 22

Section E. Calculate the Area Between Two Curves

To find the enclosed area by curve y1 between X = -2.91545 and X = -2.91545, we need to find the difference between the values obtained in section C by substituting X = -2.91545 and X = 2.91545 in the integrated equation.

And similar calculation applicable to  find the enclosed area by curve y2

Step 1: Input the following formulas in cells E25 and E26, respectively

=D22-D21  //returns the enclosed area by curve y1

=E22-E21 // returns the enclosed area by curve y2.

Step 2: Finally to get the area enclosed by the curve y1 and y2, apply the  following formula in cell E27

=E26-E25