I need to goal seek cell B1 to zero by changing C1.
Then, I need to goal seek cell B2 to zero by changing C2.
Then, I need to goal seek cell B3 to zero by changing C3.
...................
...................
I need to goal seek cell B100 to zero by changing C100.
I don't want to have to perform 100 different goal seeks. Is there a way to do them all at once?
I need to use a Multiple Cell Goal Seek.
I used the code from this site.
Here is the ....
Trying to automate Goal Seek using variables for the three arguments. It's working when I define the cells references & values within the code, but cannot figure out how using variables. I'm sure the example below is incorrect, but will give an idea what I need to accomplish:
GoalCell = Worksheets("Parts").Cells(DestRowID + 10, DestColID)
Value = Worksheets("ICA").Cells(CopyRowID + 13, CopyColID).Value
ChangeCell= Worksheets("Parts").Cells(DestRowID + 9, DestColID)
GoalCell.GoalSeek Goal:=Value, ChangingCell:=ChangeCell
CSS = P divided by S.
PS Required is a number that needs to be added to both S and P to achieve target of 83%
The answer to the below = 5. But i dont know how to get it using a formula
(If 5 is added to S and P both, S will become 36 and P will become 30; 30/36 = 83.33%)
Name S P CSS Target PS required
Adam 31 25 80.65% 83.00% ?
Goal seek function
Code:
Worksheets("Sheet1").Range("Polynomial").GoalSeek _
Goal:=15, _ ChangingCell:=Worksheets("Sheet1").Range("X")
Is it possible to modify this such that I can use variables? For example, the variables are 'left term', 'right term' and 'sigma max' .
I want to set the value of the following equation 'left term - right term' to 0 by changing variable 'sigma max' Everything is done on the userform and not in the spreadsheet.
(Note: In spreadsheet format, the above query is equivalent to setting a cell which has a formula to 0 by changing the value in another cell, fairly simple).
Say employee John Doe has a salary X. Giving him a salary raise Y (euro/dollar) induces a pension premium to be paid by the employer, on that amount.
The premium is (fixed) 17% of the part above a (fixed) treshold value of 3.750. On the part of the raise below the 3.750, the pension premium is (fixed) 4,35%.
E.g. having a salary of 3.600, with a raise of 400.
4,35% of (3.750 - 3.600 = 150) equals 6,525
And 17% of the remaining 250 equals 42,500
So the total premium is 49,025
But, I want to give a raise with a total cost of no more than 400. So, I use goal seek to find a raise that, together with te pension premiums equals 400.
In the example, with 3.600 as a salary to start with, that raise would be 358,10 Because the part below 3.750 stays the same and induces a premium of 4,35% of 150, being 6,525 and the remaining part is 208,10 at 17% is 35,377.
Proof: 358,10 + 6,525 + 35,377 = 400
The question is: is there a possibility to have a formula that calculates the (e.g.) 358,10 based on a salary to start with (e.g. 3.600), an aimed total cost (e.g. 400) and the fixed constants 3.750, 17% and 4,35%?
I'm trying to understand how complex numbers are handled/processed in Excel.
As related to my application, an interesting exercise would be to use Goal Seek w/s command to find the roots of the equation:
X^2 + 4 = 0
setting the (rounded) value in cell A2 to 0 by changing A1
A1:: 1+i
A2:: =COMPLEX(ROUND(IMREAL(IMSUM(IMPOWER(A1,2),4)),6),
ROUND(IMAGINARY(IMSUM(IMPOWER(A1,2),4)),6))
Obviously a conventional or direct use of Goal Seek wouldn't work since Excel treats complex numbers as text.
Perhaps, one should use Goal Seek twice in this case:
first: find the coefficient "a" for IMREAL(A2) = 0
second: find the coefficient "b" for IMAGINARY(A2) = 0
and the root would be "a+bi".
I've been playing around with GoalSeek in VBA because of this thread and found something about the syntax that I don't understand.
If I want to find out whether GoalSeek worked, I can use
Ok = Range("B1").GoalSeek(Goal:=10, ChangingCell:=Range("A1"))
Debug.Print Ok
This requires parentheses around the GoalSeek arguments.
If I just want to do the GoalSeek, then I need
Range("B1").GoalSeek Goal:=10, ChangingCell:=Range("A1")
with no parentheses. Why? The Help says that GoalSeek returns True if it works, and the proforma syntax includes parentheses, but then the only example uses the syntax without parentheses.
Is there a way to "copy" Goal Seek acroos multiple rows?
I have used Goal Seek in the first row. However this spreadsheet is several
hundred lines long and using it like this is not practical.
I want to seek a goal value of 30% on the cell that I called “c”, and I want the result of the goal seek to be put in the cell situated on the same row, 6 columns before.
This is the code that I tried to write, registering and saving the “goal seek” operation for one row.
Is it clear enough?
What i wrote:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/02/2010 by Whirlpool Europe Srl
'
' Keyboard Shortcut: Ctrl+w
'
On Error Resume Next
For Each c In ActiveWorkbook
If cvalue < 0.3 Then
Range("c").GoalSeek Goal:=0.3, ChangingCell:=Range offset(c,0,-6)
Next c
End Sub
I have created the following in an effort to automatically goal seek whenever changes are made to the cell C2.
Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Row = 3 And Target.Column = 2 Then
Range("D19").GoalSeek Goal:=Range("C2").Value, _
ChangingCell:=Range("D3")
End If
End Sub
As far as I know, the Macro is running all of the time? When I use goal seek manually it works fine. Assuming the above is running, when I change the value in C2 nothing happens. Does the fact that I have a three sheet workbook make any difference? I am sort of lost. By changing the value in C2 I want Excel to change the value in D19 to match C2 by adjusting D3.
Application.EnableEvents = False
Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Row = 3 And Target.Column = 2 Then
Range("D19").GoalSeek Goal:=Range("C2").Value, _
ChangingCell:=Range("D3")
End If
End Sub
Application.EnableEvents = True
Doesn't seem to work either. However, I don't really know if other settings in the workbook are correct.
I wanted to set up a macro to automate goal seek on a range of cells. I've attached a short extract below of the spreadsheet I'm working on (the full one has a few thousand lines of data) which I hope illustrates what I'm trying to do.
jan
feb
mar
apr
may
jun
jul
aug
Avg
New Avg
0
0
0
0
0
0
0
0
0
0
34
20
43
43
43
43
43
43
35
39
[Code] ...........
I am trying to get Goal Seek to seek a value on each row as such:
Project 1 : Value 1 : Goal Seek using Value 1
Project 2 : Value 2 : Goal Seek using Value 2
etc....
How do I create one macro that will pick up Value 1, 2 etc and then set the Goal Seek to that value automatically, since Value 1, 2, etc will already be available in a cell? Alternativley there could be a macro that just runs that a Goal Seek for all the rows at once?
I am trying to setup a macro to automate goal seek to calculate the required payment to meet a 12% IRR Lookback at the end of a 10 year period. The problem I am running into is that in multiple scenarios the required return is reached without any excess payment at the 10 year mark so it is returning a negative payment in the goal seek cell. In other words it's adjusting what might be a 13% IRR to a 12% IRR (the minimum return) by showing an expense at the end of the term. I need a way to set it to goalseek with a minimum of 0.
View 3 Replies View RelatedI'm trying to build a macro that iteratively runs a goal seek, then reduces a certain value (Let's call it "Bogey") in a different cell by a small amount (note that changing Bogey will affect the desired goal seek value), then runs the goal seek again, reduces Bogey by a small amount again, etc etc, and keeps running in this loop, with the restriction on this loop being that a different cell, affected by Bogey--let's call it "Constraint"--doesn't drop below a certain amount.
View 5 Replies View RelatedIs there a way to use goal seek in VBA that won't trigger a full model calculation?
I would like to (somehow) perform goal seek, but only recalculate range("A1:B50") say on a single worksheet (where both the input value and the final calculated value is included in that range), as opposed to calculating the whole model inbetween each iteration.
Also, all dependencies between the input value and the final calculated result are contained in that range.
wish to write an VB macro in Excel (2003) that populates a 6 row by 10 column matrix (for a 3 D graph) by inserting values from matrix row column headers in cells in another worksheet and using goal seek to derive a value which will be pasted in the respective cell in the 6X10 matrix
Specific example: Matrix (Row Headers A2 to A7, Column Headers B1 to K1, Cell values are in B2 to K7). Please note row and column headers are numerical values since this is for a 3D graph
Macro to be written
Pick a value (numerical) from row header A3 in Sheet A and paste it in cell B6 in another sheet (Sheet B). Pick anothet value (numerical) from column header D1 in Sheet A and paste it in cell B7 in Sheet B.
Run a goal seek scenario such that cell S10 in sheet B is equal to 50 by changing cell G8 in Sheet B
Copy the value chosen/optimised by Goal Seek from cell G8 in Sheet B and paste it in Cell D3 (which is the relevant cell for the values for intersection of headers A3 and D1) in Sheet A
Macro should populate all cells in the matrix in Sheet A (matrix cells to be populated are B2 to K7) in a similar fashion
The macro should be locked in to the cells which are either being copied from or pasted into so that it still references them even if new columns or rows are inserted.
I have the total sales for a company. Then I have four specific segments that fall under those sales. Two out of the four segments I have exact sales figures for. The other two segments I just have the year-end sales and the dollar increase in each quarter. Is there a way to solve in excel for the missing quarters. So each of the four segments would have to add up to the total sales for both the quarter and the year. I've attached my example spreadsheet too. There are two cells missing highlighted in blue but i'm going to try to obtain that data. provided i fill in those two blanks, is there way to back into the rest of this spreadsheet?
View 3 Replies View RelatedAny 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 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.
This may have been answered on here but can not seem to find it. My situation is I have values in A1,A2 & A3 that are like counter reading so the value is always changing. What I am looking to do is change the cell color if one of the values is over 500 from the other two values. Say A1 is 3000, A2 is 3250 and A3 is 3500. I would like the cell for A3 to change color.
View 2 Replies View RelatedI am very new to excel and my formulas dont add up.
I need to calculate based on my quarterly goal and my quarter to date actual sales what my percentage pacing is.
Now the way i did it is =B5/A5
Do i need to add the Quarter date calculations in there because its not giving me the accurate percentage.
Goal Actual Pacing
Basically it is another sports modelling experiment that I have had (I get the day off work and come up with things like this ), where I just had a curiosity to play out the last games for the top 3 teams in the English Premier League (just to see if I could get some percentages).
The attached, is just the values from one run of my model (I have taken the workings out to reduce the size and for other reasons).
Basically everything to the left of column K, is the model and I am happy with it (and I know goals have to be integers - this is basic);
This plays the games out, and feeds the data into the table at M36 (highlighted yellow), which feeds the data tables at M42 (highlighted green), which are counted to get percentages in the tables (highlighted yellow at M6).
I have put some example output from runs on the second worksheet (so you can see how it works - I like that it suggests Chelsea can win even though it won't be very likely )
Basically you can probably see the problem in cell O22. The percentage chance of finishing first should equal 100% when totalled (e.g. the sum of Liverpool, Chelsea and Man City finishing first, as it has to be one of them should equal 100%). I tracked this back to the fact that I had no way of splitting teams level on points. As a result I added Goal Difference to the model (crudely - e.g. decimal) and here's the question;
How would I go about using Goal Difference to break tie's effectively?
I am thinking I could do some kind of IF function in Q37 (and then have a tie-breaked rank in column R to feed the data tables), on the basis of if anything in N37 to N39 was equal but I am not sure how I would go about applying this.
Example Values_EPL.xlsx
I want to save one million dollars in 10 years earning 8.00% interest. How do I calculate how much I need to save per month.
View 11 Replies View RelatedBelow is my data point for each month. The goal I need to hit is 99%. So I need to figure out what minimum monthly percentage I need for the rest of the year, I will need to reach a goal of 99%, and if I can't reach it, return an error. Lastly, i want to be able next month to go in and fill in the AUG percentage with an absolute number (i.e. 89%) and then I would like the rest of the percentages to automatically update by figuring out the new minimum monthly average given the new value for August. I thought that I might be able to do that if there is a function that says "If cell is a number, leave it alone, if it's a formula, then include that cell in the calculation of the minimum monthly average.
Jan 89%
Feb 88%
Mar 83%
Apr 89%
May 90%
Jun 86%
Jul 82%
Aug
Sep
Oct
Nov
Dec
Goal 99%
I currently have two columns one for each of the following formulas and then another column that's works on the result of the two together but what I want to know is how do I get the two combined?
=COUNTIFS('08-09 Scores'!$C:$C,$B6,'08-09 Scores'!$CB:$CB,">="&$AY$5,'08-09 Scores'!$CB:$CB,"<="&$AZ$5,'08-09 Scores'!$AT:$AT,">"&$BA$5)
=COUNTIFS('08-09 Scores'!$C:$C,$B6,'08-09 Scores'!$CB:$CB,">="&$AY$5,'08-09 Scores'!$CB:$CB,"<="&$AZ$5,'08-09 Scores'!$W:$W,"="&$F$5)
I've put the bits that I want to be "or" in red, The first bits are obviously the same and just the last bit needs to be or.
I am basically trying to find out how many games played by the selected team had the first goal scored after a certain time(the first formula)
or the first goal was scored by the away team(the second formula)
If i have a range say E12:O12 and want to find the last cell before blank, lets say it finds m12 as the cell with the value before blank. then with m12 it needs to determine weather row 11 in the same column has Text either "S" or "F" if "S" then m12 = t if "F" then m12 offset(1,-1) = t. And just to make things more difficult i need the range E12:O12 to step 2 as well until it gets to E208:O208 .Noting that row 11 never changes and will always have either an "S" Or an "F"
and also t = time()
I would like to accomplish 2 things in my Excel 2010 spreadsheet by click a cell which already has a number and formatting in it.
1. How do I change the color of the cell, the color of the number in it, and the border around it. (Make it look like I just pressed a button by clicking it.)
2. At the same time have the text in different cell and the number in the selected cell appear in another different cell. (Text in a different cell = A , and the number in the selected cell = 23, the value in the resulting cell be "A 23") Everything I would like to happen at the same time by clicking the selected cell. I would also like this to be done several times by clicking different cells and not changing the previously selected cells.
I am working on a workbook with 200+ sheets and an index linking to each of the sheets. On the sheets there is a cell containing the results of calculations and I need the cell referencing the sheet in the index to change background colors depending if the calculations value is greater or less than 0. I currently have working code that also changes the tab color of each sheet depending on the value, but i am trying to add the index cell background change functionality into that. also, the index listings is subject to change. my working code is
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("d34").Value < 0 Then
Me.Tab.ColorIndex = 3
ElseIf Range("D34").Value > 0 Then
Me.Tab.ColorIndex = 4
Else
[Code]...
and the code i am trying to get working is
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngX As Range
Set myRange = Worksheets("Index").Range("A1:A500").Find(Range("C2").Text, lookat:=xlPart)
' the C2 is the reference the title that would be on the index
If Range("d40").Value < 0 Then
[Code]....
I keep getting the runtime error '1004: application-defined error or object-defined error the thing is, i ran the cell background changing code in a normal macro before integrating it in my other worksheet code first and it worked fine. I have a little code experience, though not much in vba and I am unsure what the issue is. if there is another better way to accomplish what I'm tring to do, that would also be fine.
cell a1 has yes cell b1 has yes and i want to type yes in c1 that will change a1 and b1 automatically to a blank cell
View 3 Replies View RelatedI'm trying to create a tag with a color border. What I desire is to fill the BLANK cells around the tag, A1:D1 + D1:D19 + A1:D19 + A1:19 in a certain color based on the text value of the cell B11. There are 5 different values, such that if the B11 read Red Sox - the boarder is going to be red, if it reads Houston Astros it will be dark blue, etc..
I have a similar problem with changing the color of the cell based on the month. So regardless of the year, 2014, 2015, 2016, etc... If I use MONTH() function I can just get numbers from 1-12. I want Cell C16-C18 to be certain color depending the date entered in cell C17 such that for each quarter, months 1-3, 4-6, 7-9, 10-12 they are different color.
I have had no luck with conditional formatting (and I also believe that it is good up to 3 cases only). I am decent in logic/programming language but have little knowledge with macro notation and especially how to run them in excel 2013. I do know how to start it alt+F11 and that I need to make sure that code is written under the specific sheet where my tag is located.
I did a search on this site and found some code I was looking for (see link:
http://www.excelforum.com/excel-prog...e-in-cell.html - Leith Ross's response code).
The code works perfectly, however, if I save the workbook with a different spreadsheet on top than the spreadsheet referred to in this code, I get an error message: Method 'Range' of object '_Worksheet' failed.
I should state that I did change "Private Sub Workbook_Open()" to "Private Sub Auto_Open()".
I assume I need to modify the code but am unsure as to how.