Section Properties from Coordinates Without VBA

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

This entry was posted in Coordinate Geometry, Excel, Newton, UDFs, VBA and tagged , , , , , . Bookmark the permalink.

13 Responses to Section Properties from Coordinates Without VBA

  1. Bill Harvey says:

    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.



  2. Bill Harvey says:

    Sorry, I should have said: The cross product is Ax * By – Bx * Ay


  3. dougaj4 says:

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


  4. Bill Harvey says:

    Sorry, I was really trying to say that the difference and average steps are unnecessary. The cross product is the cleaner route. Bill


  5. dougaj4 says:

    Bill – OK, I’ll have a closer look at it!

    (Sent from Wellington Airport, on my way to Symposium on HP Concrete at Rotorua)


  6. Pingback: Dots and Crosses | Newton Excel Bach, not (just) an Excel Blog

  7. Pingback: Daily Download 20: Section Properties | Newton Excel Bach, not (just) an Excel Blog

  8. Joseph says:

    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?


  9. Bill Harvey says:

    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.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s