Center of Mass

[Gerhard]

Hello together,
i have a 20*10 matrix with positive integer-values and I have to calculate
the center of mass of this "volume". Should be easy in Excel...but what's the
easiest way of doing it?
Best Regards
Gerhard

 

[Bernie Deitrick]

Gerhard,

What do the integer values represent, and what does the matrix represent?

HTH,
Bernie
MS Excel MVP

 

[Bernard Liengme]

I can solve this problem with these assumptions:
1) the integers represent mass and
2) the first mass is in located at point (1, 1) on Cartesian plane.
I will demonstrate with a 2 by 3 matrix
1 1
2 2
1 1

I located this in A2:B4 but it can be anywhere provided you adjust the
formulas
In A6:B* I compute the X coordinate of each mass; in A6 use
=ROW(A2)-ROW($A$2)+1 and copy down and across
1 1
2 2
3 3

Likewise in A10:B12 find the Y coordinates; in A10 use
=COLUMN(A6)-COLUMN($A$2)+1
1 2
1 2
1 2

The inner term in these allows you to move the data on the worksheet

The X coordinate of the centre of mass is give by
=SUMPRODUCT(A2:B4,A6:B8)/SUM(A2:B4)
The Y coordinate of the centre of mass is give by
=SUMPRODUCT(A2:B4,A10:B12)/SUM(A2:B4)
The result (2, 1.5) agrees with other ways of computing this example.
Clearly if the first mass is on the origin, we need to subtract 1 from the x
and y values.
Email me (remove TRUENORTH.) and I will send you a file
best wishes

 

[Bernard Liengme]

Depending on how you look at the matrix, you may wish toe reverse X and Y in
my demo