Results 1 to 15 of 16

20050619, 05:16 #1
 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

20050619, 10:02 #2
 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):
=(A3A2)*((B3C3)+(B2C2)/2)
Drag the formula down and sum the results of that column to get the area.Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20050619, 10:19 #3
 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 Yvalue on the opposite side to get the matching Xpt and use those points.
Steve

20050619, 21:02 #4
 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

20050620, 00:06 #5
 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 welldefined 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

20050620, 00:20 #6
 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>=(A2A3)*(B2+B3)/2</pre>
Copy C3 to C4 to C202 (or move the mouse to the bottom right corner of C3 and dblclick 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 Xvalues.
The area is:
<pre>=Sum(C3:C202)</pre>
Steve

20050620, 00:23 #7
 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 welldefined 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 yaxis) and current (on xaxis) 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>

20050620, 02:03 #8
 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 20Jun05 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.

20050620, 03:43 #9
 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/thread1784168.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

20050620, 09:56 #10
 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

20050620, 12:11 #11
 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

20050620, 12:23 #12
 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

20050620, 12:26 #13
 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

20050620, 12:33 #14
 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

20050620, 13:07 #15
 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