nested if optimising

good day all,

i have been trying to crack this for a while now, but i cant seem to do it the file i have is to link how often we order with the lead time and keep a safety stock of a month and a half extra depending on the RC for example if its 1 then i have to keep 1.5 months extra, but if it is 2 then i have to keep 2.5 months extra. i also have to test this against the arriving month so i have 6 categories of RC and 6 categories of Lead time and testing all possibilities resulted in a 36 nested if statements to do it. i will attach the original formula (if u have time to read it) and a small test file , i hope you can help me out since excel is not allowing me to save the formula for it is more than 8K characters i had to save it as binary file.

your help is much appreciated

Hi George

I have many doubts (almost as many as the docx lines). I'll try to systematize just a few:

1) The stock averages for each month are in adjacent and ordered columns, ie [@ [avg cogs jan]]: [@ [avg cogs dec]]?
2) When [@ [Review Cycle]] = 4 the formula presents 2 distinct methods for each [@ receiving month]]. Do you want the highest of values?
3) In the last part of your formula, the "if" referring [@ Review Cycle] = 6 seems to contain the "else" part which makes me think if [@ Cycle Review] can be greater than 6. It is true?

See the formula:

=MAX(0,Sum_12+
IF([@[Review Cycle]]=1,
IF([@[receiving month]]=3, 
-([@[7th of jan stock value -drp str and reserved]]-[@[avg cogs jan]]-[@[avg cogs feb]]),
IF([@[receiving month]]=4,
-[@[7th of jan stock value -drp str and reserved]]-[@[avg cogs jan]]-[@[avg cogs feb]]-[@[avg cogs mar]],
IF([@[receiving month]]=5,
-[@[May stock]],
IF([@[receiving month]]=6,
-[@[May stock]]-[@[avg cogs may]],[@[avg cogs jul]]+[@[avg cogs aug]]+0.5*[@[avg cogs sep]])))),

IF([@[Review Cycle]]=2,
IF([@[receiving month]]=3,
-[@[7th of jan stock value -drp str and reserved]]-[@[avg cogs jan]]-[@[avg cogs feb]],
IF([@[receiving month]]=4, 
-[@[7th of jan stock value -drp str and reserved]]-[@[avg cogs mar]]-[@[avg cogs feb]]-[@[avg cogs jan]],
IF([@[receiving month]]=5,
-[@[May stock]],
IF([@[receiving month]]=6,
-[@[May stock]]-[@[avg cogs jun]],[@[avg cogs jul]]+[@[avg cogs aug]]+[@[avg cogs sep]]+0.5*[@[avg cogs oct]]-[@[May stock]]-[@[avg cogs jun]]-[@[avg cogs may]])))),

IF([@[Review Cycle]]=3,
IF([@[receiving month]]=3,
-([@[May stock]]+[@[avg cogs mar]]+[@[avg cogs apr]]),
IF([@[receiving month]]=4,
-([@[May stock]]-[@[avg cogs apr]]),
IF([@[receiving month]]=5,
-[@[May stock]],
IF([@[receiving month]]=6,
-[@[May stock]]-[@[avg cogs jun]],[@[avg cogs jul]]+[@[avg cogs aug]]+[@[avg cogs sep]]+[@[avg cogs oct]] +0.5*[@[avg cogs nov]]-[@[May stock]]-[@[avg cogs jun]]-[@[avg cogs may]])))),

IF([@[Review Cycle]]=4,
IF([@[receiving month]]=3,
-[@[7th of jan stock value -drp str and reserved]]-[@[avg cogs jan]]-[@[avg cogs feb]],
IF([@[receiving month]]=4,
-([@[May stock]]-[@[avg cogs apr]]),
IF([@[receiving month]]=5,
-[@[May stock]],
IF([@[receiving month]]=6,
-[@[May stock]]-[@[avg cogs jun]],+[@[avg cogs jul]]+[@[avg cogs aug]]+[@[avg cogs sep]]+[@[avg cogs oct]]+[@[avg cogs nov]]+0.5*[@[avg cogs dec]]-[@[May stock]]-[@[avg cogs may]]-[@[avg cogs jun]])))),

IF([@[Review Cycle]]=4,
IF([@[receiving month]]=3,
-[@[7th of jan stock value -drp str and reserved]]-[@[avg cogs jan]]-[@[avg cogs feb]],
IF([@[receiving month]]=4,
-[@[May stock]]+[@[avg cogs apr]],
IF([@[receiving month]]=5,
-[@[May stock]],
IF([@[receiving month]]=6,
-[@[May stock]]-[@[avg cogs may]],[@[avg cogs jul]]+5.5*[@[avg cogs]]-[@[May stock]]-[@[avg cogs jun]])))),

IF([@[Review Cycle]]=5,
IF([@[receiving month]]=3,
-[@[May stock]]-[@[avg cogs apr]]-[@[avg cogs mar]],
IF([@[receiving month]]=4,
-[@[May stock]]-[@[avg cogs apr]],
IF([@[receiving month]]=5,
-[@[May stock]],
IF([@[receiving month]]=6,
-[@[May stock]]-[@[avg cogs may]],[@[avg cogs jul]]+6.5*[@[avg cogs]]-[@[May stock]]-[@[avg cogs may]]-[@[avg cogs jun]])))),

IF([@[Review Cycle]]=6,
IF([@[receiving month]]=3,
-[@[May stock]]-[@[avg cogs apr]]-[@[avg cogs mar]],
IF([@[receiving month]]=4,
-[@[May stock]]-[@[avg cogs apr]],
IF([@[receiving month]]=5,
-[@[May stock]],
IF([@[receiving month]]=6,
-[@[May stock]]-[@[avg cogs may]],[@[avg cogs jul]]+7.5*[@[avg cogs]]-[@[May stock]]-[@[avg cogs may]]-[@[avg cogs jun]])))),

IF([@[receiving month]]=3,
-[@[May stock]]-[@[avg cogs apr]]-[@[avg cogs mar]],
IF([@[receiving month]]=4,
-[@[May stock]]-[@[avg cogs apr]],
IF([@[receiving month]]=5,
-[@[May stock]],
IF([@[receiving month]]=6,
-[@[May stock]]-[@[avg cogs may]]-[@[avg cogs jun]]
))))))))))))

This formula calculates Sum_12
=SUMPRODUCT((COLUMN($A$1:$L$1)>= [@[receiving month]])*(COLUMN($A$1:$L$1)<= [@[receiving month]]
+[@[Review Cycle]])+(COLUMN($A$1:$L$1)= [@[receiving month]]
+[@[Review Cycle]]+1)*0.5,[@[avg cogs jan]:[avg cogs dec]])

IF (Review Cycle] < 5),
IF([receiving_month] =3, [@[avg cogs mar] + [avg cogs apr] +(IF[Review Cycle] > 1,[avg cogs may], 0) + (IF[Review Cycle] > 2,[avg cogs jun],0) + (IF[Review Cycle] > 3,[avg cogs jul],0) +0.5*[LOOKUP([Review Cycle],{2,3,4},{[avg cogs may], [avg cogs jun], [avg cogs jul]}-([@[7th of jan stock value -drp str and reserved]]-(LOOKUP([Review Cycle],{1,2,3,4},{[@[avg cogs jan]]-[@[avg cogs feb]], [@[avg cogs jan]]-[@[avg cogs feb]], [@[avg cogs jmar]]-[@[avg cogs apr], [@[avg cogs jan]]-[@[avg cogs feb]])



i think this is exactly what i want, thank you very much