Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested IF Limits
My spreadsheet has a calculation that compares material available (both in
inventory and ready to ship to a production facility) to material required for production. The ready to ship materials are packed in containers of varying quantites. What the following formula is supposed to do is count the number of containers needed for each material so that the total (in inventory and shipable) is equal to or greater than the quantity required for production. There are 60 different materials (rows). =IF(K37<0,IF(L37=K37*-1,COUNT(L37),IF((L37+M37)=K37*-1,COUNT(L37:M37),IF(SUM(L37:N37)=K37*-1,COUNT(L37:N37),IF(SUM(L37:O37)=K37*-1,COUNT(L37:O37),IF(SUM(L37:P37)=K37*-1,COUNT(L37:P37),COUNT(L37:AC37)))))),0) Column K shows the quantity of material needed (and is shown as a negative number). Columns L-AC show the quantity of material in each container. I can ship a limit of 24 containers at a time but, if a particular material requires more than 7 containers, this formula shows the total number of continers rather than the number needed. I've looked at some of the solutions that have been offered for similar problems but did not see one that fit what I was seeking. Thanks, ronnnomad |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested IF Limits
On 18 May, 14:56, ronnomad wrote:
=IF(K37<0,IF(L37=K37*-1,COUNT(L37),IF((L37+M37)=K37*-1,COUNT(L37:M37),IF(SUM(L37:N37)=K37*-1,COUNT(L37:N37),IF(SUM(L37:O37)=K37*-1,COUNT(L37:O37),IF(SUM(L37:P37)=K37*-1,COUNT(L37:P37),COUNT(L37:AC37)))))),0) I can ship a limit of 24 containers at a time but, if a particular material requires more than 7 containers, this formula shows the total number of continers rather than the number needed. Here's a non-nested alternative: =MIN(IF(L37K37*-1,1,999),IF(SUM(L37:M37)K37*-1,2,999),IF(SUM(L37:N37)K37*-1,3,999),IF(SUM(L37:O37)K37*-1,4,999),IF(SUM(L37:P37)K37*-1,5,999),IF(SUM(L37:Q37)K37*-1,6,),........) The MIN will find the lowest value in the sequence (ie the first time there is enough units). If this resolves to 999, then you don't have enough units even taking all 24 containers. Caveat: If you don't put a negative value in K37, results will be unexpected. HTH Andrew |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested IF Limits
Andrew,
I guess I needed to supply one more piece of information. The quantity of material in columns L-AC may be less than the number in K. You suggestion works when the total in L-AC is greater than K, but gives no response if the quantity is less. I want to see the number of containers even if I cannot fulfill the total need. Ron "loudfish" wrote: On 18 May, 14:56, ronnomad wrote: =IF(K37<0,IF(L37=K37*-1,COUNT(L37),IF((L37+M37)=K37*-1,COUNT(L37:M37),IF(SUM(L37:N37)=K37*-1,COUNT(L37:N37),IF(SUM(L37:O37)=K37*-1,COUNT(L37:O37),IF(SUM(L37:P37)=K37*-1,COUNT(L37:P37),COUNT(L37:AC37)))))),0) I can ship a limit of 24 containers at a time but, if a particular material requires more than 7 containers, this formula shows the total number of continers rather than the number needed. Here's a non-nested alternative: =MIN(IF(L37K37*-1,1,999),IF(SUM(L37:M37)K37*-1,2,999),IF(SUM(L37:N37)K37*-1,3,999),IF(SUM(L37:O37)K37*-1,4,999),IF(SUM(L37:P37)K37*-1,5,999),IF(SUM(L37:Q37)K37*-1,6,),........) The MIN will find the lowest value in the sequence (ie the first time there is enough units). If this resolves to 999, then you don't have enough units even taking all 24 containers. Caveat: If you don't put a negative value in K37, results will be unexpected. HTH Andrew |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested IF Limits
On 18 May, 19:34, ronnomad wrote:
I want to see the number of containers even if I cannot fulfill the total need. .... then change the last value to be summed from a conditional IF(SUM(L37:AC37)K37*-1,24,999 to an absolute: 24 That way, if there are not enough, it will still return 24. HTH Andrew |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested IF Limits
Sorry, I guess because I know what I want, I not being clear enough. If for
the first item I need 1000 pcs but I only have 700 pieces in two containers, the formula answer ( for that row) should be two. I rarely (if ever) send 24 containers of one item. The shipment is a combination. Ron "loudfish" wrote: On 18 May, 19:34, ronnomad wrote: I want to see the number of containers even if I cannot fulfill the total need. .... then change the last value to be summed from a conditional IF(SUM(L37:AC37)K37*-1,24,999 to an absolute: 24 That way, if there are not enough, it will still return 24. HTH Andrew |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested IF Limits
On 18 May, 20:50, ronnomad wrote:
Sorry, I guess because I know what I want, I not being clear enough. If for the first item I need 1000 pcs but I only have 700 pieces in two containers, the formula answer ( for that row) should be two. I rarely (if ever) send 24 containers of one item. The shipment is a combination. Sorry for delay. I still don't get your requirements - can you give me a more explanatory example? The formula, as it stands, will give you the number of containers required (not the number of pcs) to fulfil each particular request. e.g. 700 in container 1, 700 in container 2, requirement is for 1000 pcs, therefore answer is 2 (more explicitly, answer is 700 from container 1 plus 300 from container 2). The formula I already gave should return 2 in this case. Andrew |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested IF Limits
Andrew,
Your formula does work but only if the total quantity available to ship is greater than the quantity needed. If, as in the example I gave, the quantity available (700 pcs) is less than the quantity needed (1000 pcs), the formula result is 0 (zero). In this instance the answer should be 2 because 2 is the total number of containers available even though the quantity is less than what is needed. And don't give a second thought about the time. I wasn't working yesterday. Thanks, Ron "loudfish" wrote: On 18 May, 20:50, ronnomad wrote: Sorry, I guess because I know what I want, I not being clear enough. If for the first item I need 1000 pcs but I only have 700 pieces in two containers, the formula answer ( for that row) should be two. I rarely (if ever) send 24 containers of one item. The shipment is a combination. Sorry for delay. I still don't get your requirements - can you give me a more explanatory example? The formula, as it stands, will give you the number of containers required (not the number of pcs) to fulfil each particular request. e.g. 700 in container 1, 700 in container 2, requirement is for 1000 pcs, therefore answer is 2 (more explicitly, answer is 700 from container 1 plus 300 from container 2). The formula I already gave should return 2 in this case. Andrew |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested IF Limits
On 29 May, 14:05, ronnomad wrote:
Your formula does work but only if the total quantity available to ship is greater than the quantity needed. If, as in the example I gave, the quantity available (700 pcs) is less than the quantity needed (1000 pcs), the formula result is 0 (zero). In this instance the answer should be 2 because 2 is the total number of containers available even though the quantity is less than what is needed. The only condition where the quantity available (700 pcs) is less than the quantity needed (1000 pcs) should be once you have tried the last container (the 24th one). The second formula I gave should return 24 even if there are not enough pieces in all 24 containers: .... then change the last value to be summed from a conditional IF(SUM(L37:AC37)K37*-1,24,999 to an absolute: 24 So let me restate back as a question. Why should your example above return 2 instead of moving on to the 3rd container? (and the 4th, ..... until it either finds enough pieces, or reaches the 24th container) HTH Andrew |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested IF Limits
The formula should return 2 because only 2 containers are available. If, in
the example, this particular part is packed 350 to a container and I need 1000, and there were an unlimited number of containers available, the formula should return 3. However, if there were only 2 containers avaialble, it should return 2. Please note that this is only one of many items to be shipped and the shipment (in every instance) is an aggregate of all the items. I may have 2 containers of the first item, 5 of the second, 3 of the third and so on. Although the number of containers for an individual item is important, the total number of containers, for all items combined, cannot exceed 24. If it does, then I have to 'cherry pick' and decide what to leave off. Although I don't believe this is critical, I should note that the materials in question come off a production line and the final process results in each container quantity being variable. Because of this, I should also note that I use a second formula (in essence a duplicate of the first but instead of counting containers it sums the quantity in the containers). So, going back to the example of unlimited containers, the result shoud be 3 containers and 1,050 pcs. My original formulas sufficed so long as no single item required more than 7 containers. "loudfish" wrote: On 29 May, 14:05, ronnomad wrote: Your formula does work but only if the total quantity available to ship is greater than the quantity needed. If, as in the example I gave, the quantity available (700 pcs) is less than the quantity needed (1000 pcs), the formula result is 0 (zero). In this instance the answer should be 2 because 2 is the total number of containers available even though the quantity is less than what is needed. The only condition where the quantity available (700 pcs) is less than the quantity needed (1000 pcs) should be once you have tried the last container (the 24th one). The second formula I gave should return 24 even if there are not enough pieces in all 24 containers: .... then change the last value to be summed from a conditional IF(SUM(L37:AC37)K37*-1,24,999 to an absolute: 24 So let me restate back as a question. Why should your example above return 2 instead of moving on to the 3rd container? (and the 4th, ..... until it either finds enough pieces, or reaches the 24th container) HTH Andrew |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Value Limits in a Cell | Setting up and Configuration of Excel | |||
nested if based on nested if in seperate sheet. how? | Excel Worksheet Functions | |||
Overcoming Nested IF limits | Excel Discussion (Misc queries) | |||
Row Limits | Excel Worksheet Functions | |||
Limits | Excel Discussion (Misc queries) |