Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

This section contains formulas for calculating the area, surface, circumference, and volume for common two- and three-dimensional shapes.

Calculating the Area and Perimeter of a Square

To calculate the area of a square, square the length of one side. The following formula calculates the area of a square for a cell named side:

=side^2

To calculate the perimeter of a square, multiply one side by 4. The following formula uses a cell named side to calculate the perimeter of a square:

=side*4

Calculating the Area and Perimeter of a Rectangle

To calculate the area of a rectangle, multiply its height by its base. The following formula returns the area of a rectangle, using cells named height and base:

=height*base

To calculate the perimeter of a rectangle, multiply the height by 2 and then add it to the width multiplied by 2. The following formula returns the perimeter of a rectangle, using cells named height and width:

=(height*2)+(width*2)

Calculating the Area and Perimeter of a Circle

To calculate the area of a circle, multiply the square of the radius by Π. The following formula returns the area of a circle. It assumes that a cell named radius contains the circle's radius:

=PI()*(radius^2)

The radius of a circle is equal to one-half of the diameter.

To calculate the circumference of a circle, multiply the diameter of the circle by Π. The following formula calculates the circumference of a circle using a cell named diameter:

=diameter*PI()

The diameter of a circle is the radius times 2.

Calculating the Area of a Trapezoid

To calculate the area of a trapezoid, add the two parallel sides, multiply by the height, and then divide by 2. The following formula calculates the area of a trapezoid, using cells named parallel side 1, parallel side 2, and height:

=((parallel side 1+parallel side 2)*height)/2

Calculating the Area of a Triangle

To calculate the area of a triangle, multiply the base by the height and then divide by 2. The following formula calculates the area of a triangle, using cells named base and height:

=(base*height)/2

Calculating the Surface and Volume of a Sphere

To calculate the surface of a sphere, multiply the square of the radius by Π and then multiply by 4. The following formula returns the surface of a sphere, the radius of which is in a cell named radius:

=PI()*(radius^2)*4

To calculate the volume of a sphere, multiply the cube of the radius by 4 times Π and then divide by 3. The following formula calculates the volume of a sphere. The cell named radius contains the sphere's radius.

=((radius^3)*(4*PI()))/3

Calculating the Surface and Volume of a Cube

To calculate the surface area of a cube, square one side and multiply by 6. The following formula calculates the surface of a cube using a cell named side, which contains the length of a side of the cube:

=(side^2)*6

To calculate the volume of a cube, raise the length of one side to the third power. The following formula returns the volume of a cube, using a cell named side:

=side^3

Calculating the Surface and Volume of a Cone

The following formula calculates the surface of a cone (including the surface of the base). This formula uses cells named radius and height:

=PI()*radius*(SQRT(height^2+radius^2)+radius))

To calculate the volume of a cone, multiply the square of the radius of the base by Π, multiply by the height, and then divide by 3. The following formula returns the volume of a cone, using cells named radius and height:

=(PI()*(radius^2)*height)/3

Calculating the Volume of a Cylinder

To calculate the volume of a cylinder, multiply the square of the radius of the base by Π and then multiply by the height. The following formula calculates the volume of a cylinder, using cells named radius and height:

=(PI()*(radius^2)*height)

Calculating the Volume of a Pyramid

Calculate the area of the base, multiply by the height, and then divide by 3. This next formula calculates the volume of a pyramid. It assumes cells named width (the width of the base), length (the length of the base), and height (the height of the pyramid).

=(width*length*height)/3