Hi all,
I have a small table that I need help with.
Basically I need to organise some shipments and in order to complete a shipment I need to add several different parts
I have included an example of my table
Each complete kit contains devices 1 to 10 (qty indicated in cells D2:D11)
I would like cell B14 to calculate how many complete kits based on cells C2:C11 are ready to ship - currently 0 but as Units available increases so should kits available
help!!
thanks in advance
Joe
Formula for B14
Formula:
Please Login or Register to view this content.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
Sorry, my wording may not have been accurate
So, each device has a minimum qty per kit (cells D2:D11) and a complete kit is not ready until Column E equals column D
B14 will then increase as stock levels increase but can only increase by 1 each time the minimum qty required doubles for EVERY device
example - device 1 requires 3 units per kit - if I have 3 I have enough for 1 kit - if I have 4 still only 1 kit - if I have 6 then B14 could increase to 2 - BUT.... this has to calculate ALL devices not just one - B14 can opnly increase once Each device has enough to satisfy two kits
It seems to me that the simplest way to do this is to add a helper column which could be hidden for aesthetic purposes. The helper column will yield the number of kits that could be made from the number of units available using the ROUNDDOWN function.
On the file you attached to post #1, paste the following into cell G2: =ROUNDDOWN(C2/D2,0) then double click the fill handle to copy down to cell G11.
The MIN function then populates cell B14, as you can only make as many complete kits as the smallest number of units that meet the quantity of units per kit.
Using that line of thinking paste the following into cell B14: =MIN(G2:G11)
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks