Calculating Pay based on Work Type

[Hunter_Reynolds]

Good afternoon everyone. I’m trying to create a field that calculates the appropriate pay given the work type. In my case, there are 3 different work types, (1. Booked gig, Not booked gig, and Custom Rate).

In case 1 where it’s a booked gig, the base pay is $125. If a team member surpasses $125 in sales, then they’ll receive a 20% commission on any sales after. (example, $500 revenue is generated, $500-125=$375*.2=$75+125=$200)

In case 2, there is no base pay, but team-member makes 40% commission on all sales made. (example, $500 revenue generated, $500*.4=$200).

In case 3, a custom rate is set and field is populated by custom rate given.

I’ve been using the following formula, but my issue is that it’s still calculating the commission even if the commission benchmark is not reached for case 1.

SWITCH({Work Type},“WAE - Club Booked”,(((Revenue-125).2)+125),“WAE - Club Not Booked”,(Revenue.4),{Custom Rate})

I tried creating the following formula to correct for the situation above, essentially creating a nest of IF statements, that account for each scenario.

IF({Work Type},“WAE - Club Booked”,IF(Revenue>125,(((Revenue-125).2)+125),125),IF({Work Type},“WAE - Club Not Booked”,Revenue.4,{Custom Rate}))

When I submit the formula above, Airtable changes the formula to

IF({Work Type},“WAE - Club Booked”,IF(Revenue>125,(((Revenue-125)*.2)+125),125))

Is this a bug? Why is it reformatting my formula? Any help is greatly appreciated. I’d be happy to answer any questions!

[augmented]

Hi Hunter. Give this a try…I’ve never seen Airtable change a formula like that. It should just give an error.

IF({Work Type},
     IF({Work Type}='WAE - Club Booked',
         IF(Revenue>125,(((Revenue-125) * 0.2)+125),125),
         IF({Work Type}='WAE - Club Not Booked', Revenue * 0.4,
         {Custom Rate}))
)

[Kamille_Parks · Accepted solution]

Hunter_Reynolds:

Is this a bug? Why is it reformatting my formula?

Its reformatting because your parenthesis are mismatched; Airtable is trying to interpret what you gave it by removing a piece that doesn’t work.

Your original formula is very close. If you add MAX() you can apply the commission to only the portion that exceeds 125.

SWITCH(
  {Work Type},
  "WAE - Club Booked", (MAX({Revenue} - 125, 0)*.2) + 125,
  "WAE - Club Not Booked", ({Revenue}*.4),
  {Custom Rate}
)

[Hunter_Reynolds]

Thanks for the help!

[Hunter_Reynolds]

I did not know about the MAX function! Thanks, that worked!