Function to work out area of items per roll

**joesinla** · 07-11-2010, 11:44 PM

I have a window tinting business where i use rolls in 36-48-60-72 inch roll sizes. I am having trouble with a function that returns proper roll sizes. The way it works is:

If I have a window that is 23X49 I would pick a 48" roll, because 23 rounds to 24 which is half of 48.

If a window is 60x45 it returns 60.
If 2 windows are lets say 14x28, then a roll size of 48 will be needed.
and so on...

Im having trouble with the equation though, can someone please try to help me. It doesn't work

**Jamesr571** · 07-12-2010, 04:13 AM

Hello there,

can you give me the between sized for each size?

E.G: between what sizes (I am assuming it is based on width?) would you use 36", and same for all sizes

What you can use is a IF function. Here is a example so you can try for yourself

Say 36 = 1-6
Say 48 = 7-12
Say 60 = 13-18
and say 72 = 19+
Here is the formula I would use

=IF(G8<=6,36,IF(AND(G8<=12,G8>6),48,IF(AND(G8<=18,G8>12),60,IF(G8>18,72,""))))

All you need to do is edit the digits in bold as they are constants I put in to show you the formula

**joesinla** · 07-12-2010, 11:14 AM

Hi there, thanks for the advice. That would be fine for choosing each width and height size. My formula now must pick which one will waste less.

For example,

if i have 2 piece of film that are 24x56 then i would pull 2 pieces from half of the 48" roll, this would not waste any film. Whereas if i pull it from a 60" i will waste 4 inches. It would be easy except for I pull from half boxes sometimes. ive attaced a simple version of what im trying to do. Please help me fill in the "roll size" box with a working formula.

**Jamesr571** · 07-14-2010, 08:44 AM

Hello there,

In order to make sure I get this one right could you provide what size rolls there are, eg:

36x??
48x??
60x??
72x??

Reason for this is because depending on the size you could cut the film from either dimension in order to get the correct size