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?

 

[Jamie Ramos]

No it is an acceptable loss. We cannot go over the original run length under any circumstances.

The sheet is coming along nicely now. You all are great. I am so glad I found this site.

 

[Jamie Ramos]

I am very excited about how far Ive gotten today. I was able to use the formula in another area to get results I needed.

I am stuck once again on an if /and statement. There are 2 columns; one (D) has a cell (2) that has wet or dry inputted and the second column has dimmable (f) and yes or no as data (cell 2). I got it to work in the cells with only the wet or dry option. I am stuck because on one of the calculations if the answer is dry it multiplies by one price but if the answer is wet AND yes in dimmable the calculation is different. I have tried to build a formula for it and I think I am either using AND in the wrong way or too many parenthesis or commas but am not sure.

I will keep trying but if anyone has any input I would appreciate it. Here is what I have for the wet/dry so far in my other cells with the multipliers being different:

=IF(D2'"Dry", L5*85, IF(D2="wet", L5*125)) And it works great.

Thank you.

 

[kpark91]

You forgot an equal sign.

=IF(D2="Dry", L5*85, IF(D2="wet", L5*125))

and remember the comparison is case-sensitive.

 

[Jamie Ramos]

Oh sorry...I do have that in there...typo. I think it is the second half of the formula where I am going wrong. How do I get it to see that if D2 is wet and F2 is No that it muliplies by say 85 but if D2 is wet and F2 is yes it multiplies by say 150?

That is where I am stuck.

 

[kpark91]

Try

=IF(D2 = "wet", IF(F2 = "No", L5 * 85, IF(F2 = "Yes", L5 * 150)))

 

[Jamie Ramos]

It worked!!!!! Thank you , thank you, thank you!!!!
Oh my gosh I wish I found you guys 6 weeks ago!! Would have saved me alot of heartache and headaches!!

 

[Jamie Ramos]

Hello-

Its me again. Been doing really well and actually am learning alot about how formulas work thanks to your examples. I now have a new challenge that I have been stuck on.

It goes back to my original questions about determining the length of product to choose based on longest first. I have a new tweak that I cannot seem to work out. In this case an example is below.

I have 2 fixture lengths: 88 and 46. The products are staggered however and after the first fixture lose 4" for every fixture thereafter. So the math would look like this:

1 53 ft run = 636"
1 ea 88
600-88=548" remaining
6 ea x 84(88-4)=504"
1 ea x 42(46-4)=42"
So you really need (7) 88" pieces and (1) 46" piece and have 4" leftover which is fine.

Im stumped. I've tried it a hundred ways and not sure if it can be done...but if it can I know someone here knows how.

Since you guys are geniuses!!!!!!!!!



Thanks.

 

[Jamie Ramos]

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?

Sorry Ron. I just noticed that you answered this part of my questions. But I dont want to add another 12". If there is 12" or more remaining it must go back and recalculate choosing whichever sizes it must to get end result to have no more than 11" left over. In other words it needs to choose the best lengths possible to get it to no more than 11" left but at the same time start with the largest.

For example:

Run length in inches 1812
Lengths avail. 92,69,46,34,22
The current formula chooses 19 of 92(1748") and 1 of 46 (total 1794") with 18" leftover.
It should choose 17 of 92(1564"), 3 of 69(207") and 1 of 34(34") for a total of 1805" with only 7" leftover.

I didnt explain that properly when I first came looking for help. Sorry.

Can you help with this?

 

[Jamie Ramos]

I've tried to see if I can get help on this and another formula in a different thread but have received no replies after getting lots of help when I first posted.

I just want to know if I broke an etiquette rule about too many questions so I dont do it again when I have an issue.