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

Excel is the most widely used tool when it comes to dealing with huge datasets. We can perform myriads of tasks of multiple dimensions in Excel. Sometimes, we need to calculate the peak area in Excel. In this article, I will show 2 effective methods to do so.


Download Practice Workbook

Download this workbook to practice while going through this article.


2 Effective Methods to Calculate Peak Area in Excel

This is the dataset that I am going to work with. The dataset is a graphical representation of the equation y=sinx ranging from 0 to pi. The graph clearly has a peak at sin (pi/2) where the corresponding y value is 1. We will calculate the area of this graph now.

how to calculate peak area in excel


1. Use Trapezoidal Rule to Calculate Peak Area in Excel

In this section, I will show how to use the trapezoidal rule to calculate peak area in Excel. In this method, we will divide the curve into some smaller trapezoids. Then we will calculate the area for those trapezoids individually and then add those small areas to get the final area.

Notice that the small segments are like trapezoids. The smaller the segments are, the more they resemble to a trapezoid. After that we will calculate the areas of these small trapezoids.

Steps:

  • Go to D5 and write the following formula
=(C5+C4)/2*(B5-B4)

how to calculate peak area in excel

Explanation:

  • C4 and C5 represent the lengths of the parallel lines here. These are along y-axis in the graph.
  • (B5-B4) represents the distance between the parallel lines. They are along x-axis in the graph.

  • Now press ENTER. Excel will calculate the area for a trapezoid.

how to calculate peak area in excel

  • After that, we will add the areas using the SUM function. Go to D18 and write the following formula
=SUM(D5:D16)

how to calculate peak area in excel

  • After that, press ENTER. Excel will calculate the final area.

Note: The result is not exactly 2 as it should be. That’s because we assumed the segments as trapezoids. But actually, they are not perfect trapezoids. That’s why the discrepancy results. Had the segments been smaller, they would have resembled trapezoid more and we would have got the result closer to 2.

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


2. Apply Definite Integral Rule to Calculate Peak Area in Excel

Now, I will use the method of applying the definite integral rule to calculate the area.

Steps:

  • First of all, we have to determine the integral form of sinx. The integration of sinx is –cosx. We will ignore the constant value since our final target is to determine the definite integration of sinx ranging from 0 to pi.

  • The range is from 0 to pi. So we will calculate -cos(0) and -cos(pi). The corresponding values are -1 and 1.

how to calculate peak area in excel

  • Our upper limit is pi and the lower limit is 0. So we will subtract -cos(0) from -cos(pi). Go to F9 and write down the formula
=F8-F7

  • Now press ENTER. Excel will calculate the area under the peak.

how to calculate peak area in excel

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


Things to Remember

  • The equation for the area of a trapezoid is A = (a+b)/2 * h.

Where, a and b are the length of two parallel sides

h is the distance between the parallel sides.

  • The integration of sinx is -cosx
  • The value for cos(0) and cos(pi) can also be calculated using the COS function. Note that the angles are in radian.

Conclusion

In this article, I have demonstrated 2 effective methods for how to calculate peak area in Excel. I hope it helps everyone. And lastly, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.


Related Articles

Akib

Akib

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo