May 09, 2019 10:16 AM
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:
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:
Solved! Go to Solution.
May 09, 2019 11:50 AM
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
)
)
May 09, 2019 11:50 AM
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
)
)
May 09, 2019 12:57 PM
Oh my lanta. You are a life saver. Thank you SO very much.
May 10, 2019 03:08 PM
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,
May 10, 2019 04:44 PM
Thanks, @Justin_Barrett. Fixed it.
May 10, 2019 11:23 PM
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
May 14, 2019 11:09 AM
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.
May 14, 2019 07:05 PM
Not quite. 0.125 is the same as 12.5%. 0.0625 is 6.25%, not 0.625%