Commission Calculation Formula

[HannahS]

Hi,

I'm trying to create a table to track income and the records feed from different tables using automations which are assigned a deal type as they're created. If they're type A the commission is 15% of the payment amount, if they're type B the commission is 10% of the payment amount and if they're type C then the commission field needs to return 0. I don't know if it makes a difference, but the payment amount field is a formula. 

I've tried 

SWITCH({Type of deal},

"A", {Payment amount} * 0.15,

"B", {Payment amount} * 0.10,

"C", 0

)

But I just keep getting ERROR in the results.

I tried looking here at nested IF formula and came up with:

IF(

AND({type of deal} = ”A”, {Payment amount} *0.15,))

IF(

AND({type of deal} = “B”, {Payment amount} *0.10,))

IF(

AND({type of deal} = “C”, {Payment amount} *0))

But there's clearly something bigger wrong there that I can't see because it says it's not a valid formula.

Any suggestions would be hugely appreciated, thank you. 

[Milan_Automable]

Hi @HannahS ,

Actually, the SWITCH formula seems to work for me:

One thing I notice is that you use type of deal in the IF and Type of deal (big T) in the SWITCH formula, maybe this is your issue? 🙂

I'd love to explore more use-cases of Airtable and automation specific to your business, if you're interested feel free to book a free call at any time that works for you!

Best, Milan - Automable.AI

[HannahS]

That's so weird, it's definitely not working for me and it's not to do with the Type/type issue (that was just me typing in here and getting it wrong). Is it possibly because my payment amount field is a formula pulling that figure from somewhere else so it's not a number field? 

[Mike_AutomaticN]

Hey @HannahS!
If you'd like to move forward with the nested IF() formula, you should check the one shared below. You'll easily see the differences with the formula you shared on your post!

IF({Type of deal} = "A", {Payment amount} * 0.15,
  IF({Type of deal} = "B", {Payment amount} * 0.10,
    IF({Type of deal} = "C", 0)
  )
)

Mike, Consultant @ Automatic Nation

[Milan_Automable · Accepted solution]

Maybe it's a string with the digits of a number? If it's a number formula output, it should still work.

If it is a string datatype, you can convert it to a number by adding 0 to it (+0). So the below might work:

SWITCH({Type of deal},

  "A", ({Payment Amount} + 0) * 0.15,

  "B", ({Payment Amount} + 0) * 0.10,

  "C", 0

)

-- Best, Milan - Automable.AI Free Airtable consultation

[kuovonne]

Since the solution is marked for converting the string into a number, I just want to add that you may want to look into why the {Payment Amount} is a text string instead of a number. There may be a good reason for the {Payment Amount} to be a text string, but usually it is best if you make these data type changes at the source.

[HannahS]

Thanks, but I can't see a way to do that - because these records are created by automation from a couple of different tables and I need the record names to be fairly descriptive (so 'Payment stage' 'project' 'amount') and then I extract the amount from that to create the payment amount...I can't see a way to pull the amount through from multiple different places and stages (particularly when some projects will have multiple payment stages) without duplicating ... I don't know that that will make a lot of sense without being able to see the whole set up.

[HannahS]

Although, now you've said that, I've gone back to the formula with the extraction and added 0 there and now I don't need do do it in each individual calculation so thank you for prompting me to think about that!

[Marvel]

Your first formula is almost correct! The issue is likely with the {Payment amount} field being a formula. Sometimes, Airtable formulas don’t recognize numbers correctly if the field is formatted as text.

Try this version instead:

SWITCH( {Type of deal}, "A", VALUE({Payment amount}) * 0.15, "B", VALUE({Payment amount}) * 0.10, "C", 0 The VALUE() function ensures Airtable reads numbers correctly. If this doesn’t work, check that {Payment amount} is actually returning a number and not text.

If you're still stuck, let me know, and I'll help further! 🚀