Help with logic please!

[Jat999]

Hi all

I am struggling with nested IF statements to get the logic correct for the following problem:

I am trying to calculate forward forecast consumption. There are some rules which need to be observed.

Assume whole week buckets. Sales orders consume forecast for current week and or following week

[TABLE="width: 628"]
<colgroup><col><col><col span="6"></colgroup><tbody>[TR]
[TD][TABLE="width: 628"]
<colgroup><col><col><col span="6"></colgroup><tbody>[TR]
[TD]Sales Orders[/TD]
[TD][/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sales F/Cast[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Expected remaining F/Cast[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[/TR]
</tbody>[/TABLE]

The formula I have used is:
=IF(AND(F18-F19>0,F22=0,G19<(F18-F19)),0,IF(G18>G19,0,IF(AND(G19-G18>0,G18>0),(G18+(F19-F18)),IF(G19-G18=0,0,G19-G18))))<strike></strike>

On the above table, the results are as expected, until I change the cell with value 120 to say - 80. Here the results then change.

If anyone can help me with this, i will be deeply grateful

Thanks in advance
John
[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[AlanY]

it would be easier if you could clarify the rule/condition in words so that is less guess work for us

 

[Jat999]

Sorry - Thanks Alan

Rules are:
Sales F/cast is consumed by Sales Orders in current week. Any remaining Sales order can then consume ONLY following week.
Using the example shown above
Week 1 Sales F/C = 0, Sales Order = 200. Result Sales F/C = 0 - as there was no Sales F/C but...
Week 2 Sales F/C = 100, Sales Order = 0. Result Sales F/C = 0 - here the Sales F/C is consumed by the Sales Order of previous week.
Week 3 Sales F/C = 100, Sales Order = 120. Result Sales F/C = 0 - All Sales Order consumes current week F/C and there is 20 to carry over into next week
Week 4 Sales F/C = 100, Sales Order = 50. Result Sales F/C =30 - Sales order of 50 plus the 20 carry over minused from the 100 = 30
Week 5&6 Sales F/C = 100, Sales Order = 0. Result Sales F/C = 100. - No sales orders.

The formula I pasted works until you reduce week 3 sales order to less than 100. This is where I think my formula logic is flawed.

Again, thanks in advance
John

Week 4

 

[Jat999]

Alan and all

I think I have cracked it, maybe not in the most elegant way, but seems to respond with all I throw at it.
Would be interested in feedback if you think I can improve the logic.

Updated formula
=IF(AND(F18-F19>0,F22=0,G19<(F18-F19)),0,IF(G18>G19,0,IF(AND(G19-G18>0,G18>0),IF(G19-G18>0,IF(AND(F22=0,F18-F19>0),G19-(F18-F19+G18),G19-G18)),IF(F22=0,(G18+(F19-F18)),G19-G18))))

 

[Jat999]

I celebrated too early. Still not working.

any ideas please?

 

[dreid1011]

See if this works for you (there may yet be a cleaner way to write this):

=IF(OR(C3=0,C2-C3>=0,((B2-B3)+C2)-C3>=0),0,IF(B2-B3>0,ABS(((B2-B3)+C2)-C3),ABS(C2-C3)))


Here is how I set up the table(change the references in my formula to suit your data):

Row 1 is Column Headers. Week 1-6 etc

Row 2 is Sales Order (Col A is Row Headers, Col B is empty, Col C-H is data)
Row 3 is Sales Forcast (Col A is Row Headers, Col B is empty, Col C-H is data)

Row 4 is results with formula in Col C-H

The formula works without errors if Col B is left blank. If not, then my formula will probably return an error in the first cell due to performing mathematical operations on text values.

 

[Jat999]

A huge thanks - tested it in the operational workbook - 100%.



John

 

[dreid1011]

I am glad that is working for you. I had my doubts as I didn't test every possibility.