# Excel Curves- Area under the curve

1. ## Excel Curves- Area under the curve

I use chromotography(GPC) in my research. This gives bell shaped curve. To compare two cuvres, I need area under the curve. Can any one teach me the way of calculating area under the curve in excel worksheet?(The curve will be in the shape of "Normal distriubution" shape).

Please help Saravanan  Register To Reply

2. ## Re: Excel Curves- Area under the curve

Is it neccessary to calculate area under the functions?

Is enough to compare maximum of the function and Sigma?
They must be somehow corelatred to determine area under curve and if you already have those numbers you can compare them.  Register To Reply

3. ## Re: Excel Curves- Area under the curve

Dear Zbor.

Two compare two curves I need same area. This can tell me quatitative difference in maxima peak.

If ur a chemist, then there is another explanation: To compare two chromotagraph, it is necessary to have same chemical concentration, bbut practically it is not possible, so area normalization is required. Which is normally supplied with chromography softwrare, but my software is bit old. Hence I need to do this manually.

I know x value where y reaches maximum. What is mean by sigma? I have X and Y coordinate value. Do u mean summation? How to caculate? Plz help.  Register To Reply

4. ## Re: Excel Curves- Area under the curve

If the curve is truly Gaussian, then you can compute the mean and deviation and use the NORMDIST function to compute the integral (with the cumulative argument = True).

If it's not, you can use integrate numerically.

Post some data (i.e., a workbook)?  Register To Reply

5. ## Re: Excel Curves- Area under the curve

Hi Shg,

I have attached my excel sheet in it. There are two curves, Blue and Red. Blue is of lesser area. I need relative ratio of areas. (Area of Blue / Area of Red).

Saravanan  Register To Reply

6. ## Re: Excel Curves- Area under the curve

OverLay(1).xlsx

I put all values as positive, but since they are small, it doesn't affect result (I guess)  Register To Reply

7. ## Re: Excel Curves- Area under the curve

Hello zbor,

Thats Fantastic!!! Thank you very much.

I am just trying to understand the mechanism of this max function.

For Example:

Column A
2
3
4
7
9
78

If we use max function for above data. we will get 78. How you have used this to calculate area?   Register To Reply

8. ## Re: Excel Curves- Area under the curve

This is situation: http://people.sc.fsu.edu/~burkardt/l...rectangles.png

When you aproximating surface under (any) curve you have (y1+y2)/2 (that's average on y) * (x2-x1) (that's difference on x axis).

Now you summ all of this parts and you get total area.

For this issue, since you have x axis from 800 to 1600... and you have 2400 results, that means x step is 2400/(1600-800)=3 (that's 3 in formula)

You can multiply all this parts: 3*(ABS(C4)+ABS(C3))/2

but I add to this summ of previous, so my formula calculate all sum, and result is last number (wich is biggest so it's MAX).
But you could for this formula make SUM (without D2 in formula =D2+3*(ABS(C4)+ABS(C3))/2)

Edit_ this won't work for formulas that intercepting X axis.  Register To Reply

9. ## Re: Excel Curves- Area under the curve

Dear Zbor,

Thats Amazing.

Thanks a lot, a lot, a lot......................................

-Saravanan  Register To Reply

10. ## Re: Excel Curves- Area under the curve

I defined two names, tbl and rgn. The first refers to all of the data, and the second refers to the data between the specified limits. The formulas in D3 and E3 are the areas under the curve within the specified region, obtained by summing the data below. This could be done directly without the added column, but I thought the method shown would be clearer to you.

I get somewhat different results than zbor ...  Register To Reply

11. ## Re: Excel Curves- Area under the curve

You get 71,6&#37;, I get 65,6%.

The difference can be, as I mention by side, that I change all numbers into positive. As I see, you take positive and negative.
What happens is that curve flow around X axis and + and - in your case compend, and in my case add to each curve. But since it's happening at the first part of the curve where values are negligible compared to the peak it doesn't affect result too much.

I don't know wich of those approaches is better (or what approach has beter physical anchorage).. Difference is only 5%. He can take either any of it, either to test few times result and see wich give better approximation (or put known values and compare results).

Just to clearify problem once more time. If you have y1=A*sin(x) function compared to y2=1 function: your area would be 0 (because negative and positive part of sin function are the same) and compared to 1 is 0. Mine area would be A/SQROOT(2), compared to 1 gives A/1,41 (because I consider both part of sin function as positive). But since Gaussian curve is not symmetrical arround X axe we get positive values for both approachs and now he need to see what's give him more precise result.  Register To Reply

12. ## Re: Excel Curves- Area under the curve

can any one able to explain this function
SUM(INDEX(rgn, 0, COLUMN() ) )

What it does actually?  Register To Reply

13. ## Re: Excel Curves- Area under the curve

rgn is a dynamic named range that covers the data.

INDEX is a versatile function that can return a value, or, as here, a range (see Help). When the row index is zero, it returns an entire column of a table, and vice versa. Here, it returns the column of the table directly below.

It would be better practice to recast the formula as

=SUM(INDEX(rgn, 0, COLUMNS(\$A3:Me) ) )

where A3 is the column in which the table starts, and Me refers to the cell in which it appears.  Register To Reply

14. ## Re: Excel Curves- Area under the curve

Thanks for your reply. This function used to sum up from required part of the curve. Am I right?

As alternative
I am intrested from X=900 to X = 1600. After calculating the area (y2+y1)/2*(X2-x1))for all data points. Why cannot we sum up all small little areas using sum() function?(only from 900-1600). I have converted all values to positive.  Register To Reply

15. ## Re: Excel Curves- Area under the curve

I am intrested from X=900 to X = 1600.
The formulas integrate over the time values specified in A3 and A4.
Why cannot we sum up all small little areas using sum() function?
It does use the SUM function.  Register To Reply