How to Calculate Area Under Scatter Plot in Excel (2 Easy Methods)

In real life, sometimes we need to calculate the area of the scatter portion of land. Microsoft Excel can easily calculate the area under a scatter plot chart. This is a time-saving task also. Today, in this article, we’ll learn two quick and suitable ways to calculate the area under a scatter plot chart in Excel effectively with appropriate illustrations.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


2 Suitable Ways to Calculate Area Under Scatter Plot in Excel

Let’s say, we have a dataset that contains information about several abscissae and the coordinate of some points are given in columns B, and C respectively. Using these points will create a scatter plot chart in Excel. Hence, we will calculate the area under the scatter plot chart. Here’s an overview of the dataset for our today’s task.

excel calculate area under scatter plot

Create a Scatter Chart:

In this portion, we will create a scatter chart to calculate the area under the scatter plot chart. To do that, firstly we will make a dataset to create a scatter plot chart. Our dataset is,

excel calculate area under scatter plot

  • After that, we will create a scatter chart. To do that, select the table range B4 to C14. Hence, from your Insert tab, go to,

Insert → Charts → Scatter

  • As a result, you will be able to create a scatter plot chart which has been given in the below screenshot.

excel calculate area under scatter plot


1. Use Trapezoidal Rule to Calculate Area Under Scatter Plot in Excel

For instance, we will use the trapezoidal rule to calculate the area under the scatter plot chart. From our dataset, we can easily do that. To calculate the area under the scatter plot chart, firstly, you have to know the trapezoidal rule. The trapezoidal rule is,

Area = (½)*(short base + long base)*(height)

Using the above trapezoidal rule, we will calculate the area under the scatter plot chart. Let’s follow the instructions below to calculate the area under the scatter plot chart!

Step 1:

  • First, select a cell to apply the trapezoidal rule. From our dataset, we will select cell D5 for the convenience of our work.

Use Trapezoidal Rule to Calculate Area Under Scatter Plot in Excel

  • Hence, write down the trapezoidal rule in that cell. The trapezoidal rule is,
=(1/2)*(C5+C6)*(B6-B5)

  • After that, simply press Enter on your keyboard. As a result, you will get 6.5 square units as the output of the trapezoidal rule.

Use Trapezoidal Rule to Calculate Area Under Scatter Plot in Excel

Step 2:

  • Further, autoFill the trapezoidal rule to the rest of the cells in column D which has been given in the below screenshot.

Use Trapezoidal Rule to Calculate Area Under Scatter Plot in Excel

Step 3:

  • Now, we will calculate the total area under the scatter plot. To do that, we will apply the SUM function. Hence, again, select cell D15 and write down the SUM function in that cell. The SUM function is,
=SUM(D5:D14)
  • Where D5:D14 is the data range of the SUM function.

  • After that, simply press Enter on your keyboard. As a result, you will get the total area under the scatter plot chart, and the area is 197.5 square units.

Use Trapezoidal Rule to Calculate Area Under Scatter Plot in Excel

Read More: How to Calculate Area of Irregular Shape in Excel (3 Easy Methods)


2. Apply Chart Trendline to Calculate Area Under Scatter Plot in Excel

In this method, we will use the Chart Trendline in the scatter plot chart and then calculate the area under the scatter plot chart. This is an easy and time-saving task also. From our dataset, we can easily do that. Let’s follow the instructions below to calculate the area under the scatter plot chart!

Step 1:

  • First, create a scatter plot chart. Now, select the scatter plot chart. Hence, from the Chart Design tab, go to,

Chart Design → Add Chart Element → Trendline → More Trendline Options

Apply Chart Trendline to Calculate Area Under Scatter Plot in Excel

  • After that, a Format Trendline window will appear in front of you. From that window, firstly, select the Polynomial Secondly, check the Display Equation on Chart option.

Step 2:

  • Further, we will calculate the area under the scatter plot chart using this polynomial equation. We’ll calculate the definite integral of the polynomial equation. The definite integral equation is,

Y = (⅓)*X3 – X2 + 3X

  • Now, we will apply the above formula for x = 1, we will get the output of the formula in cell C16. The output is 2.33333333.

Apply Chart Trendline to Calculate Area Under Scatter Plot in Excel

  • Further, we will apply the above formula for x = 10, we will get the output of the formula in cell C17. The output is 263.33333333.

  • After that, we will get the area under the scatter plot chart by subtracting C16 from C17. The formula is,
=C17-C16

Apply Chart Trendline to Calculate Area Under Scatter Plot in Excel

  • Hence, simply press Enter on your keyboard. As a result, you will get the total area under the scatter plot chart, and the area is 261 square units.

Apply Chart Trendline to Calculate Area Under Scatter Plot in Excel

Read More: How to Calculate Peak Area in Excel (2 Effective Methods)


Things to Remember

👉 #N/A! error arises when the formula or a function in the formula fails to find the referenced data.

👉 #DIV/0! error happens when a value is divided by zero(0) or the cell reference is blank.

👉 In Microsoft 365, Excel will show the #Value! Error if you don’t select the proper dimension. The #Value! error occurs when any of the elements of the matrices is not a number.


Conclusion

I hope all of the suitable methods mentioned above to calculate the area under the scatter plot will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo