It has been awhile since I have been in math class. I have built a program that does works, but it uses for loops and can take forever to come up with the answer. I am hoping there is a mathematical way to solve the following problem.
I am given Total pieces and Total Postage
(x + y + z) = Total pieces
x, y, z are also integers
(.34x +.465y + .44z) = Total postage
For ex.
Total pieces = 331
Total postage = 112.665
The answer is x = 330 y = 1 and x = 0
This is a system of equations in three variables so using the combination method can be useful. Are you familiar with that?
And I guess you made a typo in your solutions and that: z=0.
You have 2 equations but 3 unknowns, so cannot be solved directly.
However, a FOR loop should take care of this in a split second!
To start, only 2 variables need to be looped: the 3rd is by default.
I'll give you a simpler example (yours will work the same):
number of pieces = a + b + c = j
total cost = ua + vb + wc = k
Example; givens are:
u = 3, v = 7, w = 9, j = 12, k = 74
Program:
100 u = 3: v = 7: w = 9: j = 12: k = 74
110 FOR a = 0 TO j
120 FOR b = 0 TO j-a
130 c = j - a - b
140 IF u*a + v*b + w*c <> k THEN GOTO 160
150 PRINT u,a,u*a,v,b,v*b,w,c,w*c
160 NEXT b
170 NEXT a
This one has 3 solutions:
3*3 + 8*7 + 1*9
4*3 + 5*7 + 3*9
5*3 + 2*7 + 5*9
Wilmer,
Thanks for your help.
I created a nested loop to the other day. I actually have 5 unkowns. The problem is when I get over 100 pieces, the computer takes forever. I am looking at your sample and I I might be able to remove one of the for loops to speed it up. Thanks again for your help.
Wilmer,
why do you have
110 FOR a = 0 TO j
120 FOR b = 0 TO j-a
130 c = j - a - b
Does line 120 and 130 speed the loop up?
Do you mind explaining?
The following is what I have.
Sub Allcombined()
'piece that are calculated
Dim Pieces1_oz As Long '1oz mail
Dim Pieces2_oz As Long '2oz mail
Dim PiecesFull As Long 'Full mail
Dim PiecesForeign As Long 'Canada Mail
Dim PiecesCanada As Long 'Canada Mail
Dim X As Double
Sheets("Postage").Select
'Range("A1").Select
X = ActiveCell.Row
For PiecesForeign = 0 To TotalPieces
For PiecesCanada = 0 To TotalPieces
For PiecesFull = 0 To TotalPieces
For Pieces2_oz = 0 To TotalPieces
For Pieces1_oz = 0 To TotalPieces
If (Pieces1_oz + Pieces2_oz + PiecesFull + PiecesForeign + PiecesCanada) = TotalPieces And _
((Pieces1_oz * PostageCost1oz) + (Pieces2_oz * PostageCost2oz) + (PiecesFull * PostageCostFull) + (PiecesForeign * PostageCostForeign) + (PiecesCanada * PostageCostCanada)) = TotalPostage Then
'ActiveCell.Offset(X, 0).Value = X
Range("S" & X).Value = Pieces1_oz
Range("T" & X).Value = Pieces2_oz
Range("U" & X).Value = PiecesFull
Range("V" & X).Value = PiecesForeign
Range("W" & X).Value = PiecesCanada
X = X + 1
Else
End If
Next Pieces1_oz
Next Pieces2_oz
Next PiecesFull
Next PiecesCanada
Next PiecesForeign
End Sub
Not sure what you're asking; line 120 is a loop (as is line 110).Originally Posted by dragonat2000
Line 130 eliminates a 3rd loop, since c can be calculated.
As example, if total pieces which are represented by variable j are 100,
and a = 25 and b = 35, then c = 100 - 25 - 35 = 40
In line 120, do you understand why the loop is 0 to j-a?
Is your program in some version of Basic?
I use UBasic.
YIKES!!
Why don't you save yourself LOTS of typing by making
those 5 pieces = a,b,c,d,e and total pieces = t ?
Then you'd have:
For a = 0 to t
For b = 0 to t
For c = 0 to t
For d = 0 to t
For e = 0 to t
And your lines like:
If (Pieces1_oz + Pieces2_oz + PiecesFull + PiecesForeign + PiecesCanada) = TotalPieces
would be simplified to: if a + b + c + d + e = t
Not picking...just trying to simplify your life!!
You are correct, there is allot of typing, It is just a best practice for programming. I am programming in vba visual basics for applications. Microsoft’s version. I am able to have excel talk to any of the Microsoft product and an outside database to make things faster and simpler for the department.
what I don't understand is
In line 120 FOR b = 0 TO j-a
What is the benefit for minusing a from j?
I guess you could call that "loop control"...
Say your total pieces are 10, and you have 3 different items; a,b and c.
Say a is at 6; then b + c cannot exceed 4, right?
Or b can be maximum 4 (leaving c at 0), right?
Programming it as in your program:
for a = 0 to 10
for b = 0 to 10
for c = 0 to 10
you are then doing unnecessary calculations when a+b+c>10.
But this way:
for a = 0 to 10
for b = 0 to 10-a
c = 10-a-b
a+b+c always equal 10
For that example, your program does 11^3 = 1331 calculations.
Doing it the way I show does only 66 calculations.
With pieces = 200 and 5 items, your program does 201^5 = 328 080 401 001 calculations.
The way I show does 29 984 301 calculations.
Prove it to yourself: run your program both ways.
Well, to each his own. I'd be making typoes (poor typer!) like pieces/peices...
I personally would set up the "full wording" as REM's, a bit like this:
For a = 0 to t REM For PiecesForeign = 0 To TotalPieces
For b = 0 to t REM For PiecesCanada = 0 To TotalPieces
Should keep the boss happy!
BUT all that's none of my business...
Played with your problem a bit (I find it interesting!) and found a way to
elimimate 4th loop: so 3 loops to find 5 items; reduces time to 3 seconds!
We have:
j = total items (given)
k = total cost (given)
u,v,w,x,y = cost of each item (given)
a,b,c,d,e = number of each item (to be calculated)
So we have these 2 equations:
a + b + c + d + e = j
au + bv + cw + dx + ey = k
These can be combined and simplified to give:
d = [k - jy - a(u - y) - b(v - y) - c(w - y)] / (x - y)
and e = j - a - b - c - d
Makes for quite a simple looper program:
100 for a = 0 to j
110 for b = 0 to j-a
120 if a*u + b*v > k then goto 210
130 for c = 0 to j-a-b
140 if a*u + b*v + c*w > k then goto 200
150 d = [k - jy - a(u - y) - b(v - y) - c(w - y)] / (x - y)
160 e = j - a - b - c - d
170 if d<0 or e<0 then goto 190
180 print a,b,c,d,e
190 next c
200 next b
210 next a
Lines 120 and 140 are important, as they both cancel a loop.
Tested it a few times: 2 to 4 seconds only, for j=200.
If you have questions, don't be shy...
  |
LinkBack URL
About LinkBacks