Formula not working

[Matt_Gibson]

Hello, I’m trying to figure out what I thought was a simple formula but it’s not working no matter what I do. I’m trying to get airtable multiple a $ amount by % field to come up with the after tax price. I currently have a Table called Member Fee which is a Dollar amount price before tax. that table has 2 fields the main one being Member Fee, the second is simple the name of the program that has that price. I have another able for GST/HST (which is what the taxes are called). In that table I Also have 2 field the Tax percentage which is in a value of 1.000 because we have the province of QC who’s tax in %14.975. In my main table the 3 fields I’m trying to calculate are called Member fee which is linked to the member fee table, GST/HST which is linked to the GST/HST table and Total Fee. The total fee field format is set to Currency with a precision of $1.00 and the formula I’m using is VALUE({Member Fee})*{GST/HST} however this does not seem to be working. Can someone please help me?

[Elias_Gomez_Sai]

What error are you getting? Both Fields contains numbers? The only thing you must have in mind is that a Percentage field is set relating to 1, not to 100, so you have to multiply the result by 100.

Formula: A/B*100

[Matt_Gibson]

This is what it looks

[W_Vann_Hall]

You’re halfway there — but your problem also reveals an odd ‘gotcha’ in the way Airtable works.

First, the solution. This formula should work:

VALUE({Member Fee})*VALUE({GST/HST})

The oddity is that VALUE({GST/HST}) equals 0.13. What’s so strange about that? you ask. Well, if you go to the [GST/HST] table, you’ll find the value of the primary field for the record named 13% is 13. That is to say, a percent field with the value of 13%, used in a formula, has a numeric value of 13. This is an Airtable bug of long standing that essentially has been grandfathered into the product because of the number of formulas it would break if corrected. However, something I hadn’t previously realized is that calculating the value of the string '13%' returns the correct numeric value of 0.13. In your case, when you refer to a linked-record whose primary field is a percent field, the reference is automatically converted to a string — which is why you were receiving #ERROR! responses. Since it’s a string, though, the VALUE() function behaves as it should, returning the correct value.

The reason I’m going through all this is that if you perform calculations in the [GST/HST] table, you’ll need to treat that 13% value differently than you do here. One possible method would be to wrap it as follows:

VALUE({GST/HST}&'%')

(Actually, that seems to be a safe wrapper for any percentage; that is, if I append '%' to the string '13%' I get '13%%', and VALUE('13%%') returns 0.13.)