Calculating the geometric centroid of a floor plan 1

[GalileoG]

I have been struggling with this for a few days now. When I think I have a code that works for any possible geometric configuration, I find a flaw. You kind folks are my last resort.

I have two columns in my spreadsheet, for x and y values. The user inputs the following to create a square:

x,y

0,0
0,50
50,50
50,0
0,0

Then, I am trying to get my spreadsheet to find the centroid of this shape. Easy enough for a square, but my code needs to work for any possible configuration. For example, this configuration:

x,y

0,30
0,120
50,120
50,155
80,155
80,180
155,180
155,155
290,155
290,120
300,120
300,19.3
50,0
0,30

How can I find the centroid of the shape that the above would give me? I'm about to rip my hair out. Would appreciate any and all help. Thank you.

Clansman

If a builder has built a house for a man and has not made his work sound, and the house which he has built has fallen down and so caused the death of the householder, that builder shall be put to death." Code of Hammurabi, c.2040 B.C.

 

[electricpete]

Somewhere on Doug / IDS' website I downloaded the attached file which does many things with arbitrary areas, including find the centroid (see tab "coords"

http://newtonexcelbach.wordpress.com/

=====================================
(2B)+(2B)' ?

 

[GalileoG]

That is EXACTLY what I was looking for. I love you.

Have a GREAT weekend!

Clansman

If a builder has built a house for a man and has not made his work sound, and the house which he has built has fallen down and so caused the death of the householder, that builder shall be put to death." Code of Hammurabi, c.2040 B.C.

 

[GalileoG]

Now I have to reverse engineer his spreadsheet into mine. He is using VBA to calculate the centroid, I am wondering if there is an excel only solution out there?

Clansman

If a builder has built a house for a man and has not made his work sound, and the house which he has built has fallen down and so caused the death of the householder, that builder shall be put to death." Code of Hammurabi, c.2040 B.C.

 

[cowski]

Why the aversion to VBA?

 

[electricpete]

Attached is a no-vba solution based on:

http://en.wikipedia.org/wiki/Centroid#Of_a_finite_set_of_points

=====================================
(2B)+(2B)' ?

 

[msquared48]

If I am not mistaken, there was a program in the Structural Analysis module for my HP41C that would calculate that for any set of closed coodinate point sequence, plus structural A, Ix, Iy, and Ixy values.

Mike McCann
MMC Engineering

 

[IDS]

I have posted an example of calculating areas and centroid coordinates without VBA here (the spreadsheet is also attached to this message):

http://newtonexcelbach.wordpress.com/2011/08/07/section-properties-from-coordinates-without-vba/

The on-sheet calculation is fairly straightforward, but using the Secprop User Defined Function is really much more convenient, flexible and robust, so I have also included that with the download spreadsheet.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[corus]

Here's another one I downloaded. The writer's name is on the sheet and you can find more by googling the company and/or his name.

Tara

http://tinyurl.com/4ydjg7m

 

[electricpete]

Pretty cool use of array formulas.

=====================================
(2B)+(2B)' ?