Multiple if statements to satisfy the requirement

[imaliuddin]

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>

Thanks in advance

 

[Eric W]

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

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula 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)))) <thead> </thead><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.

 

[imaliuddin]

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>

Thanks in advance.

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

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

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula 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]

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.