This excel sheet will help you find the centroid of an irregular shape
Materials Required: Computer with Microsoft Excel
Approximate Completion Time: 10 minutes
- From the desktop screen, open the Microsoft Excel program and save the file to the computer or to another device.
- Begin by typing, on the same row (horizontal), each of the following in six columns (vertical) and consider typing them in bold in order to recognize them easily.
- Area, in^2 (inches are abbreviated in, in this case they are squared)
- X bar, in (X bar represents the distance from the origin to the location of the centroid in the x direction, Y bar is the same except in the y direction)
- Y bar, in
- X bar*Area, in^3
- Y bar*Area, in^3
Now, divide the shape into two components: the triangle whose base lies on the y axis and has a height of 12in. in the x direction.
- Type Triangle under the Component column to represent this component.
- For the second component, also in the Component column, type Rectangle, which denotes the rectangle located atop the triangle.
Now excel will be used to calculate the columns in the row for the Triangle. The formula for the area of a triangle is the base multiplied by the height and all of this divided by 2 (b*h/2).
- In the Triangle row and under the Area column, type =6*12/2 and press Enter on the keyboard.
Next we will input the location of the centroid of the triangle. This is given by the table above which indicates that the centroid of a triangle is located, from the corner that is opposite of the hypotenuse (the longest side of the triangle), one-third of the length of the base in the y direction and one-third of the length of the height in the x direction in this case. This corner is not at the origin in this figure, so Y bar is affected, but X bar is simple: it is one third the length of the height (since the triangle is on its side)
- Under X bar type =1/3*12 and press Enter.
Since the centroid is one-third of the distance from this corner, it is two thirds from the origin.
- Under Y bar type =2/3*6 and press enter.
- Under X bar*Area type = and click the cell that is under the X bar column, then type * and finally, click the cell under the Area column. Then press Enter to finish.
- Under the Y bar*Area column type = then click the cell that is under the Y bar column, then type *and finally, click the cell under the Area column. When finished, again press Enter.
Now calculate the parts for the rectangle. The area is the length times the width.
- Under the Area column type =6*3
The X bar of the rectangle is half the length of the rectangle (3) plus the distance from the origin to the left side of the rectangle in the x direction (6). The Y bar of the rectangle is half the width of the rectangle (1.5) plus the distance from the origin to the bottom of the rectangle in the y direction (6)
- Type =3+6 under the X bar column of the Rectangle row.
- Type =1.5+6 under the Y bar column of the Rectangle row.
The X bar*Area is the same as for the triangle
- Type =, then click on the cell under X bar, followed by typing * then clicking on the cell under Area. When finished press Enter.
- Do the same for Y bar *Area of the rectangle.
- Now under the Component column create another row which can be titled Total for the total shape.
- In the Total row, under the Area column, add the two cells in the two rows directly above the current cell by typing =, clicking on the first cell, typing +, then clicking on the second cell and pressing Enter.
- Skipping the X bar and Y bar columns, add, in the same manner as step 16, the X bar*Area and Y bar*Area columns in the Total row.
Now the calculation of the X bar, which is done by taking the Total X bar*Area and dividing it by the Total Area.
- Type =, then click the Total X bar*Area cell, type / and then click the Total Area cell. Finish by pressing Enter.
- For the Y bar type =, then click the Total Y bar*Area cell, type / and then click the Total Area cell. Finish by pressing Enter.
The results should be that the X bar is approximately 5.667 and the Y bar is approximately 5.1667. What this means is that the centroid of this shape is, on the xy coordinate plane, 5.667 inches in the positive x direction and 5.1667 inches in the positive y direction. If these were not the results obtained, check the work as there may have been a mistake in the process.
To show this position, create a scatterplot graph.
- Begin by clicking on the Total X bar result, dragging the mouse to the left to highlight the Total Y bar result.
- Next, under the INSERT tab in the menu, select the scatterplot that only has points with no connecting lines (usually the first option).
- Move the chart so it does not cover the data table, then right click on the chart
- From this drop down menu click the Select Data option, then click on edit in the menu that pops up.
- Move the cursor to type in the Series X Values box.
- Then click on the Total X bar cell (the one with the 5.667 value) and click OK on this menu and the menu that pops up after it.
- Now to format the axes, double click on any of the numbers on either axis.
- In the menu that appears on the right type in 12 for the maximum if the x axis, or 9 for the y axis.
- Then press enter and do the same for the other axis.
Now there is a visual for the location of the center of mass, the centroid, of the shape that was just calculated. Title the chart appropriately by typing in the text box for Chart Title.
Now the Excel sheet is finished.