Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    calculate area inside shape (Excel 2003)

    I have a graph I created and need to calculate the area contained within it. The chart type is xy scatter and there are about 200 data points. The shape of the output chart looks sort of like an ameba (two curved lines, one above the other with their ends meeting - -see attached picture).

    It's been way too long since I've done any calculus <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> and I've looked through Excel help and online and couldn't find any reference to calculating areas. How can this be done?

    Thnx, Deb

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculate area inside shape (Excel 2003)

    If we assume :

    The x data are in column A
    The top Y data (The top part of the chart) are in column B
    the bottom y data are in column C.

    Then the surface of a narrow rectangle is (starting on row 3, data starts from row 2):

    =(A3-A2)*((B3-C3)+(B2-C2)/2)

    Drag the formula down and sum the results of that column to get the area.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: calculate area inside shape (Excel 2003)

    In addition to Jan's suggestion. You will need to make sure the X's on the top curve and the bottom curve are the same. If nothing else, interpolate the Y-value on the opposite side to get the matching Xpt and use those points.

    Steve

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculate area inside shape (Excel 2003)

    Thanks for the feedback. I don't really have a 3rd column that is plotted so am not sure how to use your formula. The original data does have a 3rd column but it's just a time unit (sequence number) and it's not plotted. Attached is the actual chart with the first 200 data points (the rest of the data is the next cycle and not needed here). I understand adding up all the little rectangles within the bounds of the curve (basic integral calculus) to get the total area.

    Can you review this and show me how to adapt your formula?

    Thnx, Deb

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculate area inside shape (Excel 2003)

    Another approach is to try to fit the data to an equation. If it's a well-defined hysterisis phenomenon, it might be possible to determine all of the required coefficients and integrate that, using the same program that fitted the curve probably.

    Alan

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: calculate area inside shape (Excel 2003)

    This seems like a reasonable solution (it would be nice if you had some calculated by alternate means to check). I did some rough ones by adding gridlines and calculating the squares and it seemed "valid".

    In C3: enter
    <pre>=(A2-A3)*(B2+B3)/2</pre>


    Copy C3 to C4 to C202 (or move the mouse to the bottom right corner of C3 and dbl-click to autofill)
    (You have 1 less calc than values). You are summing and subtracting essentially the trapezoidal areas. Each group is calculated separately (min points and max points) since you do not have "matched pairs" at the X-values.

    The area is:
    <pre>=Sum(C3:C202)</pre>


    Steve

  7. #7
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculate area inside shape (Excel 2003)

    Yes, quite true but not sure if this is well-defined or not. In this case the data was collected as the current was increased on a given load (a magnetic core) and it was stopped when it reached its saturation point (where the two lines converge and flatten). The program that generated the data (which was imported into Excel), doesn't *seem* to have the ability to determine the power loss (which is the area under the curve, or the area within the bounds of the two curves) so that's why I'm trying it this way. The program used to do this electronic experiment generated its own nice hysteresis curve and it's easy visually to see where the point of saturation is, but the power loss value is what's needed (I haven't been able to find a way to calculate that value with this other program - it may exist, but it's not to be found so far).

    The data plotted is: flux density (on y-axis) and current (on x-axis) over 200 data points (equally spaced in time). The data varies from approximately +/-2.0 with 9 digit precision. I realize this only makes sense if you have a background in electromagnetics but in any case, the idea is the same for any curve where you need to calculate its area. Maybe I can put this in Matlab or such programs.....

    Thnx, Deb <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculate area inside shape (Excel 2003)

    <P ID="edit" class=small>(Edited by AlanMiller on 20-Jun-05 12:03. )</P>Hmmm <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>... I'd have thought that there might be curve "families" that could be used to fit hysteresis loop data. The upper left and lower right curve portions look like candidates for hyperbolic (or even inverse) tangent functions, even though these may not actually reflect the physical relationships involved. I'm surprised I can't find any such parametric equations searching on the web - it must be a common requirement. Sorry I can't help further.

    Alan

    [Edited] - one obvious place to look is the program that generated the data. Clearly, it must use a formulaic method, possibly described in its documentation. That might blaze the path for a formulaic integration.

  9. #9
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculate area inside shape (Excel 2003)

    Actually you're quite close. I did find this http://www.pcreview.co.uk/forums/thread-1784168.php which actually comes out to the same value as the previous posts with SUM but it's still off from my measured value (of power loss). But the whole point is to have a 2nd source to confirm (it could be my combined circuit tolerances too). Getting closer.....

    And even better... http://www.duncanwil.co.uk/areacurv.html

    Thanks again,
    Deb

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: calculate area inside shape (Excel 2003)

    Another "crude way" to do a "reality check" on the calculations is to use an analytical balance, if available.

    Print the chart (of the same size) on good stock paper. Then literally cut out the shape and also cut out a rectangular piece of know dimension (as large as possible to reduce errors).

    You get the mass of the "known rectangle" and you have its area. From the mass of the unknown and the previous 2 values (you also know 0 area = 0 mass) you can interpolate the area of the shape...

    Steve

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculate area inside shape (Excel 2003)

    Yet another crude way is to use XL to calculate average values for Flux Density for the Current subdomains (not magnetic ones <img src=/S/grin.gif border=0 alt=grin width=15 height=15>) appropriate to the areas shown. Each average x subdomain size will give the area of a rectangle, equal to its corresponding area. Adding and subtracting the results will give you the total area within the figure. For instance, subtracting the calculated A1 from the calculated (A1+A2) will give you the A2 area.

    Alan

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: calculate area inside shape (Excel 2003)

    I printed your chart 10 times.
    With 5 of them I cut out the rectangular shape from

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: calculate area inside shape (Excel 2003)

    This is similar to my approach, but my "subdomains" are much smaller trapezoidal areas than the domains you list, so should yield better precision.

    Steve

  14. #14
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculate area inside shape (Excel 2003)

    I can't actually see that our results would be any different, since all the points are spaced equally along the domain (if that's a correct assumption).

    Alan

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: calculate area inside shape (Excel 2003)

    The values come from TIME so the X coord do not line up. So while Area A1 could be calculated directly, Area A2 (and its counterpart) can not be calculated directly: one would have to manipulate the data.

    I think that your scheme would also require a means to "define" the domains. The method I listed uses the values directly as they are listed so no domain definitions are required.

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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