Identify the cells that add up to zero
m_r_be...@my-deja.com
With Windows NT
And Excel '97
End With
=====================================
Suppose you have the following in a column:
(68,062.50)
4,125.00
(5,221.17)
(336.85)
5,558.02
It may not appear quite obvious that the last three values add up to
zero.
If you have a lot more cells in the column, finding those cells that add
up to zero will be quite a challenge. So my question is: What's the code
that will find just that? Say, highlight those cells that add up to
zero in red.
The top cell can be called TopAddress, and the bottom cell BottomAddress
(Or you can use "selection" or set a range if you like).
Marc
Thanks.
Sent via Deja.com http://www.deja.com/
Before you buy.
m_r_be...@my-deja.com
can be more than 2 cells involved, as in the example. Alright the Pros,
who's the genius?
================================================================
Sub Add_Up_To_Zero()
Dim vals(1 To 5), i, matches As Integer
matches = 0
TA = ActiveCell.Row
ActiveCell.End(xlDown).Select
BA = ActiveCell.Row
For i = TA To BA
vals(i) = Cells(i, 1).Value
If i > 1 Then
For j = 1 To i - 1
If vals(i) + vals(j) = 0 Then
Range(Cells(TA, 1), Cells(BA, 1)).Interior.Color =
xlAutomatic
Cells(i, 1).Interior.ColorIndex = 3 + matches
Cells(j, 1).Interior.ColorIndex = 3 + matches
matches = matches + 1
End If
Next
End If
Next
End Sub
================================================================
In article <8uetpn$agr$1...@nnrp1.deja.com>,
m_r_be...@my-deja.com
Tom Ogilvy
combinations (or permutations) of n things taken r at a time. You could use
you cell addresses as the n things, then build a loop to vary r from 2 to n.
Use the addresses it produces as the argument to the sum function and keep
those that sum to zero.
Deja URL:
'http://x59.deja.com/=dnc/getdoc.xp?AN=650778336.1
'Since you asked, here it is. It is generic, i.e. it isn't written
specifically
'or a given population and set size, as yours it. It will do permutations or
'combinations. It uses a recursive routine to generate the subsets, one
routine
'for combinations, a different one for permutations.
'Author: Myrna Larson <myrna...@home.com>
'Date: 2000/07/25
'Forum: microsoft.public.Excel.misc
'To use it, you put the letter C or P (for combinations or permutations) in
a
'cell. The cell below that contains the number of items in a subset. The
cells
'below are a list of the items that make up the population. They could be
'numbers, letters and symbols, or words, etc.
'You select the top cell, or the entire range and run the sub. The subsets
are
'written to a new sheet in the workbook.
Option Explicit
Dim vAllItems As Variant
Dim Buffer() As String
Dim BufferPtr As Long
Dim Results As Worksheet
Sub ListPermutations()
Dim Rng As Range
Dim PopSize As Integer
Dim SetSize As Integer
Dim Which As String
Dim N As Double
Const BufferSize As Long = 4096
Set Rng = Selection.Columns(1).Cells
If Rng.Cells.Count = 1 Then
Set Rng = Range(Rng, Rng.End(xlDown))
End If
PopSize = Rng.Cells.Count - 2
If PopSize < 2 Then GoTo DataError
SetSize = Rng.Cells(2).Value
If SetSize > PopSize Then GoTo DataError
Which = UCase$(Rng.Cells(1).Value)
Select Case Which
Case "C"
N = Application.WorksheetFunction.Combin(PopSize, SetSize)
Case "P"
N = Application.WorksheetFunction.Permut(PopSize, SetSize)
Case Else
GoTo DataError
End Select
If N > Cells.Count Then GoTo DataError
Application.ScreenUpdating = False
Set Results = Worksheets.Add
vAllItems = Rng.Offset(2, 0).Resize(PopSize).Value
ReDim Buffer(1 To BufferSize) As String
BufferPtr = 0
If Which = "C" Then
AddCombination PopSize, SetSize
Else
AddPermutation PopSize, SetSize
End If
vAllItems = 0
Application.ScreenUpdating = True
Exit Sub
DataError:
If N = 0 Then
Which = "Enter your data in a vertical range of at least 4 cells. " _
& String$(2, 10) _
& "Top cell must contain the letter C or P, 2nd cell is the number " _
& "of items in a subset, the cells below are the values from which " _
& "the subset is to be chosen."
Else
Which = "This requires " & Format$(N, "#,##0") & _
" cells, more than are available on the worksheet!"
End If
MsgBox Which, vbOKOnly, "DATA ERROR"
Exit Sub
End Sub
Private Sub AddPermutation(Optional PopSize As Integer = 0, _
Optional SetSize As Integer = 0, _
Optional NextMember As Integer = 0)
Static iPopSize As Integer
Static iSetSize As Integer
Static SetMembers() As Integer
Static Used() As Integer
Dim i As Integer
If PopSize <> 0 Then
iPopSize = PopSize
iSetSize = SetSize
ReDim SetMembers(1 To iSetSize) As Integer
ReDim Used(1 To iPopSize) As Integer
NextMember = 1
End If
For i = 1 To iPopSize
If Used(i) = 0 Then
SetMembers(NextMember) = i
If NextMember <> iSetSize Then
Used(i) = True
AddPermutation , , NextMember + 1
Used(i) = False
Else
SavePermutation SetMembers()
End If
End If
Next i
If NextMember = 1 Then
SavePermutation SetMembers(), True
Erase SetMembers
Erase Used
End If
End Sub 'AddPermutation
Private Sub AddCombination(Optional PopSize As Integer = 0, _
Optional SetSize As Integer = 0, _
Optional NextMember As Integer = 0, _
Optional NextItem As Integer = 0)
Static iPopSize As Integer
Static iSetSize As Integer
Static SetMembers() As Integer
Dim i As Integer
If PopSize <> 0 Then
iPopSize = PopSize
iSetSize = SetSize
ReDim SetMembers(1 To iSetSize) As Integer
NextMember = 1
NextItem = 1
End If
For i = NextItem To iPopSize
SetMembers(NextMember) = i
If NextMember <> iSetSize Then
AddCombination , , NextMember + 1, i + 1
Else
SavePermutation SetMembers()
End If
Next i
If NextMember = 1 Then
SavePermutation SetMembers(), True
Erase SetMembers
End If
End Sub 'AddCombination
Private Sub SavePermutation(ItemsChosen() As Integer, _
Optional FlushBuffer As Boolean = False)
Dim i As Integer, sValue As String
Static RowNum As Long, ColNum As Long
If RowNum = 0 Then RowNum = 1
If ColNum = 0 Then ColNum = 1
If FlushBuffer = True Or BufferPtr = UBound(Buffer()) Then
If BufferPtr > 0 Then
If (RowNum + BufferPtr - 1) > Rows.Count Then
RowNum = 1
ColNum = ColNum + 1
If ColNum > 256 Then Exit Sub
End If
Results.Cells(RowNum, ColNum).Resize(BufferPtr, 1).Value _
= Application.WorksheetFunction.Transpose(Buffer())
RowNum = RowNum + BufferPtr
End If
BufferPtr = 0
If FlushBuffer = True Then
Erase Buffer
RowNum = 0
ColNum = 0
Exit Sub
Else
ReDim Buffer(1 To UBound(Buffer))
End If
End If
'construct the next set
For i = 1 To UBound(ItemsChosen)
sValue = sValue & ", " & vAllItems(ItemsChosen(i), 1)
Next i
'and save it in the buffer
BufferPtr = BufferPtr + 1
Buffer(BufferPtr) = Mid$(sValue, 3)
End Sub 'SavePermutation
Regards,
Tom Ogilvy
m_r_be...@my-deja.com
Last night, I was working on how to -- list -- the combinations from the
choosing of n picks out of k subjects. Of course, given the
complexities that come about as k and n grow, things just have to
give way to some serious math...So I limited myself with just 4
rows (as in my example) and used simple "If" statements in my code.
You bet I was wondering how I could get started with VBA code with
regards to this combination business. And here comes Mirna!
Mirna, Mirna, Mirna...you did it again.
Thanks Tom.
Marc R. Bertrand
In article <OcZUQ9rSAHA.196@cppssbbsa04>,
Myrna Larson
Yes, but someone else -- I think is was Dana DeLouis, (but if not, apologies
to the author) -- just in the past few days showed how to solve your
fundamental problem using Solver.
This was REALLY SLICK !!!! I'd always thought that Excel couldn't do it.
Problem: You have 5 numbers in A1:A5. You want to find a combination of those
numbers whose sum is 0.
In B1:B5, put the number 1.
In B6, put the formula =SUMPRODUCT(A1:A5,B1:B5).
In B7, put the formula =COUNTIF(B1:B5,1)
Then use Solver.
The Target cell is B6
The goal is that B6 has a value of 0
The cells to change are B1:B5
The constraints are:
B1:B5 must be "bin" (i.e. binary -- 1 or 0 -- which wasn't obvious to me)
B7 must be >= 1
Without the formula in B7 and that last constraint, Solver obligingly sets B1
to B5 to all 0's <g>.
(You could, of course, say the contraints for B1:B5 are that they must be
integers, must be >= 0 and must be <= 1, but that's 3 constraints instead of
1.)
m_r_be...@my-deja.com
Forget Dana! It's you I want to marry!
:-)
Thanks for everything.
Marc.
In article <tkio0tsm74g9vfesu...@4ax.com>,
> >I don't believe it!
> >
> >Last night, I was working on how to -- list -- the combinations from
the
> >choosing of n picks out of k subjects. Of course, given the
> >complexities that come about as k and n grow, things just have to
> >give way to some serious math...So I limited myself with just 4
> >rows (as in my example) and used simple "If" statements in my code.
> >
> >You bet I was wondering how I could get started with VBA code with
> >regards to this combination business. And here comes Mirna!
> >
> >Mirna, Mirna, Mirna...you did it again.
> >
> >Thanks Tom.
>
>
Myrna Larson
>Forget Dana! It's you I want to marry!
Are you sure??? I was 64 back in September, and will be on Medicare in another
10 months <g>. Besides that, I don't think my husband would think much of the
arrangement.
m_r_be...@my-deja.com
any more...But tell your lucky husband a smart woman is a man's best
friend and that a lot of us are jealous!
Marc
In article <podp0t02fjl07cpcm...@4ax.com>,
m_r_be...@my-deja.com
What if you want your cell B6 to be less than a certain amount instead
of zero, say less than 5?
You see, zero works very well for perfect cancellations of amounts in
the column, but I need solver to work for cancellations whose result is
in absolute terms less than 5 (the differences equal to less than 5
dollars in our case can be written off). The "<" cannot be used in the
Solver box, and we can't put an expression in.
So Mirna, are you up to it?
Don't stay up all night over this. Think about your Honey.
Marc
In article <tkio0tsm74g9vfesu...@4ax.com>,
> >I don't believe it!
> >
> >Last night, I was working on how to -- list -- the combinations from
the
> >choosing of n picks out of k subjects. Of course, given the
> >complexities that come about as k and n grow, things just have to
> >give way to some serious math...So I limited myself with just 4
> >rows (as in my example) and used simple "If" statements in my code.
> >
> >You bet I was wondering how I could get started with VBA code with
> >regards to this combination business. And here comes Mirna!
> >
> >Mirna, Mirna, Mirna...you did it again.
> >
> >Thanks Tom.
>
>
m_r_be...@my-deja.com
====================================================================
Sub Good_For_0()
SolverOk SetCell:="$B$6", MaxMinVal:=3, _
ValueOf:="0", ByChange:="$B$1:$B$5"
SolverAdd CellRef:="$B$1:$B$5", Relation:=5, FormulaText:="binary"
SolverAdd CellRef:="$B$7", Relation:=3, FormulaText:="1"
SolverSolve (True)
End Sub
=====================================================================
The argument "ValueOf:=" is pretty much fixed to accept values only,
isn't it? I tried a "<5" but the answer was all wacked up. I wonder if
assigning a variable to the argument would do the trick or if do to the
math behind the scenes being specific to a value goal no expression can
be set. I wish I were Mr. Pascal, but...
Marc R. Bertrand
In article <8un7h5$irt$1...@nnrp1.deja.com>,
> > >I don't believe it!
> > >
> > >Last night, I was working on how to -- list -- the combinations
from
> the
> > >choosing of n picks out of k subjects. Of course, given the
> > >complexities that come about as k and n grow, things just have to
> > >give way to some serious math...So I limited myself with just 4
> > >rows (as in my example) and used simple "If" statements in my code.
> > >
> > >You bet I was wondering how I could get started with VBA code with
> > >regards to this combination business. And here comes Mirna!
> > >
> > >Mirna, Mirna, Mirna...you did it again.
> > >
> > >Thanks Tom.
> >
> >
>
Myrna Larson
On Sun, 12 Nov 2000 23:02:00 GMT, m_r_be...@my-deja.com wrote:
>What if you want your cell [B8] to be less than a certain amount instead
>of zero, say less than 5?
This sometimes takes a bit of juggling and ingenuity, and some additional
formulas (like what I show for B8 and B9, below).
Let's say we are dealing with a list of outstanding invoices, and the customer
sends a partial payment, but he doesn't indicate which invoices it is to be
applied to. You want to select the best group of invoices to mark them paid.
You have invoice amounts in A1:A5, and you put the number 1 in B1:B5.
B6 contains the formula =SUMPRODUCT(A1:A5,B1:B5)
B7 contains the amount of the payment
B8 contains the formula =ABS(B6-B7)
B9 contains the formula =SUM(B1:B5)
Set the target as B8 (which is the difference between the amount paid and the
invoices marked as paid), and say that Solver should set it to the minimum
instead of to a particular value.
The constraints are
B1:B5 integer
B1:B5 >= 0
B1:B5 <= 1
B9 >= 1
I couldn't get B1:B5 binary to work reliably, so I had to use the 3
constraints for the 1's and 0's. B9 >= 1 just means that at least one invoice
must be marked as paid.
m_r_be...@my-deja.com
or an array formula might just do the job as well.
Marc
In article <8un8og$jps$1...@nnrp1.deja.com>,
m_r_be...@my-deja.com wrote:
> The code for the small probem is this:
> ====================================================================
> Sub Good_For_0()
>
> SolverOk SetCell:="$B$6", MaxMinVal:=3, _
> ValueOf:="0", ByChange:="$B$1:$B$5"
> SolverAdd CellRef:="$B$1:$B$5", Relation:=5, FormulaText:="binary"
> SolverAdd CellRef:="$B$7", Relation:=3, FormulaText:="1"
> SolverSolve (True)
>
> End Sub
> =====================================================================
>
> The argument "ValueOf:=" is pretty much fixed to accept values only,
> isn't it? I tried a "<5" but the answer was all wacked up. I wonder if
> assigning a variable to the argument would do the trick or if do to
the
> math behind the scenes being specific to a value goal no expression
can
> be set. I wish I were Mr. Pascal, but...
>
> Marc R. Bertrand
>
> In article <8un7h5$irt$1...@nnrp1.deja.com>,
> m_r_be...@my-deja.com wrote:
> > OK, here is a challenge I believe:
> >
> > What if you want your cell B6 to be less than a certain amount
instead
> > of zero, say less than 5?
> >
> > > The constraints are:
> > > B1:B5 must be "bin" (i.e. binary -- 1 or 0 -- which wasn't
> obvious
> > to me)
> > > B7 must be >= 1
> > >
> > > Without the formula in B7 and that last constraint, Solver
> obligingly
> > sets B1
> > > to B5 to all 0's <g>.
> > >
> > > (You could, of course, say the contraints for B1:B5 are that they
> > must be
> > > integers, must be >= 0 and must be <= 1, but that's 3 constraints
> > instead of
> > > 1.)
> > >
> > > >I don't believe it!
> > > >
> > > >Last night, I was working on how to -- list -- the combinations
> from
> > the
> > > >choosing of n picks out of k subjects. Of course, given the
> > > >complexities that come about as k and n grow, things just have to
> > > >give way to some serious math...So I limited myself with just 4
> > > >rows (as in my example) and used simple "If" statements in my
code.
> > > >
> > > >You bet I was wondering how I could get started with VBA code
with
> > > >regards to this combination business. And here comes Mirna!
> > > >
> > > >Mirna, Mirna, Mirna...you did it again.
> > > >
> > > >Thanks Tom.
> > >
> > >
> >
m_r_be...@my-deja.com
You know the A1:A5 little group of cells...well I have hundreds of them
in a column. Some groups consist of one or two or three rows only while
others can consist of as much as a dozen rows. Your introducing a cell
wich contains some number which I just don't have does't make sense in
this particular problem. It's good though for the invoice problem you
describe.
In this particular problem, you must deal only with what you have: only
the one group of cells in A1:A5, or rather those groups of cells
separated by a blank row. You see I want to be able to loop through
those groups of cells down the column (say column A) and have the
results input in another column (say column B). If I have a model that
works for one group of cells, I can loop and apply that model to all
groups.
Sorry.
Marc
In article <qv9u0top8c5cs4ml1...@4ax.com>,
Myrna Larson <myrna...@home.com> wrote:
>
> On Sun, 12 Nov 2000 23:02:00 GMT, m_r_be...@my-deja.com wrote:
>
> >What if you want your cell [B8] to be less than a certain amount
instead
> >of zero, say less than 5?
>
> This sometimes takes a bit of juggling and ingenuity, and some
additional
> formulas (like what I show for B8 and B9, below).
>
> Let's say we are dealing with a list of outstanding invoices, and the
customer
> sends a partial payment, but he doesn't indicate which invoices it is
to be
> applied to. You want to select the best group of invoices to mark them
paid.
>
> You have invoice amounts in A1:A5, and you put the number 1 in B1:B5.
>
> B6 contains the formula =SUMPRODUCT(A1:A5,B1:B5)
> B7 contains the amount of the payment
> B8 contains the formula =ABS(B6-B7)
> B9 contains the formula =SUM(B1:B5)
>
> Set the target as B8 (which is the difference between the amount paid
and the
> invoices marked as paid), and say that Solver should set it to the
minimum
> instead of to a particular value.
>
> The constraints are
>
> B1:B5 integer
> B1:B5 >= 0
> B1:B5 <= 1
> B9 >= 1
>
> I couldn't get B1:B5 binary to work reliably, so I had to use the 3
> constraints for the 1's and 0's. B9 >= 1 just means that at least one
invoice
> must be marked as paid.
>
>
Bernie Deitrick
Marc,
I was able to introduce a tolerance by using the formula (from Myrna's
example)
=((ABS(B6)-5)<0)*1
in a cell and constraining solver to solve for that cell and find a value of
1 for it.
HTH,
Bernie
m_r_be...@my-deja.com wrote:
Myrna Larson
This is a new target cell, and you want to set it to a Value Of 1. You still
need the other constraints, that B1:B5 are binary (or integer and 0 or 1),
that the number of 1's in the range B1:B5 must be >= 1. I don't remember now
if there was a constraint for B6 (the SUMPRODUCT cell) or not.
>Your formula is brilliant, but is it a target cell or a constraint
>cell? It your formula is in a cell that will have a value of 1 and you
>make it the target cell, what do you keep as constraints B6, B7, both?
>
>Marc
>
>======================================================================
>In article <3A1022DC...@consumer.org>,
m_r_be...@my-deja.com
Marc
Tushar Mehta
One of the less obvious, and consequently, unexploited capabilities of
Solver is that it does *not* need an objective (target cell)! In cases
where one wants to get a solution that is "in the ball park," so to say,
use of Solver with just constraints and no objective might do the job.
To not specify a target cell, do just that. Leave the target cell box
empty.
I reviewed the complete thread courtesy deja.com before this post and I
believe the 'no target cell' method might apply here.
For example, starting with the original problem posted on 11/9, consider
the foll.:
B C D
2 -68,062.50 0
3 4,125.00 0
4 -5,221.17 1
5 -336.85 1
6 5,558.02 1 0
7 3
8
9
10
11 <-- missing objective
12 5
13 TRUE
14 TRUE
15 TRUE
16 TRUE
100
where the Solver model is saved in B11:B17. The formulas are:
-68062.5 0
4125 0
-5221.17 1
-336.85 1
5558.02 1 =SUMPRODUCT(B2:B6,C2:C6)
=SUM(C2:C6)
<-- missing objective
=COUNT($C$2:$C$6)
=$C$7>=1
=$D$6<=5
=($C$2:$C$6=0)+($C$2:$C$6=1)=1
=$D$6>=0
={100,100,0.000001,0.05,TRUE,FALSE,FALSE,1,1,1,0.0001,TRUE}
The constraints are explained below.
=$C$7>=1 -- pick at least one
=$D$6<=5 -- goal is to be within 5 of the true objective of 0
=($C$2:$C$6=0)+($C$2:$C$6=1)=1 -- this specifies binary values
=$D$6>=0 -- goal must be positive
This method allows one to specify that the model is a linear model. For
complex problems, use of a linear model can lead to substantial time
savings.
--
Regards,
Tushar Mehta
www.tushar-mehta.com
--
In <8un7h5$irt$1...@nnrp1.deja.com>, m_r_be...@my-deja.com
<m_r_be...@my-deja.com> wrote
> OK, here is a challenge I believe:
>
> What if you want your cell B6 to be less than a certain amount instead
> of zero, say less than 5?
>
> You see, zero works very well for perfect cancellations of amounts in
> the column, but I need solver to work for cancellations whose result is
> in absolute terms less than 5 (the differences equal to less than 5
> dollars in our case can be written off). The "<" cannot be used in the
> Solver box, and we can't put an expression in.
>
> So Mirna, are you up to it?
>
> Don't stay up all night over this. Think about your Honey.
>
> Marc
>
>
>
>
>
>
>
> In article <tkio0tsm74g9vfesu...@4ax.com>,
> Myrna Larson <myrna...@home.com> wrote:
> > On Fri, 10 Nov 2000 16:19:53 GMT, m_r_be...@my-deja.com wrote:
> >
> > Yes, but someone else -- I think is was Dana DeLouis, (but if not,
> apologies
> > to the author) -- just in the past few days showed how to solve your
> > fundamental problem using Solver.
> >
> > This was REALLY SLICK !!!! I'd always thought that Excel couldn't do
> it.
> >
> > Problem: You have 5 numbers in A1:A5. You want to find a combination
> of those
> > numbers whose sum is 0.
> >
> > In B1:B5, put the number 1.
> >
> > In B6, put the formula =SUMPRODUCT(A1:A5,B1:B5).
> >
> > In B7, put the formula =COUNTIF(B1:B5,1)
> >
> > Then use Solver.
> >
> > The Target cell is B6
> > The goal is that B6 has a value of 0
> > The cells to change are B1:B5
> > The constraints are:
> > B1:B5 must be "bin" (i.e. binary -- 1 or 0 -- which wasn't obvious
> to me)
> > B7 must be >= 1
> >
> > Without the formula in B7 and that last constraint, Solver obligingly
> sets B1
> > to B5 to all 0's <g>.
> >
> > (You could, of course, say the contraints for B1:B5 are that they
> must be
> > integers, must be >= 0 and must be <= 1, but that's 3 constraints
> instead of
> > 1.)
> >
> > >I don't believe it!
> > >
> > >Last night, I was working on how to -- list -- the combinations from
> the
> > >choosing of n picks out of k subjects. Of course, given the
> > >complexities that come about as k and n grow, things just have to
> > >give way to some serious math...So I limited myself with just 4
> > >rows (as in my example) and used simple "If" statements in my code.
> > >
> > >You bet I was wondering how I could get started with VBA code with
> > >regards to this combination business. And here comes Mirna!
> > >
> > >Mirna, Mirna, Mirna...you did it again.
> > >
> > >Thanks Tom.
> >
> >
>
>
Dana DeLouis
Solver will usually not work with a function such as "ABS."
The reason is because ABS is what is considered a "non-smooth function."
Solver absolutely hates these types of functions!
Even when you are using Binary constraints, I believe that Solver first
attempts to solve the problem as if this constraint was not there.
When changing cells are "changing," Solver keeps track of the affect it has
on the Target cell.
In a cell that uses ABS, when Solver expects the value to go negative, the
result suddenly jumps to a Positive value.
Solver was not expecting this sudden reversal in direction. Solver hates
this, and will usually Quit the first time you trick it with this function.
It won't even give it a second chance. It will quickly just give up!
A short list of functions that do not work in Solver are:
IF, CHOOSE, LOOKUP, HLOOKUP, VLOOKUP, COUNT
ABS, MIN, MAX, INT, ROUND, CEILING, FLOOR, AND, OR, NOT.
What usually works for what I believe you are doing is to Minimize your
target Cell with the constraint that your target cell is Greater or Equal to
some small negative number that is within your acceptable limits of
accuracy.
It is usually better to use BIN then the other method of 3 constraints for
the same result.
Also, when working with a Binary model, don't forget to set the option for
"Assume Linear Model."
This will greatly increase the speed of finding a solution.
Also, you mentioned something like." ...a formula,
or an array formula might just do the job as well."
My opinion is that there are too many combinations for 1 formula to figure
out.
When you have dozens of cells, the number of possible combinations becomes
too great.
One of the benefits of using a program like solver is that the logic inside
the program can systematically make better and better guesses at a solution
during each loop. During each loop, the program can also more quickly
eliminate infeasible solutions .
Therefore, the number of combinations that Solver looks at are much less.
Anyway, good luck. Dana DeLouis.
"Bernie Deitrick" <dei...@consumer.org> wrote in message
news:3A1022DC...@consumer.org...
> > In article <qv9u0top8c5cs4ml1...@4ax.com>,
> > Myrna Larson <myrna...@home.com> wrote:
> > >
> > > On Sun, 12 Nov 2000 23:02:00 GMT, m_r_be...@my-deja.com wrote:
> > >
> > > >What if you want your cell [B8] to be less than a certain amount
> > instead
> > > >of zero, say less than 5?
> > >
> > > This sometimes takes a bit of juggling and ingenuity, and some
> > additional
> > > formulas (like what I show for B8 and B9, below).
> > >
> > > Let's say we are dealing with a list of outstanding invoices, and the
> > customer
> > > sends a partial payment, but he doesn't indicate which invoices it is
> > to be
> > > applied to. You want to select the best group of invoices to mark them
> > paid.
> > >
> > > You have invoice amounts in A1:A5, and you put the number 1 in B1:B5.
> > >
> > > B6 contains the formula =SUMPRODUCT(A1:A5,B1:B5)
> > > B7 contains the amount of the payment
> > > B8 contains the formula =ABS(B6-B7)
> > > B9 contains the formula =SUM(B1:B5)
> > >
> > > Set the target as B8 (which is the difference between the amount paid
> > and the
> > > invoices marked as paid), and say that Solver should set it to the
> > minimum
> > > instead of to a particular value.
> > >
> > > The constraints are
> > >
> > > B1:B5 integer
> > > B1:B5 >= 0
> > > B1:B5 <= 1
> > > B9 >= 1
> > >
> > > I couldn't get B1:B5 binary to work reliably, so I had to use the 3
> > > constraints for the 1's and 0's. B9 >= 1 just means that at least one
> > invoice
> > > must be marked as paid.
> > >
> > >
> >
Dana DeLouis
Hello. Just to share some thoughts on Solver.
I am assuming you are using Myrna's excellent example from a previous post
that makes sure that all the Binary values are not all zero's.
i.e. "... In B7, put the formula =COUNTIF(B1:B5,1)"
The intent here is to make sure that at least one of the Binary values is
set to 1.
However, I believe that this particular use of Countif will not work with
Solver.
The reason is, although you are using Binary Values, the real values are not
really 0, or 1.
They are close, but not exact.
There are options for Precision & Tolerance that you can increase, but they
are sometimes never "Exact."
Solver may be trying to use 0.99999 in Cells B1:B3, but Countif does not see
these as 1.
The Countif will show 0 in B7. However, Solver thinks there are 3 Cells
that are within the Tolerance of being 1.
Therefore, this really mess up Solver. It will usually quit at the first
sign of confusion.
I think you will find that in B7 you should use:
Sum(B1:B5)
with the constraint that B7 >= something like 0.999
Also, using "ValueOf=" usually does not work on these types of problems due
to Precision when working with a non-smooth functions.
Just some thoughts. Dana DeLouis.
<m_r_be...@my-deja.com> wrote in message
news:8un8og$jps$1...@nnrp1.deja.com...
> The code for the small probem is this:
> ====================================================================
> Sub Good_For_0()
>
> SolverOk SetCell:="$B$6", MaxMinVal:=3, _
> ValueOf:="0", ByChange:="$B$1:$B$5"
> SolverAdd CellRef:="$B$1:$B$5", Relation:=5, FormulaText:="binary"
> SolverAdd CellRef:="$B$7", Relation:=3, FormulaText:="1"
> SolverSolve (True)
>
> End Sub
> =====================================================================
>
> The argument "ValueOf:=" is pretty much fixed to accept values only,
> isn't it? I tried a "<5" but the answer was all wacked up. I wonder if
> assigning a variable to the argument would do the trick or if do to the
> math behind the scenes being specific to a value goal no expression can
> be set. I wish I were Mr. Pascal, but...
>
> Marc R. Bertrand
>
>
>
>
>
> In article <8un7h5$irt$1...@nnrp1.deja.com>,
> m_r_be...@my-deja.com wrote:
> > OK, here is a challenge I believe:
> >
> > What if you want your cell B6 to be less than a certain amount instead
> > of zero, say less than 5?
> >
> > You see, zero works very well for perfect cancellations of amounts in
> > the column, but I need solver to work for cancellations whose result
> is
> > in absolute terms less than 5 (the differences equal to less than 5
> > dollars in our case can be written off). The "<" cannot be used in the
> > Solver box, and we can't put an expression in.
> >
> > So Mirna, are you up to it?
> >
> > Don't stay up all night over this. Think about your Honey.
> >
> > Marc
> >
> > In article <tkio0tsm74g9vfesu...@4ax.com>,
> > Myrna Larson <myrna...@home.com> wrote:
> > > On Fri, 10 Nov 2000 16:19:53 GMT, m_r_be...@my-deja.com wrote:
> > >
> > > Yes, but someone else -- I think is was Dana DeLouis, (but if not,
> > apologies
> > > to the author) -- just in the past few days showed how to solve your
> > > fundamental problem using Solver.
> > >
> > > This was REALLY SLICK !!!! I'd always thought that Excel couldn't do
> > it.
> > >
> > > Problem: You have 5 numbers in A1:A5. You want to find a combination
> > of those
> > > numbers whose sum is 0.
> > >
> > > In B1:B5, put the number 1.
> > >
> > > In B6, put the formula =SUMPRODUCT(A1:A5,B1:B5).
> > >
> > > In B7, put the formula =COUNTIF(B1:B5,1)
> > >
> > > Then use Solver.
> > >
> > > The Target cell is B6
> > > The goal is that B6 has a value of 0
> > > The cells to change are B1:B5
> > > The constraints are:
> > > B1:B5 must be "bin" (i.e. binary -- 1 or 0 -- which wasn't
> obvious
> > to me)
> > > B7 must be >= 1
> > >
> > > Without the formula in B7 and that last constraint, Solver
> obligingly
> > sets B1
> > > to B5 to all 0's <g>.
> > >
> > > (You could, of course, say the contraints for B1:B5 are that they
> > must be
> > > integers, must be >= 0 and must be <= 1, but that's 3 constraints
> > instead of
> > > 1.)
> > >
> > > >I don't believe it!
> > > >
> > > >Last night, I was working on how to -- list -- the combinations
> from
> > the
> > > >choosing of n picks out of k subjects. Of course, given the
> > > >complexities that come about as k and n grow, things just have to
> > > >give way to some serious math...So I limited myself with just 4
> > > >rows (as in my example) and used simple "If" statements in my code.
> > > >
> > > >You bet I was wondering how I could get started with VBA code with
> > > >regards to this combination business. And here comes Mirna!
> > > >
> > > >Mirna, Mirna, Mirna...you did it again.
> > > >
> > > >Thanks Tom.
> > >
> > >
> >
Myrna Larson
wrote:
Dana:
Glad to see we have a Solver expert among us! Thanks for your input on this,
especially re the functions to avoid, like ABS and the lookups, etc.
>Even when you are using Binary constraints, I believe that Solver first
>attempts to solve the problem as if this constraint was not there.
That was my experience when using it on one of my own problems. I used BIN,
and Solver couldn't solve it. When I told it to keep the current solution, all
of changing cells that were supposed to be 1/0 contained floats!
>Also, when working with a Binary model, don't forget to set the option for
>"Assume Linear Model."
>This will greatly increase the speed of finding a solution.
When I did this (with a set of data that couldn't be solved) I got an error at
the outset -- something about the data or constraints not fitting that
assumption??
Can you explain what that message means?
Dana DeLouis
Hello Myrna. How are you?
I will guess that the message you had was
"The conditions for Assume Linear Model are not satisfied"
It is hard to say without knowing what you had.
I think the most common problem when using an Integer model in Solver are
unknowingly using equations that are not Linear. For example:
1/C1, LOG(C1), C1^2 or C1*C2 where both C1 and C2 are decision
variables.
Basically, anything that is not "Linear."
Chances are there is a cell somewhere with one of these equations.
Solver will find it quickly, then give up....:>)
Another problem is "Scaling." That is, the difference between the Largest
Values, and the Smallest.
It is possible to get this message when Scaling is a problem.
An example would be "How many multi million dollar airplanes to buy subject
to the constrain we do not buy any more than 10 airplanes of any one type,
and that the total Dollars spent is less than $9,000,000,000.
.. It is the difference between the numbers 1-10, and 9,000,000,000.
Excel will usually work ok when the difference is about 6 significant
digits.
Anything more, you should select the option "Use automatic Scaling."
This is more math work for Solver to do, but it usually will help.
You mentioned that the problem could not be solved before this option was
turned on.
This probably had some affect.
When you have any number of Binary variable, being able to use "Assume
Linear Model" greatly increases the speed.
Anyway, these are just some guesses.
This is a favorite subject of mine. I would be glad to take a look at your
sheet if you would like.
Dana
"Myrna Larson" <myrna...@home.com> wrote in message
news:bp5b1tg58b0rj6hok...@4ax.com...
> On Thu, 16 Nov 2000 00:42:48 -0500, "Dana DeLouis" <da...@email.msn.com>
> wrote:
>
> Dana:
>
<snip>
> >Also, when working with a Binary model, don't forget to set the option
for
> >"Assume Linear Model."
> >This will greatly increase the speed of finding a solution.
>
> When I did this (with a set of data that couldn't be solved) I got an
error at
> the outset -- something about the data or constraints not fitting that
> assumption??
>
> Can you explain what that message means?
>
<snip>
Tushar Mehta
I don't know how much you know about the subject, so my apology if I am
duplicating information you already know.
Adding to Dana DeLouis's explanation:
An additional consequence of developing a linear model is that the
solution is guaranteed to be the 'best,' in the sense that no other
combination of decision variables (changing cells) will yield a better
objective function value (target cell value). It may turn out that
different combinations of decision variables might yield the same
result, but none will be better than what Solver finds.
On the flip side, solving a non-linear model is *not* guaranteed to find
the best solution. It is possible for Solver, or any other method for
that matter, to get stuck at a 'local' optimum. For example, consider a
non-linear function y=f(x) that has a true maximum of y=10 at x=0, then
drops (smoothly) to y=0 at x=5, climbs to (y=8 at x=10), drops to
(y=0,x=15), climbs to (y=9,x=20) and drops off towards minus infinity.
A non-linear model starting with x=40 may not find the true global
maximum -- it might get stuck at the 'local' maximum of y=9,x=20.
Trying with another starting value of x, say x=-10 might yield a
different result!
Also, Dana did not include x/y < k (where x and y are decision variables
and k is some constant) in the 'non linear constraint' list. That is
for a very good reason. As long as x and y are positive numbers, one
can rewrite the constraint as x - k*y < 0. There are a whole bunch of
other rules (tricks?) on how to convert certain non-linear constraints
into linear ones, but, off the top of my head, I don't remember them.
One item that Dana did not mention is the IF statement. That, too, is a
non-linear constraint, and one of the more common causes why XL models
fail the linearity test!
Finally, I haven't had any significant problems with binary variables
(or, for that matter, integer variables). That might be because I try
and convert models I'm solving into linear models before I use Solver.
Another reason might be that for non-linear models, I try and shy away
from integer constraints -- after justifying why the error introduced by
the approximation is something I can live with :)
--
Regards,
Tushar Mehta
www.tushar-mehta.com
--
In <bp5b1tg58b0rj6hok...@4ax.com>, Myrna Larson
<myrna...@home.com> wrote
> On Thu, 16 Nov 2000 00:42:48 -0500, "Dana DeLouis" <da...@email.msn.com>
> wrote:
>
> Dana:
>
> Glad to see we have a Solver expert among us! Thanks for your input on this,
> especially re the functions to avoid, like ABS and the lookups, etc.
>
> >Even when you are using Binary constraints, I believe that Solver first
> >attempts to solve the problem as if this constraint was not there.
>
> That was my experience when using it on one of my own problems. I used BIN,
> and Solver couldn't solve it. When I told it to keep the current solution, all
> of changing cells that were supposed to be 1/0 contained floats!
>
> >Also, when working with a Binary model, don't forget to set the option for
> >"Assume Linear Model."
> >This will greatly increase the speed of finding a solution.
>
> When I did this (with a set of data that couldn't be solved) I got an error at
> the outset -- something about the data or constraints not fitting that
> assumption??
>
> Can you explain what that message means?
>
Myrna Larson
wrote:
I don't know much of anything about it, so thanks for the additional info.
Here's a question that, I'm sure, will betray my novice status re Solver: what
does the term "linear model" mean? What's the essence of a linear model vs a
non-linear model?
Tushar Mehta
email copy to Myrna Larson.
Please use the newsgroup for further discussion.]
Hi Myrna,
While the following is very applicable to the effective use of Solver,
it is drifting well away from direct XL-applicability ;-)
A linear model is one in which the objective and each of the constraints
is 'linear' in nature. A constraint is linear if it can be drawn as a
straight line in the 2-variable case, or as a surface in a 3-variable
case, or as a plane in the n-variable case. One example of a linear
constraint is 2x+3y+4z <= 100. An objective is linear if it produces a
straight line (in the 2-variable case) when used in an equation such as
<objective> = k, where k is a constant. So, an objective such as 10*x +
20*y would be linear since 10*x + 20*y = <some constant> is a straight
line.
Non linearity would be introduced by just about anything else. Examples
would be a function such as LOG or EXP, inverse (1/x), raising to a
power (x^3 or SQRT(x)), multiplying two variables (x*y), or use of an IF
clause.
So, a constraint (or an objective) such as LOG(x) < 10 is nonlinear.
Similarly, an objective (or constraint) of 10*x*y is non-linear. As
would be an objective of (x + y)^2
One way to test for non-linearity is with Solver. Set up the Solver
model, check the 'Assume linear model' checkbox, and ask Solver to
analyze the problem. If it detects any non-linear condition, it will
complain and stop.
Under some conditions, a seemingly non-linear constraint (or objective)
can be restated as a linear one, sometimes with the introduction of a
binary variable. For two variables, x and y, replacing x/y < k (where k
is a constant) with x - k*y < 0 with x,y > 0 is one example. Two more
complex examples are below.
First, the IF statement. Suppose that a firm has a choice of 2 plants
where it can produce a product. If it uses a particular plant to
produce any amount of the product, it incurs a fixed cost of say
$50,000. This has the nature of an IF statement of the type
[IF x>0 then K else 0], where K is a constant. One can replace the IF
with linear equations by introducing a binary variable, b, and a large
constant, say, M. Now, the IF statement becomes
K*b
x <= M*b
b = 0/1 (b is binary)
x >= 0
How does it work? If x is anything other than 0, the x <= M*b will be
satisfied only if b is 1. If b is 1, the K*b will evaluate to K! Also,
since M is a very large number, once b is 1, x <= M*b will always be
true no matter how large x becomes.
Second, the ABS statement. Assume that there are two variables in the
optimization, x and y. Let one of the constraints require that x and y
be within 10 of each other, or ABS(x-y) < 10. This is non-linear.
However, the ABS condition can be decomposed, with the addition of a
dummy variable, z=(x-y) into the IF statement [IF z >= 0 then z < 10
else -z < 10]. The linear equivalent to this requires two binary
variables b and c, and a repeat visit from our friend, M, the very large
constant. The foll. equations yield the same result as [ABS (x-y)<10]
z=x-y
z*b -z*c < 10
z <= M*b
-z <= M*c
b + c <= 1
b,c = 0/1
x,y >= 0
How does it work? Well, if x > y then z > 0. Then, z<=M*b is satisfied
only with b=1; which requires that c=0 to satisfy b+c=1, and that still
satisfies -z < M*c. Similar reasoning works for x < y. If z=0, either
b or c might be one, or both might be zero but we don't care.
--
Regards,
Tushar Mehta
www.tushar-mehta.com
--
In <lhlb1tkuuju6eclfu...@4ax.com>, Myrna Larson
<myrna...@home.com> wrote
> On Fri, 17 Nov 2000 18:19:54 -0500, Tushar Mehta <ng_p...@bigfoot.com>
> wrote:
>
> I don't know much of anything about it, so thanks for the additional info.
> Here's a question that, I'm sure, will betray my novice status re Solver: what
> does the term "linear model" mean? What's the essence of a linear model vs a
> non-linear model?
>
Myrna Larson
wrote:
THANKS VERY MUCH !!! for that info on Solver. Obviously you spent quite a bit
of time on it. I hope there are others that benefit from it, too.