At the Eng-Tips forum there was a question asking for a calculation of area and centroid values from coordinates without the use of VBA. In my opinion using VBA is much the easiest and most robust way to do it, but doing the calculation entirely on the spreadsheet does make the process clearer, especially for those not familiar with VBA, so I have set up a sample calculation, which can be downloaded here: On-sheet SecProp.xls
The spreadsheet includes the “on-sheet” calculation for Area, first moment of area about the centroid, and X and Y coordinates of the centroid. For comparison I have also included the SecProp user defined function (UDF), which should be entered as an array function.
A screen shot showing both methods applied to a channel section is shown below.
Click for full size view
Note that if a shape with more than 20 segments is required it is possible to insert extra rows in the table, but the formulas in the top row (shaded darker) mut be copied over the shaded region below. For the Secprop UDF it is just necessary to adjust the range (=SecProp(coordinate_range)), then re-enter as an array function by pressing Ctrl-Shift Enter.
The SecProp UDF will also return any desired single value, by entering the row number after the range. For instance, entering:
=SecProp(A4:B24,3), returns the third result, which is the first moment of area about the Y axis (21.667).
Newton Excel Bach, not (just) an Excel Blog 
I used to labour along this path but then realised that vectors make life easier. No need to work out the side lengths, treat each side as a triangle with an apex at the origin, then the co-ordinates of the corners can be treated as vectors. The cross product Vector a x Vector b is a vector whose magnitude is the area of the parallelogram and having an appropriate sign if you call off the nodes anticlockwise. So (a x b)/2 is the area and (0 + a + b)/3 is the centroid. This also steps up to 3D with negligible further effort. Never quite got round to doing a UDF but I think it should be done by making a cross product UDF first and then calling it off for more complex areas.
Bill
LikeLike
Sorry, I should have said: The cross product is Ax * By – Bx * Ay
LikeLike
Hi Bill, It doesn’t calculate side lengths! In effect it sums the area under each line segment as the average Y value x the difference in X values (taking account of the sign).
LikeLike
Sorry, I was really trying to say that the difference and average steps are unnecessary. The cross product is the cleaner route. Bill
LikeLike
Bill – OK, I’ll have a closer look at it!
(Sent from Wellington Airport, on my way to Symposium on HP Concrete at Rotorua)
LikeLike
Pingback: Dots and Crosses | Newton Excel Bach, not (just) an Excel Blog
Pingback: Daily Download 20: Section Properties | Newton Excel Bach, not (just) an Excel Blog
This spreadsheet has been a great help to me! I would like to know a little bit more about the process it goes through to get the geometric centroid (I want to develop an app capable of getting centroids of irregular polygons). I saw it divides by 6 to get the Ax/Ay?? What specific method is this one? There is any teoric background you could pass me?
LikeLike
Joseph – does this answer your question?
https://newtonexcelbach.wordpress.com/2008/03/09/section-properties-from-coordinates/
The formula given for Ax there is not identical, but it is equivalent.
LikeLike
I still think the vector solution is quicker and more robust.
Build the polygon from triangles based at the origin. If A and B are vectors defining 2 adjacent nodes (ie the coordinates of the nodes if you like), (A cross B)/2 is the area and (A+B)/3 is the centroid. Moment of area about origin is then (area) cross (Centroid). Signs come out in the wash. Sum the set and divide the moments by the areas to get final centroid.
I will do a spreadsheet later this am.
Bill
LikeLike
A quick and dirty 4 node spreadsheet at =:
http://billharvey.typepad.com/Areas%20by%20vector.xls
LikeLike
Bill – did you see this one?
https://newtonexcelbach.wordpress.com/2011/08/21/dots-and-crosses/
That includes a UDF for area using the vector method.
My response to Joseph above was to give an intuitively obvious (I hope!) explanation of why the method works, and I think that looking at summing the areas of the trapezoids is easier than looking at vector methods.
LikeLike
How did I miss that. Sorry. Bill
LikeLike