Optimizing surface area of geometrical solids with dimension constraints Excel Model

Description
This is a model to calculate the dimensions of a solid (e.g. cylinder, cone etc.) which results in minimum surface area given the constraints on volume and other parameters like height or radius. The model uses random numbers for the given range of dimension (such as height) and calculates other dimensions (radius, slant height etc.) and eventually the surface area that meets the volume requirement.

The minimum surface area will result in least material cost for fabrication of any solid while meeting the requirement of volume and other dimensions. The model demonstrates the calculations for a cylinder and cone. But the concept can be used for any solid such as truncated cone or a cone over a cylinder or a rectangular pyramid etc.

The model is structured around a methodology to find the minimum surface area with a fixed given volume (constraint 1) and a range for the dimension of height (constraint 2). The radius is calculated for different values of height (randomly selected using random numbers within the range) using the fixed volume. The values of calculated radius and randomly selected height from the given range is used to calculate surface areas for randomly selected height within the given range.

The minimum surface area is then selected with corresponding radius and height.

1) Cylinder (with a top lid)
Volume of a cylinder, V = 𝜋 r^2 h
Radius of a cylinder, r = √[V/(𝜋 X h)]
Surface area of a cylinder, s = 2 𝜋 r (r + h)

The problem in this model is to find the minimum surface area of a Cylinder with a volume of V cubic centimetres and height within a range of h1 to h2 centimetres. The tool generates a series of random numbers for height between h1 to h2 cm and derives the radius using the fixed volume. The surface area is calculated using the random height and derived radius. The iterations with different number of random heights are generated i.e. 100, 200, 300, 400, 500 and so on. The results are summarized in a table to record the variability in minimum surface area using different number of random heights. The model can generate 1,000 random numbers but can be extended further by simply copying the rows further down.

2) Cone
Volume of a cone, V = (𝜋 r^2 h)/3
Slant height of a cone, I = √r^2 + h^2)
Radius of a cone, r = √[3V/(𝜋 X h)]
Surface area of a cone, s = 𝜋 r (l + r)

The same methodology has been applied to calculate the minimum surface area of a cone with a volume of V cubic centimetres and height within a range of h1 to h2 centimetres. In the case of a cone, slant height has been calculated which is required in the surface area formula.

The model has used @randbetween(h1, h2) to generate random numbers between height h1 to h2. The tool uses @indirect function to select a range based on the last row of the range. While running iterations with different number of random heights i.e. 100, 200, 300, 400, 500 and so on, the @indirect function selects the desired range (based on number of random heights) to locate the minimum area. The @indirect function avoids the selection of whole column e.g. A:A and thereby keeps the file size to minimum.

The surface area of a solid can be easily calculated by using differential calculus by taking the first derivative of the function and making it equal to zero and solving the equation. But in this model the constraint of height (or may be radius) is not fixed but within a range and thus using random numbers is an easy method to find the minimum area.