+5I have a Sales database. Sale amount as a currency field is in one column. Commission Rate as a percent is in another column. I need a third column, Commission Due. I am getting a formula error with the formula: {Commission Rate}*{Sale Amount} I am not sure what’s wrong.
I had the Commission Rate as a 6%, then I even changed it to a number field so 6% would be entered as .06 and I’m still getting the error.
A couple of questions:
{Commission Rate} and {Sale Amount} entered values, or is one or both carried from another table as a lookup or rollup field?+5A couple of questions:
{Commission Rate} and {Sale Amount} entered values, or is one or both carried from another table as a lookup or rollup field?Here’s a screenshot of the field error and field formats. Sale Amount is an entered value. Commission Rate is linked and selected from the Commission table. The error is the same if the commission rate is a decimal or if I change it to a percent. We would rather see it as a percent.
Here’s a screenshot of the field error and field formats. Sale Amount is an entered value. Commission Rate is linked and selected from the Commission table. The error is the same if the commission rate is a decimal or if I change it to a percent. We would rather see it as a percent.
Ah.
Links evaluate as text strings (you can tell, because they are left-aligned; numeric values are right-aligned). You need
VALUE({Commission Rate})*{Sale Amount}
Interestingly, thanks to an Airtable bug, if the primary field in the [Commission] table was configured as a percent field, and you used it directly in a formula (e.g., {Primary Field}*100), thanks to an Airtable bug your result would be off by a factor of 100. However, if the primary field is a percent, but you access it as a linked value (as you do here), when you declare VALUE('5%') (as you would by applying the VALUE() formula to a link named '5%'), it correctly evaluates as 0.05. So use '6%' or '0.06' in your commission table; either will work.
+5Ah.
Links evaluate as text strings (you can tell, because they are left-aligned; numeric values are right-aligned). You need
VALUE({Commission Rate})*{Sale Amount}
Interestingly, thanks to an Airtable bug, if the primary field in the [Commission] table was configured as a percent field, and you used it directly in a formula (e.g., {Primary Field}*100), thanks to an Airtable bug your result would be off by a factor of 100. However, if the primary field is a percent, but you access it as a linked value (as you do here), when you declare VALUE('5%') (as you would by applying the VALUE() formula to a link named '5%'), it correctly evaluates as 0.05. So use '6%' or '0.06' in your commission table; either will work.
Thanks for the help! That solved it perfectly.
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
