Skip to main content

I need to create a nested IF(AND statement for my commission sheets.


Logical Argument 1: If the Set By field has a value in it AND

Logical Argument 2: If the Month field is January, February, March or April


The result would be:



  1. Calculate the formula Profit/2 multiplied by .125, otherwise blank.


I also need in the same column:


Logical Argument 1: If the Set By field has a value in it AND

Logical Argument 2: If the Month field is May, June, July, August, September, October, November, December


The result would be:



  1. Calculate the formula Profit multiplied by .0625 ., otherwise blank.

This assumes, based on the way you worded your request, that the Month field produces a string with the month name. If this is not the case (ie, it returns a number or an actual date data-type, the OR() statements may need to edited to reflect the appropriate data type):


IF(

   AND(

      {Set By},

      OR(

         Month = "January",

         Month = "February",

         Month = "March",

         Month = "April"

      )

   ),

   (Profit / 2) * 0.125,

   IF(

      AND(

         {Set By},

         OR(

            Month = "May",

            Month = "June",

            Month = "July",

            Month = "August",

            Month = "September",

            Month = "October",

            Month = "November",

            Month = "December"

         )

      ),

      Profit * 0.0625

   )

)

This assumes, based on the way you worded your request, that the Month field produces a string with the month name. If this is not the case (ie, it returns a number or an actual date data-type, the OR() statements may need to edited to reflect the appropriate data type):


IF(

   AND(

      {Set By},

      OR(

         Month = "January",

         Month = "February",

         Month = "March",

         Month = "April"

      )

   ),

   (Profit / 2) * 0.125,

   IF(

      AND(

         {Set By},

         OR(

            Month = "May",

            Month = "June",

            Month = "July",

            Month = "August",

            Month = "September",

            Month = "October",

            Month = "November",

            Month = "December"

         )

      ),

      Profit * 0.0625

   )

)

Oh my lanta. You are a life saver. Thank you SO very much.


Not sure how my eye caught this so quickly, but the calculation in Jeremy’s solution has the operators reversed:



Based on your description, I believe it should be:


(Profit / 2) * 0.125,

Not sure how my eye caught this so quickly, but the calculation in Jeremy’s solution has the operators reversed:



Based on your description, I believe it should be:


(Profit / 2) * 0.125,

Thanks, @Justin_Barrett. Fixed it.

Of course,


(Profit / 2) * 0.125


is the same as


Profit * 0.0625


which removes the need for the formula 🙂


(@Kristen_Gerzin - maybe one of the blocks of months needs a different commission calculation?)


JB

You make a completely valid point. I will need to revisit this with my sales team, as they were originally paying the 12.5% out of their commission, whereas now it should be 0.625% out of their commission.

You make a completely valid point. I will need to revisit this with my sales team, as they were originally paying the 12.5% out of their commission, whereas now it should be 0.625% out of their commission.


Not quite. 0.125 is the same as 12.5%. 0.0625 is 6.25%, not 0.625%

Reply


Terms & Conditions