I bet Excel is powerful enough to do this, but I'm really at a loss for how to make it happen.
Let me give an example scenario. You're trying to maximize calories at a meal. You have $10 to spend and you can have 1 main dish, 1 side, and 1 dessert.
Main Dishes
Hamburger (400 cal) - $3
Sandwich (200 cal) - $1
Steak (500 cal) - $5
Side
Fries (200 cal) - $2
Corn (100 cal) - $1
A bowl of melted cheese (400 cal) - $4
Dessert
Pie (400 cal) - $3
Cake (350 cal) - $2
Boring Cookie (200 cal) - $1
So I've got columns for type of food, name of food, calories, and price. I'd like to create a function that examines what to select from each category to maximize my calories while fitting my budget constraint. Assume we're talking about dozens of items in each category.
Any ideas? Or even starting points?
I apologize if this is a little off topic:
Is the "Goal Seek" function available for the mobile version of Excel? If it is, do I need to download something to install on my phone? Thanks in advance for your help!
Josh
Hello,
Is there a way to set the "value" within a goal seek to a cell? When I run the goal seek it seems like you can only set the "set cell" and "by changing" parts to an actual cell. Any insight would be greatly appreciated.
Thanks,
Jared
One of the features I loved in 2003 version was the ability to drag/drop a point on a graph and being able to recalc that value with goal seek. That functionality seems to have been lost in 2007 version.
Does anyone know of a way to drag/drop in a graph and use goal seek to change the values?
EP
Hello Forum members,
I am new to his forum.
I have built a financial model detailing the costs for a certain project and the way in which the costs are financed. At a certain point I am running a macro that has the objective to minimize the costs that are not yet financed by taking out debt for these costs.
The costs that are not yet financed consist of two parts, so the goal seek function needs to minimize both parts. Let's call the two cells with the costs that are not yet financed A1 and A2.
Currently, I use this function:
wf.Range("Fin_shortage").GoalSeek Goal:=0, ChangingCell:=wm.Range("srDebt")
The cell called "Fin_shortage" is the problem: if I set it to be "A1+A2" the minimization does not work. Also if I set it to be "A1-A2" it does not work. Also "A1*A2" gives no correct result, as that minimizes the product of the cells, but not the individual cells, which is what I am looking for. I want both cells A1 and A2 to be minimized by changing cell "srDebt".
Who can help me achieving my goal? All help appreciated!
I am intrigued with a fact:
I have an Excel book from a student in which the Goal Seek oprion is dimmed
out and I would like to know why. I have also noticed that on the title bar
there is the following text after the book name: [Group]
Thank you for any help you can provide!
I am developing some code to support implementing the goal seek function for several columns
I get the following error message
Run Time Error 1004
Reference not valid and when I click debug the following code is highlighted:
TargetVal.Cells(i).GoalSeek Goal:=DesiredVal.Cells(i).Value, ChangingCell:=ChangeVal.Cells(i)
Here is the rest of my code which does not error out
Sub Multi_Goal_Seek()
Dim TargetVal As Range, DesiredVal As Range, ChangeVal As Range, CVcheck As Range
Dim CheckLen As Long, i As Long
restart:
With Application
Set TargetVal = .InputBox(Title:="Select a range in a single row or column", _
prompt:="Select your range which contains the ""Set Cell"" range", Default:=Range("C11:E11").Address, Type:=8)
'no default option
'prompt:="Select your range which contains the ""Set Cell"" range",, Type:=8)
Set DesiredVal = .InputBox(Title:="Select a range in a single row or column", _
prompt:="Select the range which the ""Set Cells"" will be changed to", Default:=Range("C12:E12").Address, Type:=8)
'no default option
'prompt:="Select the range which the ""Set Cells"" will be changed to",, Type:=8)
Set ChangeVal = .InputBox(Title:="Select a range in a single row or column", _
prompt:="Select the range of cells that will be changed", Default:=Range("G8:G10").Address, Type:=8)
'no default option
'prompt:="Select the range of cells that will be changed",, Type:=8)
End With
'Ensure that the changing cell range contains only values, no formulas allowed
' Loop through the goalseek method
For i = 1 To TargetVal.Columns.Count
TargetVal.Cells(i).GoalSeek Goal:=DesiredVal.Cells(i).Value, ChangingCell:=ChangeVal.Cells(i)
Next i
End Sub
Hi Guys,
Just wanting to know if anybody knows of a function in Excel, that would allow me to have a unit cost price (for example: 0.5432) and then for a table of figures containing the pack sizes to determine the correct unit price that would allow all pack costs to be at 2 decimal places. I am sure this makes no sense, so I have included an example below:
My spreadsheet would look like the following:
Cell A1 (Unit Cost Price) = .5814
Cell A3 (Pack of 75 units) = .5814*75 = 43.605
Cell A4 (Pack of 80 units) = .5814*80 = 46.512
Cell A5 (Pack of 100 units) = .5814*100 = 58.14
and so on
I need some way of making cell A1 change to a value that will cause all cells A3 to A5 to be 2 decimal places or less.
Any ideas?
Question
Suppose a student takes 25 marks in midterm exams and 10 marks in assignments. His total score will be calculated by adding his midterm, assignments and final term marks. If the student wants to make his total score equal to 70, find out the final term marks that he should take by using Goal Seek in Microsoft excel.
Sample set of values before applying goal seek operation:
Applying Goal Seek to Find Final Term Marks
25
Midterm Marks
10
Assignment Marks
0
Final Term Marks
35
Total Score
You have to calculate final term marks by using the following values of goal seek input dialog box:
Set cell = Total Score
To Value = 70
By changing cell = Final Term Marks
Show the values of used cells before and after the goal seek and show the procedure.
Things that you must remember!!
v Make sure the "Set Cell" cell is set to a formula or function or cell reference.
v Make sure the "By Changing Cell" cell is a number or blank, and not a formula, function or cell reference like =C5.
v Make sure there is a link by formulas between the two cells you entered in the Goal Seek.
v Finally, make sure your formula in the "Set Cell" cell is correct.
Goal Seek input dialog
hey all!!
trying to fixed this code... but its not working to well..
i have 6 text boxes.
Code:
Private Sub GA_Change()
Worksheets("Raw").Range("o37").Formula = GA.Text
NA.Text = Worksheets("Raw").Range("o38").Text
GM.Text = Worksheets("Raw").Range("o40").Text
NM.Text = Worksheets("Raw").Range("o41").Text
GW.Text = Worksheets("Raw").Range("o43").Text
NW.Text = Worksheets("Raw").Range("o44").Text
End Sub
Private Sub GM_Change()
newvalue = GM.Text
Worksheets("Raw").Range("O40").GoalSeek Goal:=newvalue, ChangingCell:=Range("O37")
NA.Text = Worksheets("Raw").Range("o38").Text
GM.Text = Worksheets("Raw").Range("o40").Text
NM.Text = Worksheets("Raw").Range("o41").Text
GW.Text = Worksheets("Raw").Range("o43").Text
NW.Text = Worksheets("Raw").Range("o44").Text
End Sub
GA Change works a treat..
GM Change errors when more than 1 number is put in..
any suggestions?
cheers
