Calculate quantity for given weight factor based on two known criteria

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

Re: Calculate quantity for given weight factor based on two known criteria

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.

Re: Calculate quantity for given weight factor based on two known criteria

It has been a long time, so not familiar and yes z = 0 thanks.

Re: Calculate quantity for given weight factor based on two known criteria

What if there is more than one solution?

Re: Calculate quantity for given weight factor based on two known criteria

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

Re: Calculate quantity for given weight factor based on two known criteria

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.

Re: Calculate quantity for given weight factor based on two known criteria

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

Re: Calculate quantity for given weight factor based on two known criteria

Quote:

Originally Posted by

**dragonat2000** 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?

Not sure what you're asking; line 120 is a loop (as is line 110).

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.

Re: Calculate quantity for given weight factor based on two known criteria

Quote:

Originally Posted by

**dragonat2000** 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

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!!

Re: Calculate quantity for given weight factor based on two known criteria

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?

Re: Calculate quantity for given weight factor based on two known criteria

Quote:

Originally Posted by

**dragonat2000** 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.

Re: Calculate quantity for given weight factor based on two known criteria

Quote:

Originally Posted by

**dragonat2000** 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.

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...

Re: Calculate quantity for given weight factor based on two known criteria

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...