What is Solver's limits for constraints and adjustable cells? According to Microsoft Knowledge Base Article 75714 it is: 200 adjustable cells, and unlimited constraints for linear problems and 500 for non-linear problems for Excel 97, 98 and 2000. According to Frontline Systems, the developers of the Excel Solver, the limits are 200 adjustable cells, and 200 constraints for linear problems and 100 for non-linear problems. They do not refer to a specific version of Excel. There is a significant difference (in terms of the number of constraints) between the above two pieces of information. Can someone please clarify this matter for me? I will really appreciate it. Thanks Johan Zietsman

i am using the Solver Add In to solve an equation with 16 variables. How can
i tell the solver that the variables cannot equal each other?
"a" does not equal "b" does not equal "c"......
i know under conditional formatting, there is a "does not equal" constraint
option.
but for this add in, there is not.
any ideas?

=?Utf-8?B?c3RldmU=?=

=?Utf-8?B?c3RldmU=?=

I used Solver to maximize the sum of a series of changable numbers. The
constraints are also given. However, the solver returns some solution that
doesn't meet the constraints. Why is that?
Another question, in solver constraint, there are >=, <=, = etc. When I want
to use >0, shall I just choose >= and set the value to a number that is very
close to 0, say 0.0000001?
Thanks a million!!!

=?Utf-8?B?am9qbw==?=

=?Utf-8?B?am9qbw==?=

Hi,
I am trying to key in a binary constraint into solver, but encountered this
message
'binary constraint cell reference must include only adjustable cells'. I do
not understand what it means.
Can anyone help? Thanks.

=?Utf-8?B?RW1pbHl6?=

=?Utf-8?B?RW1pbHl6?=

Hi, all!
The "almost final" answer to my analysis is contained in six cells, sa
a1 - f1 with the sum of those in g1. The last step is to be sure tha
none of the cells a1 - f1 exceeds 20% of the total in g1. A1 - F1 ca
get smaller than they are now, but not bigger.
I've been using Excel for years and just can't work this one out. An
ideas are appreciated.
TIA
Ji
--
Message posted from http://www.ExcelForum.com

JBoulton <

JBoulton <

Hi All,
I would like to use solver to solve a problem I am facing
with at present. I am refitting floorboards in my room
and would like to minimize wasting material.
Floor boards are the same width and come in varyaing
lengths (i.e. I can cut them shorter) and should be laid
down in the same way across the whole room. Furthermore, 3
or more floorboards are required to cover one length of
the room.
Question... My original idea was to have just one area
constraint (i.e. the sum of the area of the boards used
should >= the sum of the area of the room), but then I
thought that this could mean that I would have to lay
floorboards in mixed direction. Then I thought about
having constraints for lentgh and width of the room.
How do I specify the constraints for the length and width
of the room?
Many thanks for your help,
Pascal.

Pascal Tomasi

Pascal Tomasi

Via sql , I created a unique constraint on my table , but in SSMS it doesn't
show up in the Constraints list but rather in the Indexes list. I know that
Sql Server internally implements unique constraints as indexes , but why
won't it show up in the SSMS Constraints list ? Is that list for default
constraints only ?
CREATE TABLE [dbo].[CruiseShipPortOfCall](
[Id] [int] IDENTITY(1,1) NOT NULL,
[CruiseShipId] [int] NOT NULL,
[PortOfCallId] [int] NOT NULL,
CONSTRAINT [PK_CruiseShipPortOfCall] PRIMARY KEY CLUSTERED
(
[Id] ASC
),
CONSTRAINT [CK_CruiseShipPortOfCall_CruiseShipId_PortOfCallId] UNIQUE
NONCLUSTERED
(
[CruiseShipId] ASC,
[PortOfCallId] ASC
)
)

John A Grandy

John A Grandy

I've just discovered that I can edit the data values in a chart by draging
the points on the graph.
This feature will allow me to use Excel as a simple data entry tool.
But it appears this by-passes the cell validation.
For example if I have a line chart
X Y
10 5
20 8
30 9
I can drag the 2nd point to any value.
What I want to do is limit it to be within the 2 values surrounding it.
i.e. 10<X2<30 & 5<Y2<9
Does anyone have any idea how I can achieve this? I've tried searching for
this in a number of places, but nowhere seems to mention the ability to drag
points about. Even knowing the 'official' name for this feature would be an
aid to continue searching.
Cheers
DAve

DAve Shillito

DAve Shillito

Could use some help with the following:
I created a spreadsheet to track payments made to contractors. In this
spreadsheet I have a simple formula that tells when the payment has to be
made based on the date it was rec'd from the contractor and adding on the
payment terms.
Now what I have to do is add in some date constraints. Our paying office
only processes payments between certain windows during the month. Lets say
that for this month the payment window closes Jan 27th to Feb 2nd. What I
need to do is if after someone enters the date the payment is rec'd and the
14 days is added for the payment terms, should the date the payment is made
end up falling within the payment black-out period (Jan 27th to Feb 2nd), I
then need excel to adjust the must be paid by date to 3 days prior to the
black-out period.
Hope this makes sense....I've tried a whole bunch of different things and
have looked online at several sites with no luck. Would SERIOUSLY appreciate
it if someone can help me!
Thanks!

=?Utf-8?B?V2FycmlvciBQb3Bl?=

=?Utf-8?B?V2FycmlvciBQb3Bl?=

....that I can put in an IF formula?
I have a chart with age bands (i.e 20-24, 25-29, 30-35, etc. and
corresponding amounts for those age bands (247.84, 256.69, 274.39,
respectively).
My current formula reads:
=IF(I116<25,247.84,IF(I116<30,256.69,IF(I116<35,274.39,IF(I116<40,295.63,IF(I116<45,322.21,IF(I116<50,354.08,IF(I116<55,404.51,IF(I116<60,490.7,IF(I116<65,652.03,IF(I116>=65,884.34,N/A))))))))
Excel is giving me an error message at the IF where IF(I116<65,652.03....
I've double checked to make sure there are no spaces, and when I remove that
constraint and the ones that follow, the formula works.
Help?

=?Utf-8?B?T21ha2JvYg==?=

=?Utf-8?B?T21ha2JvYg==?=

Pretty straight forward solver problem, supply/demand constraints, ie Pounds Made <= Demand. What I also want to add as a constraint is that Pounds Made >= 10%ofDemand. Message to this formula
Cell Ref: $Demand$Cells >= Constraint: =0.1*$D$9:$I$
"Constraint must be number, simple reference or a formula with a numeric value
Looks like a formula to me,
Tried: Constraint: =0.1*($D$9:$I$9
Tried: Constraint: =(0.1*$D$9:$I$9
On line help say I can enter a formula, using Excel 2003. Found no help with supplied disks and on line help gaveno examples of using a formul

=?Utf-8?B?RGF2aWQ=?=

=?Utf-8?B?RGF2aWQ=?=

I want the constraints on the changing cells to be that they can only change
to 0 or not change at all. How can I do this?

=?Utf-8?B?RG9ubmE=?=

=?Utf-8?B?RG9ubmE=?=

A little background on the problem I'm trying to solve:
I'm trying to make excel figure out the idea layout of my shop (using x and
y coordinates of each machine) I have part sequences for individual parts of
what machines they travel to, and a total distance traveled. I'm trying to
minimize the total distance traveled by changing the coordinate cells that
represent the machine locations.
It works, only problem is that it sets all of the machines to the same
coordinates (obviously impossible because I cant stack machines one atop the
other).
How do i introduce a constraint that tells solver "do not use the same
coordinates for each machine"
I've tried making if statements that return a value of 1 of the machines do
not have the same coordinate, and -1 if they do and then introducing the
constraint where those cells must be greater than 0, but that doesn't seem to
work
any suggestions?

=?Utf-8?B?RGFu?=

=?Utf-8?B?RGFu?=

Is it possible to add a constraint that will round the Changed cell to the nearest 5?
Thanks
AJ

=?Utf-8?B?QWxsYW5K?=

=?Utf-8?B?QWxsYW5K?=

Please could someone give me the definition of this. I would greatly
appreciate it. i need to know in order to continue. thanks in advance.

=?Utf-8?B?a2ltYmVy?=

=?Utf-8?B?a2ltYmVy?=

Is there a known bug where the solver, in nonlinear mode,
ignores constraints? I'm constraining a decision variable to
be positive and getting a negative answer.
Here is a recording of the procedure:
SolverReset
SolverAdd CellRef:="$B$2", Relation:=3, FormulaText:="0.000001"
SolverAdd CellRef:="$C$2", Relation:=3, FormulaText:="0.000001"
SolverAdd CellRef:="$D$2", Relation:=3, FormulaText:="0.000001"
SolverAdd CellRef:="$E$2", Relation:=3, FormulaText:="0.000001"
SolverOk SetCell:="$A$2", MaxMinVal:=1, ValueOf:="0",
ByChange:="$B$2:$E$2"
SolverSolve
The D2 is coming up negative.
Thanks and Regards,
-Larry (New to Excel) Curcio

Larry Curcio

Larry Curcio

Hi
I've a very crucial data on my computer that need to be kept away from
prying eyes (especially the formulaes)
Kindly tell me a way by which I can make the formulas EXPIRE or worksheet
locked after a certain specific interval.
Example
I created a worksheet on 26-may-2004.
I wnat that the worksheet became read-only or hidden on 28-may-2004.
can i achieve that.
thanx
deepak

Deepak

Deepak

I tried to run a linear program on solver and got a error message that my
problem was too large for solver to solve. (The constraints were included as
a 3 x 168 matrix). I had successfully run a similar problem where the
constraints were included in a 5 x 168 matrix, so I can't figure out what the
problem is. The size of the problems I would like to run would be some large
number (as large as possible) x 168. Thanks so much for your help.

=?Utf-8?B?Sm9MZWlnaA==?=

=?Utf-8?B?Sm9MZWlnaA==?=

When using Solver to optimize several cells in a simple system of equations,
Solver violates several of the constraints I input. I've used Solver with
constraints hundreds of times before without problems. Has anyone
encountered this issue?

=?Utf-8?B?bWNkYXZpczYyNg==?=

=?Utf-8?B?bWNkYXZpczYyNg==?=

I execute the following code and on one worksheet within the workbook it adds
the contraints fine. On the other worksheets it does not add the constraints
at all unless the formula text is not set to 1. Has anyone run into this
same issue? Any help would be greatly appreciated.
Thanks
Function calculate()
SolverReset
SolverAdd CellRef:="$AW$10", Relation:=2, FormulaText:="1"
SolverAdd CellRef:="$AW$11", Relation:=2, FormulaText:="1"
SolverOk SetCell:="$AW$31", MaxMinVal:=3, ValueOf:="0", ByChange:= _
"$AB$10:$AB$24,$AC$11:$AC$24,$AD$12:$AD$24,$AE$13:$AE$24,$AF$14:$AF$24,$AG$15:$AG$24,$AH$16:$AH$24,$AI$17:$AI$24,$AJ$18:$AJ$24,$AK$19:$AK$24,$AL$20:$AL$24,$AM$21:$AM$24,$AN$22:$AN$24,$AO$23:$AO$24,$AP$24"
SolverOptions MaxTime:=1000, Iterations:=1000, Precision:=0.0000001, _
AssumeLinear:=False, StepThru:=False, Estimates:=1, Derivatives:=1, _
SearchOption:=1, IntTolerance:=5, Scaling:=False,
Convergence:=0.0000001, _
AssumeNonNeg:=False
SolverSolve userFinish:=True
End Function

=?Utf-8?B?Q0JC?=

=?Utf-8?B?Q0JC?=

I am building a data warehouse and have created the model in Visio 2003 Enterprise arch. Within the model, I have relationships between various tables. However when I generate the database or update it, I do not want to pass over the constraints, since the data warehouse will maintain the integrity during the loading
Is there some way that I can turn off the creation of the constraints in the database while keeping them in the model
Joe

=?Utf-8?B?Sm9lIFNhcm5h?=

=?Utf-8?B?Sm9lIFNhcm5h?=

I have a fairly basic table of bolts and their attributes...
A B C
1 SIZE LENGTH STRENGTH
2 .5 1/2 100
3 .5 3/4 150
4 .75 1/2 150
5 .75 3/4 200
Cell D6 is an input cell for "size"
cell D7 is an input cell for "strength"
Cell D8 is the resultant lookup
I want to do a vlookup(?) that finds the correct length based upon BOTH size
(D6) AND strength (D7). "Size" will always find an exact match but
"strength" needs to be the first strength that is >= the input strength.
Any help is much appreciated.
S

=?Utf-8?B?VGVjaE1HUg==?=

=?Utf-8?B?VGVjaE1HUg==?=

=SUMPRODUCT(--(A2:A213>=C224),--(A2:A213<=C225),--(W2:W213=1),H2:H213)/SUMPRODUCT(--(A2:A213>=C224),--(A2:A213<=C225),--(W2:W215=1),H2:H213)
This is the suggestion one of you gave me, however it doesn't seem to work.
col A are dates
I've been working on this for hours and can't get it??
Please help

=?Utf-8?B?anJoZWluc2NobQ==?=

=?Utf-8?B?anJoZWluc2NobQ==?=

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel
I'm running a linear optimization in Solver and I need to add the constraint that the solution variables be integers.
I click "add" next to the constraints, select the cells in question (now blank, unsolved), and select "int" from the options. I leave the "Constraint" box blank. I get the error message "Constraint must be a number, simple reference, or formula with a numeric value."
This does not happen on a PC. Any suggestions?

TR

TR

I'm getting an error message when i try to do a typical rebuild that
says there isn't enough space to complete the process and create a new
identity. what do i need to do to make it work?

ullabritt

ullabritt

Is it possible to set this option for Foreign Key and Check constraints from
Visio?
If not... does anybody know the table that holds this flag in SQL Server so
I can script the change?
Thanks in advance,
Andrew Stanford

Andrew Stanford

Andrew Stanford

Hi,
I'm trying to create a 12 month portfolio return stream where I know
the final return and I know the volatility. I want these two things,
the return and the volatility, to be my constraints and then I want
excel to randomly populate the 12M of data based upon that. I thought
goal seek was the way to go, but there's no where to put a second
constraint that I can see. Any suggestions?

Boom1

Boom1

If the solver is attempting to optimize a cell with a function in it,
and the input cell to the function has an integer constraint on it, the
solver won't do anything.
For example:
set A1 = 1
set B1 = "=dummy(A1)"
create a function in a module like so:
public function dummy( x as integer ) as integer
dummy = x * 10
end function
optimize for the maximum value of B1, constraining A1 like so:
A1 >= 1
A1 <= 10
A1 is an integer
...And the solver does nothing. What is the proper way to do this?

dave.rafkind

dave.rafkind

I need to count instances of particular cell content (alphanum)within a
filtered data table - Data table is filtered on COL A, Range to be
parsed is in COL B.
COUNTA counts instances within total range, and does change if the
range is filtered. Combining SUBTOTAL and IF statements produce #VALUE
errors, EG:
=IF([range]=[ref],SUBTOTAL(3,[range]),0)
=SUBTOTAL(3,IF([range]=[ref],[range]),0)
=SUBTOTAL(3,COUNTIF([range], [ref]))
Condition sums and nested COUNTIF statements do not work either.
In plain language, the function should: Count the instances where
visible (filtered) cells in a selected range (column) contain a
specified value.
------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

gmilton

gmilton

Hi!
I need to write a script that will remove any constraints and triggers from
any table in a specified database.
My problem is that i don't know where are stored in the system tables the
constraints and relations... I've been told that once this would be found,
i would just have to use cursor in order to remove those.
And by the way, i'll need to re-apply the constraints after a couple of
operations on the databases...
Is this something i can possibly do? Do you have any clue or tips for me?
Thanks!
Etienne

Etienne M. St-Georges

Etienne M. St-Georges

Does anyone have a user-defined chart that can autobuild the OPTIMAL RESULT
and CONSTRAINT from SOLVER. Please share...

=?Utf-8?B?cm1s?=

=?Utf-8?B?cm1s?=

Hi there,
I would like to know if the integrity constraint in schema can be used in
Excel in order to check them on a xls file mapped with this schema ? What I
mean for instance is : map a xls file with an xsd schema which states that a
"column" is a unique key, and then validate this file against this schema
to verify that the values in that column are really unique.
Best regards

Oriane

Oriane

Hi.
I am trying to do the following...
I have a column list with 1 column that lists 4 Product
Groups ( Group1, Group2, Group3, Group4). Call this
List 1.
AND. I have a column list with 3 columns that list (1)
Product Groups, (2)Product, and (3)Quantity. Call this
List 2.
I would like to see if excel can look at List 1 and for
each of the 4 Product Groups, select from List 2 the
Product with the greatest Quantity. In the case where the
greatest Quantity is equal for a given Product Group, I do
not care which Product is selected for List 1.
This is a simplification of the task I am trying to do.
List 1 actually has 3000 Product Groups and List 2 has
25,000 Products.

cmiedaner

cmiedaner

Hi,
Is it possible for excel in a selected set of cells, to place a letter (A,
B, C or D) in each of the cells "randomly" so that at the end there is a
total of 10 A's for example, 8B's, 9C's and 1D... overall?
Also, whenever anything is randomly generated in Excel, is there a way to
stop the values refreshing to new values everytime the user clicks something?
Any help would be most appreciated,
Thanks.

=?Utf-8?B?cmVhbG1maWdodGVy?=

=?Utf-8?B?cmVhbG1maWdodGVy?=

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: intel
Hi,
I'm having the exact problem that is described in this old (2005) maxosx.com discussion: <http://www.macosx.com/forums/mac-os-x-system-mac-software/256248-excel-solver-constraints.html>
I can't find information anywhere that indicates this is a common issue. Has anyone else experienced this? Is there an obvious fix? e.g. replace the user :) If this is a real bug, I find it hard to believe I'm the first person to discover it.
There is a Microsoft knowledge base article that is suspiciously similar, but it deals with German and Polish versions for Windows: <http://support.microsoft.com/kb/826118>
Thanks.

E._Thompson

E._Thompson

I currently have a spreadsheet tracking the performance of a purchasing
group. There are 3 columns of interest to me in this formula. Column D is
the purchasing group, some are names, some are acronyms, but the groups I'm
concerned with are all named "Purchasing____" followed by a number, so I have
been using the "Purchas*" value to capture all of those. Column J is the
date a purchasing group has received an order, and Column P is the date the
purchasing group sends out its purchase order. This data is being captured
in a separate workbook, and there is also a date criteria. So I have set up
hidden cells in the workbook capturing the data, with the corresponding
column head and values I'm looking matching up with column D, J, and P. J
and P have two hidden cells each, to represent the upper and lower bounds of
the date ranges I'm looking for. This is to make the various database
functions available (I've been using DCOUNTA for other data I wish to
capture, not sure if this all is necessary for this instance).
I'm looking to come up with a function that will go to every entry in column
D that is a "Purchasing____" (hence the "Purchas*"), find the NETWORKDAYS
between J and P, and then average them by month (hence the date constraints).
Any help would be much appreciated, thanks.

=?Utf-8?B?QnJldHRT?=

=?Utf-8?B?QnJldHRT?=