Md. Abdur Rahim Rasel
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.
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,
- 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.
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.
- 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.
Step 2:
- Further, autoFill the trapezoidal rule to the rest of the cells in column D which has been given in the below screenshot.
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.
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
- 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.
- 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
- 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.
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.
