IF(And Statement

[lqorri]

Hello- i am having some difficulties creating a formula to help me figure out the forecast accuracy and fulfillment rate with different if scenarios.

Forecast accuracy column: If forecast is 10,700 and i sold 8,108 that mean i over forecasted by 24%. Also, if my forecast is 85 but i sold 176 that means i under forecasted by 107%. Also, what i would like to take into account is the failed to ship portion. (176+140)-85)/85=271.76%. My forecast for row 4 is under forecasted by 271.76%. Last example for row 5 where my forecast is 45 but i failed to ship i would like my forecast to show 100% as i was going to sell 100% of my forecast but other issues occurred. the accuracy for row 6 would have to be 100% as there was no forecast but we had an order and failed to ship it.

Fulfillment rate column: row 3 fulfillment rate would equal 100% as none failed to shipped, the products was undersold which forecast accuracy column would show. row 4 fulfillment rate i would like to take into account 176/(176+140) which would equal 56% fulfillment rate. row 5 i would like to equal 0% as 45 was forecasted and 45 failed to shipped. row 6 would be 0% as well as customer needed 5 but failed to provide regardless of no forecasted.


These are the different scenarios i would like the formula to take into account but i have encoutered some issues along the way. So far this is what i have created which helps in some scenarios and so much in others.

forecast accuracy formula- =IF(AND(C3>1),(D3-C3)/C3,IF(AND(SUM(C3:E3)=0,),0,IF(AND(C3=0,D3>0),-1,0)))

fulfillment rate formula- =IF(AND(E3=0,C3=0),0,IF(AND(E3>0,D3>0,C3>0),D3/(E3+D3),1))

Thank you for your help in advance.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Forecast[/TD]
[TD]Sales[/TD]
[TD]Failed to Ship[/TD]
[TD]Forecast Accuracy[/TD]
[TD]Fulfillment Rate[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10,700[/TD]
[TD]8,108[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]85[/TD]
[TD]176[/TD]
[TD]140[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]45[/TD]
[TD][/TD]
[TD]45[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[BrianJN1]

Firstly your two formulae are always going to create issues as they form "Circular references", ie, your Forecast Accuracy is to reside in Col D but your formula there refers to that cell. The same goes for Fulfillment.

Try these in Col D: =IF(A3>1,((B3+C3)-A3)/A3*100,"")
and Col E: =B3/(B3+C3)*100

I believe that the formulae follow the logic which you have explained in your text.

 

[lqorri]

Hi Brian,

thank you so much for your help. the forecast accuracy formula works fine for majority but i forgot i had some other variables. Also, i slightly modified the original formula to make sure the % are correct. Multiplying by 100 gave me a large accuracy. Changed from 100 to 1.

Forecast Accuracy- when there is a forecast of 31 and we have sold 31 the accuracy should be 100% but in mine shows 0%. Also, if there is sales but no forecast and no failed shipments the accuracy should be -100%.

Fulfillment Rate- works great, the only issue is if there are no sales or failed to ship i get a #DIV/0




Thank you for your help in advance.


[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Forecast[/TD]
[TD]Sales[/TD]
[TD]Failed to Ship[/TD]
[TD]Forecast Accuracy[/TD]
[TD]Fulfillment Rate[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10,700[/TD]
[TD]8,108[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]85[/TD]
[TD]176[/TD]
[TD]140[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]45[/TD]
[TD][/TD]
[TD]45[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]31[/TD]
[TD]31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[BrianJN1]

How do these adjustments fit?
D3: =IF(AND(A3=0,B3>0),-100,IF(AND(A3=B3,C3=0),-100,IF(A3>1,((B3+C3)-A3)/A3*100,"")))
E3: =IF(AND(B3>0,C3>=0),B3/(B3+C3)*100,0)

 

[lqorri]

Thank you, they work great.