Use the Solve function

The solve function has been removed in the new version of Google Sheets. To find similar functionality, check out add-ons in Sheets.

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...

Solve currently supports only linear equations and the following two functions: SUM and SUMPRODUCT.

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:

solve example

  • Money available: $170
  • Land available: 70 acres
  • Fertilizer available: 100 tons

Here's how you can express this information in the spreadsheet:

solve example 2

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:

Setting up solve

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.

solve solution

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.