Excel simplifies calculations with its vast arsenal of functions, but there is no Excel function for calculating the area under a plot. However, this does not mean that you cannot use Excel to calculate the area under the plot.
Excel is more than just functions, and the Autofill handle alone can make it very easy to calculate the area under a plot. In this article, we are going to calculate the area under a plot in Excel using mathematical formulas. Read on to learn what information the area under a plot can give you and how to calculate it.
How to Calculate Area Under Plot in Excel
As a concept, the area under the plot is roughly the x-axis multiplied by the y-axis. However, there is no Excel function that calculates the area under the plot.
To calculate the area under the plot, you have to calculate the area between each two values separately and then add them together to get the total area. This may seem a bit daunting, but don’t be discouraged. It’s easier than it looks.
The area under a plot consists of triangles, trapezoids, and rectangles. Once you’ve calculated the area for each of these shape types, you can use the Autofill handle in Excel to automatically calculate the area of the remaining shapes of that type.
As an example, suppose we have the speed of a car at a specific time. If you make a chart for this data, the area under the plot will show the distance traveled by the car. This is because the x-axis is seconds, and the y-axis is meters per second. If you multiply these two together, you will get meters or the distance covered. Let’s start by making the cart.
1. Charting
In this spreadsheet, we have the speed of a car at specific points in time. The car starts moving at time zero, so its speed at that time is 0. Area charts are the best way to visualize the area under a plot, so let’s make one.
If you are a complete beginner in creating charts in Excel, you can read our guide on how to create charts in Excel to get started quickly.
2. Customizing the Chart
Your chart is accurate now, and it gives a good measure of the area under the plot, but it’s still not good enough. Before calculating the areas, let’s make some adjustments to the chart so that the size and measurements of the areas are more specific.
You now have vertical gridlines on your graph, which makes the graph easier to read. Unfortunately, the gridlines go behind the region, and the region isn’t transparent, so right now, you can’t see the gridlines at the bottom of the plot. This can be easily fixed.
Now your area chart is ready for further analysis. With these adjustments you’ve just made, it should be much easier to read the height and base of the various shapes in the field.
3. Area calculation
If you take a look at the geometry of the example chart, it’s actually made up of a triangle, a set of trapezoids, and a rectangle. So if you calculate the area of these shapes and add them, you will get the total area under the plot.
Let us now write the formula. The first segment is going to be the field in time zero, which is zero naturally.
Select the cell where you want the area for the first segment to appear. In this example, that would also be the distance traveled, so we’re going to use column C for this purpose.
For the first segment, input 0. This is because the field at time zero is also zero. Now let’s calculate the area for the next section.
This time, the segment is a quadrilateral. The area of a trapezoid is the sum of the two bases, multiplied by the height, and then divided by two. The bases are B3 and B4, and the heights are A4-A3. Again, when the formula contains the area of the segment, it sums it with the previous cell to get the total area so far.
Grab the fill handle and drop it on the cells below. This will automatically calculate the area for the cells below, since they are all trapezoids.
If you’re thinking of a rectangle, the area of a rectangle can also be calculated using the trapezoid area formula. This is because the area of a rectangle is the length multiplied by the width.
If you think of a rectangle as a trapezoid, the two bases together make up 2x the width, while the height is actually the length. Finally, dividing by two makes multiplying by two zero, and you get the area of the rectangle.
Calculate Area Under Plot with Excel
The area under the plot provides valuable information. Although there is no Excel function to calculate this directly, you can use Excel for this purpose. All you have to do is break down the area into simple shapes and calculate the area of those shapes.
Graphs and charts are wonderful visualizations that can make analysis and calculations a lot easier if you use them properly. There are many more charts besides area charts, and learning to use them will definitely give you an advantage in Excel.
