7 Replies Latest reply: Sep 6, 2012 6:51 AM by Nayan Lalla

Complex 'if" formula

Hi

I have the following complex "if" formula below which does not produce a complete result or only produce a result for one of the conditions.  And if I apply one of the 4 conditions and comment the others out, then the formula works individually.  Please can you tell me where i am going wrong

if(SOH< [Ideal Stock Holding],if([Stock Holding]<2,'Low Stock',

if(SOH> [Maximum Stock Holding],if([Stock Holding]>8,'Overstock',

if(SOH< [Ideal Stock Holding],if([Stock Holding]>8,'Consider',

if(SOH> [Maximum Stock Holding],if([Stock Holding]<3,'Consider',''))))))))

Thank you

kind regards

Nayan

• Re: Complex 'if" formula

Hi Nayan,

Can you explain the content in SOH,Ideal Stock Holding and Maximum Stock Holding or provide a sample xl file.

Regards,

Tom

• Re: Complex 'if" formula

Hi Tom

SOH - Is my current Stock on hand

Ideal Stock Holding - is the Ideal Qty on Hand we should keep which is the sum of the next 2 months sales quantity

Maximium Stock holding - this the maximum qty on hand that we should keep which is the sum of the next 4 months sales qunatity

Stock Holding -  which is current Stock on hand divided by my Average  (which is average last 6 months sales)

below is the table

 Item code Average Forecast Oct-2012 Nov-2012 Dec-2012 Jan-2013 SOH Stock Holding Ideal Stock Holding Maximum Stock Holding A 56 277 1,076 554 607 754 8.9 1,353 2,515 B 3 56 35 13 50 213 42.6 91 154 C 2,301 328 2,552 1,146 2,517 2,766 0.6 2,880 6,543 D 2,589 1,758 876 4,129 -4 4,664 0.8 2,633 6,758 E 891 2,105 747 1,417 2,899 1,519 1.6 2,852 7,168 F 170 436 1,533 262 1,417 276 5.2 1,969 3,648 G 383 396 757 400 581 1,433 4.0 1,153 2,134 H 1,398 593 2,045 2,941 1,223 3,878 3.1 2,638 6,802 I 2,593 1,789 3,989 3,456 2,237 6,447 2.3 5,777 11,471 J 3,949 6,420 9,771 4,721 5,243 4,251 0.7 16,191 26,155 K 1,352 2,420 2,464 1,645 0 1,480 0.8 4,884 6,529 Total 15,685 16,577 25,845 20,686 16,768 27,682 1.2 42,422 79,875
• Re: Complex 'if" formula

HI Nayan,

Maybe you have to sort your expression a little bit,

if(SOH< [Ideal Stock Holding],

if([Stock Holding]<2,'Low Stock',

if([Stock Holding]>8,'Consider', '')),

if(SOH> [Maximum Stock Holding],

if([Stock Holding]<3,'Consider',

if([Stock Holding]>8,'Overstock', ''))

, ''))

So the expression first looking at all the Ideal Stock Holding and if it finds nothing there it is going through the Maximum Stock Holding.

Regards

• Re: Complex 'if" formula

Hi Vicky

Thank you .  Will apply your method and get back to you.

kind regards

Nayan

• Re: Complex 'if" formula

I have change the example file with your data. My expression is working if you set a comma after the second "Consider". I have forgotten it first and changed my post yet.

Regards

Vicky

• Re: Complex 'if" formula

Hi Nayan,

Try this code

 if(SOH<[Ideal Stock Holding] and [Stock Holding]<2,'Low Stock', if(SOH<[Ideal Stock Holding] and [Stock Holding]>8,'Consider', if(SOH>[Maximum Stock Holding] and [Stock Holding]<3,'Consider', if(SOH>[Maximum Stock Holding] and [Stock Holding]>8,'Overstock')))) as Status

Not sure whether your requirement fullfill this code.

Regards,

Tom

• Re: Complex 'if" formula

Hi Vicky and Tom

Both your methods work.  Thank you.  Much appreciated.

kind regards

Nayan