# Calculating Pay based on Work Type

Topic Labels: Formulas
Solved
1962 4
cancel
Showing results for
Did you mean:
6 - Interface Innovator

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!

1 Solution

Accepted Solutions
16 - Uranus

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}
)
``````
4 Replies 4
10 - Mercury

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}))
)``````
16 - Uranus

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}
)
``````
6 - Interface Innovator

Thanks for the help!

6 - Interface Innovator

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