Multiple if statements to satisfy the requirement

imaliuddin

New Member
Hello Experts,

I need your help to automate a long manual activity which takes allot of time. Every week I have received stock transfer requests of 500 line items from LHR and ISB to replenish the stock from KHI which is our central Warehouse to feed both locations. Please see below table with expected results which i want to be derived from formula. Purpose is to make inventory transfer of available quantity in KHI to LHR/ISB with equality keeping in view who has more deficit and who has more average monthly sales/consumption. Below are the conditions which I have made and if anyone of you feels that is not logical or need changes you can make changes in the attach excel file while replying to the solution.

 Note: Stock can not be transfer in decimals and negative numbers it has to be a whole number so rounding would be required keeping in view the available stock in KHI up to the max of LHR and ISB average monthly sales. Below are the logics 1-Since KHI is our mother location so it has to be make sure KHI should not be < its monthly sales no matter LHR and ISB has zero stock 2-If LHR or ISB has current stock = their monthly average sales then no transfer is required no matter quantities are available in KHI 3-Transfer will only be executed if LHR / ISB has < its average monthly sales means if their current deficit < 100 4- if KHI has enough stock to fulfill both LHR/ISB requirement then relenish both upto their max deficit 5- if KHI has only one extra qty and deficit of anyone location is >= 80% then proceed transfer to that location 6- if KHI has only one extra qty and deficit of both location is >= 80% then check whose average sales is higher then otherone then proceed for that location 7- if both locations deficit is equal and KHI available is not enough to satisfy both then the qty will be equally devided equally to both keeping in view of if quantity is ODD then it will be -1 then divided by 2 8- If one has defcit >= 80% and other has <=20% and available stock can completely fullfil the one has higher deficit then all feed to that one and if still have leftover stock in khi then send to other location 9- If one has defcit >= 80% and other has <=20% and khi has not enough to fullfill the one has higher deficit completely then available stock will be splitted in 80-20% respectively 10- If one has defcit >= 60% and other has <=35% and available stock can completely fullfil the one has higher deficit then all feed to that one and if still have leftover stock in khi then send to other location 11- If one has defcit >= 60% and other has <=35% and khi has not enough to fullfill the one has higher deficit completely then available stock will be splitted in 60-35% respectively 12- If one has defcit >= 45% and other has <=35% and khi has not enough to fullfill the one has higher deficit completely then available stock will be splitted in 45-35% respectively 13- If one has defcit >= 45% and other has <=35% and available stock can completely fullfil the one has higher deficit then all feed to that one and if still have leftover stock in khi then send to other location 14- If one has defcit >= 35% and other has <=20% then available stock will be splitted equally keeping in view if quantity is ODD then it will be -1 then divided by 2 15- If one has defcit <= 35% and other has <=20% then available stock will be splitted equally keeping in view of ODD then it will be -1 then divided by 2

<tbody>
</tbody>

 Average Monthly Sales Current Stock Current Deficit This is the expected outcome Items KHI LHR ISB KHI LHR ISB LHR Qty LHR% ISB Qty ISB How much Extra in KHI LHR ISB Item-01 81 9 21 200 20 15 11 122% -6 -29% 119 Enough Stock 6 Item-02 67 32 28 108 25 25 -7 -22% -3 -11% 41 7 3 Item-03 57 30 11 36 33 10 3 10% -1 -9% -21 KHI Low KHI Low Item-04 0 26 14 36 0 7 -26 -100% -7 -50% 36 26 7 Item-05 66 33 17 200 10 30 -23 -70% 13 76% 134 23 Enough Stock Item-06 77 26 20 118 30 36 4 15% 16 80% 41 Enough Stock Enough Stock Item-07 81 15 22 90 7 15 -8 -53% -7 -32% 9 5 4 Item-08 59 10 32 67 7 18 -3 -30% -14 -44% 8 1 7 Item-09 42 26 27 43 17 8 -9 -35% -19 -70% 1 0 1 Item-10 53 40 29 66 31 22 -9 -23% -7 -24% 13 7 6

<tbody>
</tbody>

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Eric W

MrExcel MVP
Welcome to the MrExcel Board!

You have a LOT of rules, and I wonder how important each of them are. I don't know if you devised them, or if you're just the person designated to enforce them. I'm sure that it would be possible to come up with a set of formulas that encompass your rules exactly, but it would take a LOT of analysis, and would be quite cumbersome to maintain. I tried to come up with a method that maintains the general spirit of the rules, but is easier to incorporate. Consider:

Excel 2012
ABCDEFGHIJKLMN
1Average Monthly SalesCurrent StockCurrent DeficitThis is the expected outcome
2ItemsKHILHRISBKHILHRISBLHR QtyLHR%ISB QtyISB%How much Extra in KHILHRISB
3Item-0181921200201511122%-6-29%119Enough Stock6
4Item-026732281082525-7-22%-3-11%4173
5Item-03573011363310310%-1-9%-21Enough StockKHI Low
6Item-04026143607-26-100%-7-50%36267
7Item-056633172001030-23-70%1376%13423Enough Stock
8Item-067726201183036415%1680%41Enough StockEnough Stock
9Item-0781152290715-8-53%-7-32%954
10Item-0859103267718-3-30%-14-44%817
11Item-0942262743178-9-35%-19-70%101
12Item-10534029663122-9-23%-7-24%1376

</tbody>
Sheet1

Worksheet Formulas
CellFormula
H3=F3-C3
I3=H3/C3
J3=G3-D3
K3=J3/D3
L3=E3-B3
M3=IF(H3>=0,"Enough Stock",IF(L3<=0,"KHI Low",MIN(-H3,ROUND(L3*-H3/(-H3+MAX(0,-J3)),0))))
N3=IF(J3>=0,"Enough Stock",IF(L3<=0,"KHI Low",MIN(-J3,ROUND(L3*-J3/(-J3+MAX(0,-H3)),0))))

</tbody>

<tbody>
</tbody>

The formula first checks for whether LHR or ISB needs any stock, then checks if KHI has any available, and if so, assigns the available stock to LHR and ISB proportionately based on need. I was a bit surprised when it turned out that it matches your expected outcome exactly. I have little doubt that it will vary from your rules in some cases, but it should be pretty close. In fact I think an in-depth analysis of your rules and my formula (which I do not volunteer to do!) would show a lot more overlap than exceptions. Anyway, give it a try and see what you think.

imaliuddin

New Member
Dear Eric,

First of all thank you so much for replying me in such detail. I have tried your logics and results are preety much closer to what i am looking for. I have found some discrepancies as below if you can further eliminate those it would be of great help.

1. When both LHR(C) and ISB(D) has same avg monthly sales and same deficit as LHR(H) and ISB(J) and available quantity is only 1(L) then it returned LHR(M) 1 and ISB(N) 1 which is
practically not possible as KHI has only 1 extra to feed.

2. When both C and D has different avg sales and different deficit and available quantity is not enough to feed both but it feeds more then its available quantity to M and N

 A B C D E F G H I J K L M N Average Monthly Sales Current Stock Current Deficit How much Extra in KHI How much to transfer from KHI to.. KHI LHR ISB KHI LHR ISB LHR Qty LHR% ISB Qty ISB LHR ISB Discrepancy-1 81 26 26 82 15 15 -11 -42% -11 -42% 1.0 (Only 1 available so can't feed to both) 1.0 1.0 Discrepancy-2 81 26 25 84 15 14 -11 -42% -11 -44% 3 (Only 3 available so can't feed 2 to each) 2.0 2.0

<tbody>
</tbody>

Welcome to the MrExcel Board!

You have a LOT of rules, and I wonder how important each of them are. I don't know if you devised them, or if you're just the person designated to enforce them. I'm sure that it would be possible to come up with a set of formulas that encompass your rules exactly, but it would take a LOT of analysis, and would be quite cumbersome to maintain. I tried to come up with a method that maintains the general spirit of the rules, but is easier to incorporate. Consider:

Excel 2012
ABCDEFGHIJKLMN
1Average Monthly SalesCurrent StockCurrent DeficitThis is the expected outcome
2ItemsKHILHRISBKHILHRISBLHR QtyLHR%ISB QtyISB%How much Extra in KHILHRISB
3Item-0181921200201511122%-6-29%119Enough Stock6
4Item-026732281082525-7-22%-3-11%4173
5Item-03573011363310310%-1-9%-21Enough StockKHI Low
6Item-04026143607-26-100%-7-50%36267
7Item-056633172001030-23-70%1376%13423Enough Stock
8Item-067726201183036415%1680%41Enough StockEnough Stock
9Item-0781152290715-8-53%-7-32%954
10Item-0859103267718-3-30%-14-44%817
11Item-0942262743178-9-35%-19-70%101
12Item-10534029663122-9-23%-7-24%1376

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
H3=F3-C3
I3=H3/C3
J3=G3-D3
K3=J3/D3
L3=E3-B3
M3=IF(H3>=0,"Enough Stock",IF(L3<=0,"KHI Low",MIN(-H3,ROUND(L3*-H3/(-H3+MAX(0,-J3)),0))))
N3=IF(J3>=0,"Enough Stock",IF(L3<=0,"KHI Low",MIN(-J3,ROUND(L3*-J3/(-J3+MAX(0,-H3)),0))))

<tbody>
</tbody>

<tbody>
</tbody>

The formula first checks for whether LHR or ISB needs any stock, then checks if KHI has any available, and if so, assigns the available stock to LHR and ISB proportionately based on need. I was a bit surprised when it turned out that it matches your expected outcome exactly. I have little doubt that it will vary from your rules in some cases, but it should be pretty close. In fact I think an in-depth analysis of your rules and my formula (which I do not volunteer to do!) would show a lot more overlap than exceptions. Anyway, give it a try and see what you think.

Eric W

MrExcel MVP
The issue is the ROUND part of the formula. If you have 2 values that are .5 and .5, then ROUND will round them both up to 1. There are a couple ways to get around this. First, you can change the N3 formula to:

=IF(J3>=0,"Enough Stock",IF(L3<=0,"KHI Low",MIN(-J3,L3-N(M3),ROUND(L3*-J3/(-J3+MAX(0,-H3)),0))))

by adding the part in red, it makes sure that the sum of the LHR and ISB values does not exceed the L3 value. So essentially, if there is a ROUNDUP issue, the LHR formula will get the extra unit. If you want to give the extra unit to ISB, change the M3 formula instead.

The second option is to remove the ROUND from both formulas and use INT instead:

M3: =IF(H3>=0,"Enough Stock",IF(L3<=0,"KHI Low",MIN(-H3,INT(L3*-H3/(-H3+MAX(0,-J3))))))
N3: =IF(J3>=0,"Enough Stock",IF(L3<=0,"KHI Low",MIN(-J3,INT(L3*-J3/(-J3+MAX(0,-H3))))))

This is roughly the equivalent of your requirement that if the number is odd, subtract 1 then divide by 2. Using INT essentially rounds down always. Try both versions and see which works best for you.