Skip to main content
Solved

switch or nested IF for conditional commission calculation

H

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. 

Best answer by Milan_Automable

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
View original
Did this topic help you find an answer to your question?

8 replies

Milan_Automable
Forum|alt.badge.img+3
  • Milan_Automable
  • Participating Frequently
  • 35 replies
  • February 25, 2025

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

H
  • HannahS
  • Author
  • Known Participant
  • 18 replies
  • February 25, 2025

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

H
1 person likes this
  • H
    HannahS
Milan_Automable
Forum|alt.badge.img+3
  • Milan_Automable
  • Participating Frequently
  • 35 replies
  • Answer
  • February 25, 2025

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
H
1 person likes this
  • H
    HannahS
kuovonne
Forum|alt.badge.img+17
  • kuovonne
  • Brainy
  • 5987 replies
  • February 25, 2025

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.

Kenneth_Raghuna
H
2 people like this
  • Kenneth_Raghuna
    Kenneth_Raghuna
  • H
    HannahS
H
  • HannahS
  • Author
  • Known Participant
  • 18 replies
  • February 25, 2025
kuovonne wrote:

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.


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.

H
  • HannahS
  • Author
  • Known Participant
  • 18 replies
  • February 25, 2025
kuovonne wrote:

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.


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!

M
  • Marvel
  • Participating Frequently
  • 11 replies
  • February 25, 2025

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! 🚀

H
1 person likes this
  • H
    HannahS

Reply


Most helpful members this week

Terms & Conditions