Maximization Problems in Spreadsheets

 

Spreadsheet take away the monotomy of doing repetitive calculations. Once the original data is entered, and the necessary formulas are entered once, the spreadsheet program will do the calculations. If the original data needs changing, or an error in the formula is discovered, they can be corrected and the spreadsheet will redo all the calcultions.

The spreadsheets can be set up so that they will redo the same calculations, if the only the original data is changed. An example of this type of problem is Maximizations. A spreadsheet can be set up to get a value from a cell, perform calculations and then put "lights and mirrors" on the results. For this exploration, a spread sheet was set up to find an volume of a box that could be created by folding up the sides of the box, given the original dimensions and cut size increment.

If any of the three items are changed, the resulting calvulatins are automatically updated. The results of calculations can be graphed, depicting pictorially what is represented by the chart.

If the user would like to change the dimensions, that is easily done, by changing the values in the original dimension cells. The rest of the calculations are automatically done by the computer and a new graph is created.

 

By reading the graph, the volume of the box is maximized at a cut size of one inch. If better results are needed, the graph can be recalculated by changing the initial cut size and cut increment. This allows the results to be blown up, with very little calculations by the user.

A new spread sheet can be created easily for different types of maximization problems. The graph below is for a the maximum area that can be enclosed given a finite length of fencing. The results are presented as graphs that show the width or the length of the area enclosed. In both cases, the maximum area occurs when a square area is enclosed with length and width equal to 9 yards.


For many different types of problems, the spreadsheet can be a useful tool that eliminate repetitive calculations for the user.



Return to Lars' student page



Return to EMT 668 page