Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

switch or nested IF for conditional commission calculation

Topic Labels: Formulas
Solved
Jump to Solution
334 8
cancel
Showing results for 
Search instead for 
Did you mean: 
HannahS
6 - Interface Innovator
6 - Interface Innovator

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. 
1 Solution

Accepted Solutions
Milan_Automable
6 - Interface Innovator
6 - Interface Innovator

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

See Solution in Thread

8 Replies 8
Milan_Automable
6 - Interface Innovator
6 - Interface Innovator

Hi @HannahS ,

Actually, the SWITCH formula seems to work for me:

SwitchDemo.gif

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
6 - Interface Innovator
6 - Interface Innovator

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? 

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
6 - Interface Innovator
6 - Interface Innovator

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

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.

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
4 - Data Explorer
4 - Data Explorer

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