Multiple if statements to satisfy the requirement

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. I have attached an excel file with expected results in columns N and O respectively. The Purpose is to make the distribution with keeping in view the justice and equality. 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 it has to be a whole number
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



Excel file is attached to give you more understanding.

Thanks in advance

Attachment 645989

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

After the first few logics there are more possible percentage splits than those listed and there are other issues.
For example logics 8 and 9, what happens when one store has > 80% deficit and the other has > 20%.
Logic 12 splits stock 45% - 35%, which only accounts for 80% of the surplus stock held at KHI.
Here are my formulas (modeled in columns P:Q) which cover the first four logics and which yield the expected values for items 1 - 6:
For LHR:=IF($L3<=0,"KHI Low",IF(H3>=0,"Enough Stock",IF(-SUM(MIN(H3,0),MIN(J3,0,))<L3,-H3)))
For ISB:=IF($L3<=0,"KHI Low",IF(J3>=0,"Enough Stock",IF(-SUM(MIN(H3,0),MIN(J3,0,))<L3,-J3)))
Consider simplifying the rest of the logics to say: "The store that has the greatest monthly sales for an item will get preference of restocking for that item. If both stores have an equal monthly sales for an item then the store that has the greatest total monthly sales will get the preference."
Using that rational the formulas could be (as modeled in columns R:S):
For LHR:

Formula:

Please Login or Register  to view this content.

For ISB:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.