Skip to main content
Solved

Nested IF AND statement with two different calculatoins

K

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.

Best answer by Jeremy_Oglesby

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
   )
)
View original
Did this topic help you find an answer to your question?

7 replies

J

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
   )
)
K
1 person likes this
  • K
    Kristen_Gerzin
K
Jeremy_Oglesby wrote:

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.

J
J
2 people like this
  • J
    JPT
  • J
    Jeremy_Oglesby
J

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,
J
1 person likes this
  • J
    Jeremy_Oglesby
J
Justin_Barrett wrote:

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.

JonathanBowen

Of course,

(Profit / 2) * 0.125

is the same as

Profit * 0.0625

which removes the need for the formula :slightly_smiling_face:

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

JB

K
J
2 people like this
  • K
    Kristen_Gerzin
  • J
    Justin_Barrett
K

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.

J
Kristen_Gerzin wrote:

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


Most helpful members this week

Terms & Conditions