Excel - Imposition Values Based on Length, Width and Paper Size

[mmiram]

Hi all,

Here is what I am trying to do.

We are a printing company. We print on different paper sizes. The sizes we print on are:

8.5 x 11
11 x 17
12 x 18
14.33 x 20.5

We offer different products ranging from business cards to posters.

What I am trying to do is create a calculator that takes the length and width as the input values and then calculates the paper size that we can impose the maximum number of cards of that length. If two sheet sizes can accommodate the same number of cards, then the sheet that has the least amount of wastage should be selected.

E.g. For, Lets say my input value for length is 8.5 and width is 11.

Out of the 4 paper types I listed above, 3 of them can be used to impose 8.5 x 11 two up (i.e. two 8.5 x 11 images can be placed on the same sheet). The sheets that can accommodate them are

11 x 17
12 x 18
14.33 x 20.5

However, 11 x 17 does not waste any space as 8.5 x 11 goes exactly two up on that sheet without wasting any extra space. So I need that paper type to be selected or shown in a cell.

Is this something I can use excel solver for or do I have to go the VBA route.

If I need to, can someone please point me in the right direction in order to come with something that gets the same result.

Thanks.

 

[SkipVought]

Hi,

You have four sheet dimensions and many product dimensions.

It would seem to me that for any product, you ought to be able to loop thru the sheet dimensions and calculate the waste for each.

have you designed such an approch?

Skip,
[sub]
[red][/red]
[/sub]

 

[mmiram]

well, I am not sure that I understand you exactly but our product sizes would vary based on client requirements...for e.g. I could have a client ordering 2.35 x 3.67 which is not a standard size. As such, I cannot have a list of products and the sheets that are optimal for those products. I would need to take the input values and use a formula to see how they can be imposed on each sheet and then figure out which one is best. I am not sure how that formula can be constructed though.

 

[SkipVought]

Of course, you'd have to figure it out. But you could do that quite easily on a spreadsheet.

The [Number of widths] you can get out of a [Sheet Width] is
[tt]
[Number of widths]: =INT([Sheet Width]/[Product Width])
[/tt]
and the same with the length.

Then the [Waste area] = [Sheet Area] - [Product Area] * [Number of widths] * [Number of length]

Should be pretty simple.


Skip,
[sub]
[red][/red]
[/sub]

 

[Bong]

As it's pretty simple arithmetic, I think you can do it either with VBA or with functions on the spreadsheet.

You have 2 input dimensions, say iwidth and ilength. Then, for each output type, you also have 2 dimensions, say owidth1, olength1, etc. Then, for each output you would see how many integer times iwidth goes into owidth. If it's greater than 0, how many times does ilength go into olength? If that's greater than 0, too, you've got a possible match. Now, you also need to run that again with iwidth into olength and ilength into owidth.

For each combination where the output works, compute the area of the output (owidth x olength) minus the area of the input times however many fit. The minimum of that computation is the size you want to use.

_________________
Bob Rashkin

 

[SkipVought]

[echo]

Skip,
[sub]
[red][/red]
[/sub]

 

[Bong]

Yeah. I got distracted while I was writing that and didn't check back to see that, indeed, I was redundant.

_________________
Bob Rashkin