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

    Complex 'if" formula

    Nayan Lalla

      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
          Thomas Skariah

          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
              Nayan Lalla

              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 codeAverageForecast Oct-2012Nov-2012Dec-2012Jan-2013SOHStock HoldingIdeal Stock HoldingMaximum Stock Holding
              A562771,0765546077548.91,3532,515
              B35635135021342.691154
              C2,3013282,5521,1462,5172,7660.62,8806,543
              D2,5891,7588764,129-44,6640.82,6336,758
              E8912,1057471,4172,8991,5191.62,8527,168
              F1704361,5332621,4172765.21,9693,648
              G3833967574005811,4334.01,1532,134
              H1,3985932,0452,9411,2233,8783.12,6386,802
              I2,5931,7893,9893,4562,2376,4472.35,77711,471
              J3,9496,4209,7714,7215,2434,2510.716,19126,155
              K1,3522,4202,4641,64501,4800.84,8846,529
              Total15,68516,57725,84520,68616,76827,6821.242,42279,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