Friday, June 26, 2009 3:02 PM
Today we've added optimization capabilities to spreadsheets. Try out the Solve feature at Tools > Solve... It's useful for problems where you'd like to maximize (or minimize) a certain value.Let's say you want to buy a new house. You know that you can afford $2,000/month, and you want to determine how much house you can afford. Just create a spreadsheet with your formulas (in this case, using the equation =B1*SUM(B2:B3)/12 in Sheet1!B4).
You'll quickly see that you can buy a house worth $320,000.
Check out this more complex profit maximization example that's documented in this help center article. This example shows how the Solve feature could help a farmer who is trying to determine how much of three different crops to plant given his land, fertilizer, and budget constraints.
For those of you who are familiar with optimization functions in spreadsheets, note that we only support linear equations in the constraints and in the cell you want to optimize.
Still, the Solve feature is a great way to solve a multitude of everyday problems without hurting your head too much. We hope you find it useful.
19 comments:
This will be really helpful to OR students like me.. hope you will add non-linear and integer programming soon..!!
Thanks for excellent spreadsheet solution.
Excellent! Solve is indeed a great tool to solve problems like calculating 'equal monthly instalments'.
Thanks.
I get an error message "Unbounded"
John, unbounded means the answer to your problem is infinity (or negative infinity).
For example, consider the following problem:
Max 2x + y
x + y < 10
Then x will grow towards infinity and y towards negative infinity. Now if you take that same problem and also restrict the variables to >= 0 then you'll get x = 10, y = 0, and a solution of 20.
Any chance at using this functionality to create conditional formatting?
q053x,
this feature is not related to conditional formatting which you can find under "Format" > "Change colors with rules..."
@benccann,
Ben,
Is there an overview which are the restrictions that apply to the formulas that can be used with the Solver (Please see this forum thread:"Spreadsheet Solver - Bugs and Suggestions" http://www.google.com/support/forum/p/Google+Docs/thread?tid=3fc808e90398022b&hl=en )
ahab,
In this initial version, formulas are not supported:
http://docs.google.com/support/bin/answer.py?hl=en&answer=139704
However, as always, we are working hard at improving the product. You can use SUM currently and SUMPRODUCT will be coming soon. Thanks for using Google Spreadsheets and providing feedback. We'll continue to monitor the forums and add new features as possible.
@benmccann (thank you for replying!)
You state: "In this initial version, formulas are not supported [...]"
This is phrased a bit confusing. I think what you mean is that the use of functions (except the SUM function) in formulas used by the Solver are not supported.
I.e. that the user is being restricted to simple expressions using (a subset of?) operators only (b.t.w. which subset of this list of operators available in Google Docs spreadsheets would that be?: + - * / ^ & = <> > >= < <= ).
I know this sounds like nitpicking but the help article is far from clear by just providing an example and not clearly explaining these restrictions when using the Solver.
User shouldn't have to waste time finding out all the details by experimenting...
Solve feature do not work with tables in russian style. If I try to solve template it is ok. If I try to solve simple example in tables with russians pages "Лист..." the solve function is print "function is not valid".
@sav6622
Can you share an example spreadsheet with us that shows 'russian style tables'? I get the impression something of your description gets lost in translation...
this example
http://spreadsheets.google.com/ccc?key=0Ap5CxZdSOTR4ckRzQmdSc1pNb0J0UXlob2xVOElOWFE&hl=ru
This example is from yours template. Original template is solve. This example is not work... :-(
@sav6622 & @benmccann
I checked the sav6622's spreadsheet and it would not solve. The fact that Sav6622 did use the russian locale setting for this spreadsheet colud be of influence and we both suspected the russian sheet name had to do with that.
However changing the sheet name to simply Test (instead of Лист1 ) and the locale to United States did not make any difference. The Solver kept replying 'The goal specified must be a cell containing a valid formula.'
Finally it showed that the Solver choked on the use of parentheses. A formula like =((6,0*A6)+(8,0*B6))+C6 could not be evaluated because of the parentheses.
I think it is a crying shame that such restrictions are not documented - another hour of my life waisted trying to debug the use an 'almost-finished' Google Docs feature (Sorry for saying this Ben, I guess you are probably not the one who said "let's put it in now".)
Yes it is as ahab says... (example is not work)
Sorry for my english...
"Maximization" is translated to russian as "По максимальному значению" (not "Развернуть")
"Minimization" is translated to russian as "По минимальному значению"
(not "Свернуть")
I would suggest another option of permission "share with other users"
add "edit and save" "only edit" "only to see doc" I need add dinamyc page, I want that the users use formulas but don't erase it, thanks.
ahab,
Sorry for the confusion re functions vs. formulas. I used the word formulas since that's what was in the Insert menu. You had a valid point that function is the word most people are used to, so the team went through and updated the wording in the UI. You'll see it now says Insert > Function, so hopefully this makes more sense to you now. Also, we've added support for SUMPRODUCT now, which I believe you were interested in as well.
sav6622,
I didn't have any problem with the example you posted. Language and use parentheses should not have any affect on the results. The only restriction is that formulas must be linear and cannot use functions other than SUM and SUMPRODUCT.
sav6622, i also forgot to mention that a coworker confirmed for me the translations were a bit off, so i put in a request for someone to take another look
Post a Comment