You can use Solve to maximize or minimize a certain value when you're trying to solve a particular problem. These optimization capabilities can be applied in all sorts of fields, from Finance to Production and Science. Solve can help you, for example, to figure out how to allocate resources to various groups.
To start using Solve, go the Tools menu and select Solver...
An example of when and how to use Solve
Let's say you're a farmer and you want to decide what to grow. You know that each crop will bring in a certain amount of money and take a certain amount of land, capital, and fertilizer to grow.
You can grow three things: wheat, corn, and broccoli.
Each of them has its own costs and brings in a given amount of money. However, there are only limited resources available:
- Money available: $170
- Land available: 70 acres
- Fertilizer available: 100 tons
Here's how you can express this information in the spreadsheet:
These are the formulas used in the spreadsheet:
- Money (Cell B7) =9*A3+8*B3+7*C3
- Land (Cell B8) =7*A3+8*B3+9*C3
- Fertilizer (Cell B9) =2*A3+10*C3
- Profit (Cell B16) =6*A3+8*B3+C3
When you run Solve, it can tell you what to grow:
In this case, you'd need to select the option 'Restrict to >= 0' next to 'Cells to change' to make sure that none of the selected cells are set to a negative value. Selecting this option makes sense in this example because you wouldn't grow a negative number of bushels. Alternatively, you could have added additional constraints to restrict those cells to have values greater than or equal to zero. This could be beneficial to restrict only some variables but not all.
After clicking OK you can see the result: growing 8.75 bushels of corn for a profit of $70. According to the spreadsheet, if you're planning to acquire additional resources, "land" would provide the highest value, while additional money or fertilizer wouldn't; there are remaining quantities of those resources.
If you have a similar problem you'd like to solve, you can use this template from our template gallery.
Note: you need to verify any results you get when you use Solve. We can't guarantee that this feature will solve your problems.