May 23, 2019 01:01 PM
I am having a hard time with nested if/and statements. I am working out a commission structure for retail store employees who sell spots on trips, and they get more money the more full the trip is:
$25 for any deposit for any trip that is not yet a “go”
{TTLSignups}<{GO}= $25
$50 for a trip that has met the minimum “go” number
{TTLSignups}={GO}= $50
$100 for a domestic trip that goes full
{TTLSignups}={MaxPax} AND {Region}=“North America” =$100
$150 for an international trip that goes full
{TTLSignups}={MaxPax} AND {Region}!=“North America” =$150
Here is the formula that works, but I want it to also show that if the trip is full and the {Region} is anything other than “North America”, it should return 150.
IF({TTLSignups}={MaxPax}, 100, IF({TTLSignups}>={GO}, 50, 25))
Here is the formula that DOESNT work:
IF(TourStatus=“GO”,50,
IF(TourStatus=“Needs More”, 25,
IF(Region=“North America”,100,150)))
Any help will be appreciated!
May 24, 2019 07:27 PM
I think this adds the region check you want:
IF(
{TTLSignups}={MaxPax},
IF(
{Region}="North America",
100,
150
),
IF(
{TTLSignups} >= {GO},
50,
25
)
)
May 25, 2019 10:11 AM
You nailed it, thank you!!