Sum values in cells to meet a certain criteria

This seems really complex to me if not impossible, but I'm out of ideas...

Scenario : A client wants bourbon shipped to their warehouse in Chicago, directly from our production facility. From the Chicago warehouse they then send it out in various qty's to several different store locations. We can only fit a maximum qty of bourbon on one skid, that skid will contain product for several different store locations. We must make a sheet to put on the skid that lists what that skid contains (qty by store location).

Since there is maximum qty we can put on one skid, by trial and error we have to add up each each store location qty to meet that maximum number (doesn't have to be exact within a ballpark range). It's a tedious and annoying process and we're trying to streamline the process to make the task easier.

What I'm trying to make is something to make it somewhat automated so that the trial and error is eliminated. I have a spreadsheet made (excel file is attached) that is somewhat close, but it won't take into account other quantity's that could be used to get closer to the maximum qty on a skid.

It may require a ton of IF and SUMIF statements. That's all I know

Any help would be greatly appreciated.

im confused by your explanation. what is the max qty that can fit on one skid?

and one skid will service a number of different stores (ie A,B,C, etc?)

Copy this forumula into Cell E2 of your sample sheet
.
=IF(C2+E1<$G$1,C2+E1,C2)

Then fill the formula down from E2 to E26, tell me if this looks like what you want.

Originally Posted by ajm123456789

im confused by your explanation. what is the max qty that can fit on one skid?

and one skid will service a number of different stores (ie A,B,C, etc?)

In the excel example I attached, the max qty ("target qty" in the attachment) is 5900. This number will vary from time to time, that's why I have it listed in a standalone cell.

Yes, one skid could contain the qty's for stores A,B,C, etc. However, where the problem lies is if I want to add the store P qty with A,B,C,D -- I can't really do that because the formula is made to go in order of the values listed in the column; it has no ability to pick and choose qty's that could work more efficiently.

Originally Posted by MarkinTX

Copy this forumula into Cell E2 of your sample sheet
.
=IF(C2+E1<$G$1,C2+E1,C2)

Then fill the formula down from E2 to E26, tell me if this looks like what you want.

Yes, that worked quite well. Progress has been made, but I still need some fine tuning. Thank you!

I feel like that's a possibility. I've been tinkering around with the idea, and I have a feeling that it's going to boil down to a ginormous IF statement string.

Is the only goal the smallest number of skids?
Or does the store order need to be considered as well.

http://www.tushar-mehta.com/excel/te...ues/index.html
this guy has an explanation of how to do something like this
a big IF statement might not work because of too many nested statements

Yes, minimal skids would be best, as long as the qty on them isn't over the max/target qty.


Store order should be considered, but I'm interpreting that question this way ---

Example: Max/target qty is 5900. A skid containing qty's for stores A, B, and C equals 5100. I would like to fit D with it, but store D quantity is 1,000. That won't work, because the formula will see that if D is added with A, B, and C, it's over 5900 and will start a new calculation.

However, I want to be more efficient. I can fit say 700 more on that same skid. The spreadsheet says that store P = 300 and store Q = 400 --- it would be more efficient to put P and Q together with A, B, and C, because farther down it could eliminate a smaller skid that may have to be made.

Originally Posted by ajm123456789

http://www.tushar-mehta.com/excel/te...ues/index.html
this guy has an explanation of how to do something like this
a big IF statement might not work because of too many nested statements

I actually stumbled across that website yesterday, it may be exactly what I'm looking for.

I have set up this sheet to use Solver to solve for the number of optimal skids that contain quantities between the listed max and target diviance.
The solver setup creates the most skids to contain values between those numbers.

You may have to enable the solver add-in, once enabled you will find it on the far right under the data tab.
I have also attached a screen shot of all of the solver constraints.

Originally Posted by MarkinTX

I have set up this sheet to use Solver to solve for the number of optimal skids that contain quantities between the listed max and target diviance.
The solver setup creates the most skids to contain values between those numbers.

You may have to enable the solver add-in, once enabled you will find it on the far right under the data tab.
I have also attached a screen shot of all of the solver constraints.

WOW! I cannot thank you enough. That's exactly what I was looking for. Thanks again.

I know what you mean. It bothered me all last night and finally realized that I wanted to accomplish was completely beyond my excel realm. Thanks again!

Originally Posted by MarkinTX

I have set up this sheet to use Solver to solve for the number of optimal skids that contain quantities between the listed max and target diviance.
The solver setup creates the most skids to contain values between those numbers.

You may have to enable the solver add-in, once enabled you will find it on the far right under the data tab.
I have also attached a screen shot of all of the solver constraints.

That's right. I haven't forgotten about this spreadsheet you made for me. It's been working like a charm and saving loads of time on our end. However! We've recently updated to Office 2010 and I have been beating my head against the wall trying to get this to work. I'm no longer getting whole numbers on the skid results -- it comes out to 1.020111, 1.00002, etc.

I've taken screen shots of the solver dialog's from both office 2007 and 2010 to compare the two, and I can't find any changes. Formula's are still intact, too. I'm not sure what's going on.

Any ideas?