nested if optimising

[george.m]

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

[Glenn Kennedy]

Can you explain exactly what you want a revised formula to do. Your sample file should include some REPRESENTATIVE samples, along with manually calculated results. In your current sheet, I see a pile of 1s, little explanation and no indication of your expected (manually calculated) results.

[José Augusto]

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:

Please Login or Register  to view this content.

[pjwhitfield]

I think there can be quite a bit optimised here by totally changing around he way the IFs are done.

For example all of the Month 3 and 4 items are along the same lines for the 1st 4 Review Cycles so as I see it you could lump them in one IF statement

The difference is in the [Review Cycle] which, in each iteration moves forward in the months included, this is the formula for [Review Cycle =1, receiving_month = 3]

Please Login or Register  to view this content.

And heres the [Review Cycle = 4] for month = 3

Please Login or Register  to view this content.

The difference is the number of months added at the start and the number you add relates to the Review Cycle.
So at the worst you could say

Please Login or Register  to view this content.

Then do the same sort of thing for month 4, this will condense 8 IF statements into 2.

[george.m]

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