# Formula not working

2526 3
cancel
Showing results for
Did you mean:
4 - Data Explorer

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?

3 Replies 3
13 - Mars

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`

4 - Data Explorer

This is what it looks

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`.)