+ Reply to Thread
Results 1 to 15 of 15

Excel Curves- Area under the curve

  1. #1
    Registered User
    Join Date
    07-12-2009
    Location
    Sheffield, UK
    MS-Off Ver
    Excel 2007
    Posts
    10

    Thumbs up 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
    Last edited by callsaravananr; 07-21-2009 at 09:55 AM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    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.

  3. #3
    Registered User
    Join Date
    07-12-2009
    Location
    Sheffield, UK
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Excel Curves- Area under the curve

    Dear Zbor.

    Thanks for your reply.

    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.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,649

    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)?
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    07-12-2009
    Location
    Sheffield, UK
    MS-Off Ver
    Excel 2007
    Posts
    10

    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).

    Please help me out.

    Saravanan
    Attached Files Attached Files

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Excel Curves- Area under the curve

    How about this?

    OverLay(1).xlsx

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

  7. #7
    Registered User
    Join Date
    07-12-2009
    Location
    Sheffield, UK
    MS-Off Ver
    Excel 2007
    Posts
    10

    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?

  8. #8
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    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.

  9. #9
    Registered User
    Join Date
    07-12-2009
    Location
    Sheffield, UK
    MS-Off Ver
    Excel 2007
    Posts
    10

    Thumbs up Re: Excel Curves- Area under the curve

    Dear Zbor,

    Thats Amazing.

    Your a mathematical magician.

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


    -Saravanan

  10. #10
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,649

    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 ...
    Attached Files Attached Files

  11. #11
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Excel Curves- Area under the curve

    You get 71,6%, 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.

  12. #12
    Registered User
    Join Date
    07-12-2009
    Location
    Sheffield, UK
    MS-Off Ver
    Excel 2007
    Posts
    10

    Unhappy Re: Excel Curves- Area under the curve

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

    What it does actually?

  13. #13
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,649

    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.

  14. #14
    Registered User
    Join Date
    07-12-2009
    Location
    Sheffield, UK
    MS-Off Ver
    Excel 2007
    Posts
    10

    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.

  15. #15
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,649

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1