Hi. The use of an "Or" function will not work with Excel as Solver does not
know what to do when it is trying out a values between those given. Here
is one common way to do this type of problem using Excel's Solver.
Here, we will try to have Solver change A1 to one of the 8 values that you
listed.
1). List those 8 values in B1:B8 (ie 1,2,3,4,7,10,100,500)
2). Select C1:C8, and give them a different background color for now to
help you keep track of this range. Solver is going to adjust this range:
C1:C8
3) In A1, enter the following function: =SUMPRODUCT(B1:B8,C1:C8)
4) In C9, enter the following function: =SUM(C1:C8)
5) Now, set up Solver. Add the constraint that C1:C8 are "Bin" This
limits the values to 0 or 1. But, we only want one of those cells to be 1,
the others 0. So, we add the constraint that C9 =1.
However, Excel's solver is not too sophisticated. It won't use an exact
value of 1 in C1:C8. So, as a "technique", make the constraint in C9 both
the following....C9=0.99, and C9 <= 1.01.
Now, when Solver stops, one of the values in C1:C8 will be 1, and the
corresponding value in column B will be displayed in A1.
Here's another technique.:
Enter the following long formula in A1.
=252+B1*(-(13343/21)+B1*(110749/180+B1*(-(2708/9)+B1*(11705/144+B1*(-(1757/144)+(679/720-(29*B1)/1008)*B1)))))
This is simply a polynomial of the 8 values. We use a helper cell in B1.
Add the constraint that B1 is "INT", (an integer value).
Add the constraints that B1 =.99, and B1 <= 8.01
As B1 varies from 1,2,3,4,5,6,7 or 8, the corresponding value in A1 will be
one of your 8 values.
Hope this helps.
--
Dana DeLouis
Win XP & Office 2003
"MrShorty" wrote in
message ...
Flintstone's formula would allow you to check to see if Solver returned
one of the desired values. As for constraining solver to one of those
values, I didn't readily see how to do it. I tried setting a
constraint that Flintstones formula = TRUE, but it just gave me an
error.
Are those the only seven possibilities? If I only had seven choices to
choose from, I'd probably do it by hand. Evaluate my
formula/spreadsheet for each of the seven inputs and use the computer
between my ears to determine which of the inputs satisfied my
criteria.
Alternatively, it could be set up over seven rows or sheets (depending
on complexity), then use MAX(), MIN(), or some other combination of
worksheet functions to extract the desired outcome.
--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile:
http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=376929