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