Formula to choose largest number first.....

[Jamie Ramos]

I am a beginner in trying to write a what I think is a complicated spreadsheet but may not really be. I have looked online and cannot find a direct reference to what I am looking to do. I want to build a quoting database in either Excel or Access and am trying to determine which software would work best. My main issue with the two is the same. The biggest hurdle is determining if one or either is capable of performing a calculation that would determine how many lengths there are in a run and choose the largest first and then calculate the next best size until there is not more than an 11” space left over (and cannot go over the run length). For example:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I have a 50’ run and lengths in increments below:<o></o>
<o> </o>
96”, 84”, 72”, 60”, 48”, 36”, 24”, 12”<o> </o>
<o> </o>
It should choose 6 of the 96” pieces and 1 24” piece in the example above.

Not all the sizes are as cut and dry as above but I just need a basic start.

Is this something a newbie could do?

 

[Stu Dapples]

I dont think it will be a quick and easy to be honest....

It is going to end up being either a convaluted formula or some type of table...

I guess if you get teh required run length, divide it by the largest piece possible then do the same for the remainder you may be getting close? I think this sort of thing will, at some stage, need a human decision as to the length of the final piece to use....

There may be someone on here who can write a spicy Macro for you but as I aid, I dont think it is a quickie...

 

[Jamie Ramos]

Thats what I was afraid of.

 

[kpark91]

This sounds fun.
Where are the list of the lengths found? in column A?

There won't be much problem to solve this. It's purely mathematical :D

 

[Ron Coderre]

Perhaps something like this:
With
A1 containing a length, in feet...e.g. 50
and
A2: =A1*12....(to convert the feet to inches)
and
D1:K1 containing this list

96     84     72     60     48     36     24     12

This regular formula begins the calculation process
D2: =INT($A2/D1)

And this formula, copied across through K2, completes the process

E2: =INT(($A2-SUMPRODUCT($D1:D1,$D2:D2))/E1)

In the above example, the formulas return these values:

 96     84     72     60     48     36     24     12
 6       0      0      0      0      0      1      0

Is that something you can work with?

 

[kpark91]

Perhaps something like this:
With
A1 containing a length, in feet...e.g. 50
and
A2: =A1*12....(to convert the feet to inches)
and
D1:K1 containing this list

96     84     72     60     48     36     24     12

This regular formula begins the calculation process
D2: =INT($A2/D1)

And this formula, copied across through K2, completes the process

E2: =INT(($A2-SUMPRODUCT($D1:D1,$D2:D2))/E1)

In the above example, the formulas return these values:

 96     84     72     60     48     36     24     12
 6       0      0      0      0      0      1      0

Is that something you can work with?

sigh... MS MVP's.... all beasts...

 

[Jamie Ramos]

First I just want to say Thank You for all the super quick responses!!!

Ron- I can try that. I can then link the quantity cells to pricing somewhere.

Kpark91- I have been trying for weeks to figure this out but my brain just isnt cooperating. If it sounds fun here is more info. I havent even really built it yet because I kept trying it different ways and couldnt make it work. Bascially it needs to calculate how many of each piece it needs, then give me pricing. There are lots of variables like adding mandatory accessories. There are also some tweaky ones where the lengths are not true.

Here is more info:

It is for lighting fixtures. There are different types of fixtures all with different lengths. All of the fixtures require either a ballast or a driver and feed cable. Some of the fixtures are staggered and are sized say 92, 68, 48. But only the first length is "true". The other lengths are then 4 or 6 inches shorter because of the way they layout. I could manually shorten the lengths but want anyone to be able to just add the required run and have the sheet calculate out the whole quote for them.

yeeessshhh...I'm tired just thinking about it. Again I have started and restarted this thing so many times out of frustration but I think Excel can do it.

 

[kpark91]

Well I haven't heard back soo... there are two steps in my way of approaching this problem:

A3: Your part that needs to be evaluated (in inches)
from B1:I1 (there is your list of part lengths)

B2 Formula: =INT(A$3/B$1)
B3 Formula: =A$3-B$1*B$2

then just copy both B2 and B3 formulas to all the way to the right (column I).



The advantae of this layout is that you could view the remainder of the length left after evaluation and you could add more parts in row 1 any time!

 

[Jamie Ramos]

Ron- The formula worked great! Is there a way I could have it know that it could leave up to 11" open. In other words if it gets down to under 12" and there is no choice on size thats OK.

Kpark91- Thanks for the help!

Im going to try working on getting it to now calculate price and see if I cant figure out how to calculate the staggered pieces.

Thank you so much!!!

 

[Ron Coderre]

What do you want to happen to the under-12" remainder?
...add another 12" to cover the overage?

If that's ok...this formuls rounds up to the next increment of 12"
K2: =CEILING(($A2-SUMPRODUCT($D1:J1,$D2:J2))/K1,1)