Formula to tell me when I only need to order one part to build a product

mjones

Board Regular
Joined
Oct 27, 2007
Messages
94
Office Version
  1. 365
Platform
  1. Windows
Hi All,

Some sample data:

Can Make Qty >{=IF(MAX(C$4:C$10)=0,"Unltd",INT(MIN(IFERROR($B$4:$B$10/C$4:C$10,1E+300))))}{=IF(MAX(D$4:D$10)=0,"Unltd",INT(MIN(IFERROR($B$4:$B$10/D$4:D$10,1E+300))))}{=IF(MAX(E$4:E$10)=0,"Unltd",INT(MIN(IFERROR($B$4:$B$10/E$4:E$10,1E+300))))}
Products >ABC
PartsIn Stock
1155
22241111
3-442
48533
51210
6-11
7171010

<tbody>
</tbody>

First time I tried to all a table so kindly forgive me if it doesn't work.

The columns for products A, B and C have quantities in the parts rows of how many of each part is needed to build the product.

What I hope you can help me with, is indicate (any way possible), that I should give Product B my attention. This is because I only need to order one part (Part 3) to build it. I don't want to know about Product C because I need to order two parts to build it (3 and 6).

In short, indicate the product columns that are short only one part (rows). It would be handy if one was in a cell so I could change it to a two when I'm looking for products needing two parts ordered.

The formula in the top row will gave quantity 1 above product A. It tells me there's enough stock to build one of product A, but product B in the next columns has enough parts in stock to build negative one products and I should order some parts. Product C can build negative two products.

I sure hope this explanation is clear. This is a very complicated spreadsheet and I left a lot out and I'm hoping it's enough.

Thanks a bunch!

Michele
 
Last edited:

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Hi mjones

I inserted a row above "Can make Qty >" and called it "Part types needed". If your "In stock" data is in B5:B11 (so 15,22,-4,8,12,-1,17), your A B and C requirement data is in C5:E11 and your "Can make Qty >" is in C2:E2, use the following formula in C1 and drag it across to E1 so it covers A, B and C:

=SUM(--(IF(C$5:C$11="",0,C$5:C$11>($B5:$B11-(MAX(0,C$2)*C$5:C$11)))))

This should work out how many parts are needed to complete one additional product. I'm not sure about the negative products so you may need to remove the MAX() part of the formula, but hopefully you can see what it's doing - saying "if I take off the stock used from each part type multiplied by the number of products we can make, how many of the parts required are larger than the parts remaining".

To return the product name you can then use

=INDEX(C3:E3, MATCH(YourDesiredValue, C1:E1, 0))

In this case YourDesiredValue could be a cell containing 1, to return the product that needs only 1 more part type. You can also use MIN(C1:E1) in place to find the product needing the fewest different types of parts.

This does not count the number of parts required, only the different types required. Let me know if that's helpful or if it needs some adjustment.

Mackers
 
Last edited:
Upvote 0
Hi Mackers,

Very helpful! Will save lots of time because the spreadsheet is large.

However, I'm not sure we're doing it right. I should clarify about something you said here:

saying "if I take off the stock used from each part type multiplied by the number of products we can make, how many of the parts required are larger than the parts remaining".
Mackers

The column with the quantity of stock (in stock) has already deduced stock for items made; where items made is another row. The 'can make' quantity is telling us, given the stock available, how many products can we make. Does this mean your formula should be adjusted? Because the spreadsheet is so large, it's hard to tell if the returned values, which look reasonable, are correct.

I haven't got to the part about returning the product name. I'll try that next.

Thank you very much for your time.

Michele
 
Upvote 0
I've confirmed this is a problem. We can't use the 'can make' top row in the calculation as the formula you provided does.
The column with the quantity of stock (in stock) has already deduced stock for items made; where items made is another row. The 'can make' quantity is telling us, given the stock available, how many products can we make.
Michele

I can tell the formula doesn't work because a few product say I have enough stock to 'can make' quantity one, yet your formula says I need quantity one part to make it.

The formula to return the product name is not necessary because the names are in the row below. The formula for the product needing the least parts will always be zero since there are many products with enough stock to be made.

My biggest question to answer is which parts should I order; which are the optimal ones that will enable me to make the most products. Soon I'll have almost 1000 parts and 300 products so some way of extracting this information becomes a real time saver. Once we fix the first formula in discussion, if anyone can help me with formulas for any of these ways to interpret the data, it would be super appreciated:

1 - Which parts are short of stock and, when received, will make the most products? This is a hard one because the product might need other parts, but it's certainly a start because I can look at those products & parts in combination. I think the formula will be a new column beside the Parts column (i.e. column 2 below). The cell formula would go something like this - find the product columns that need me (the part), of those, find ones that 'can make quantity' (row one in my example below) is zero, and return the quantity of those, i.e. parts that need me with can make zero. It might say three products need me to make another one of them.

2 - Another row similar and beside the above one could be more specific. It could be the same, but then add to only count products needing one part to make it. This number is the one we're originally working on to be correct (see top). We are adding this to a new row 1. This cell would tell me, for example, if I order more of this part, 3 product types can be made because they only need this part.

I know this sounds really complication and honestly don't expect much of a response. Crossing my fingers. Right now it's very slow going and I'm sure I'm missing important information.

All the best to everyone in this great forum!

Michele
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top