This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Other questions
- Formula not working

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

0
2526
3

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 02, 2018 05:19 AM

3 Replies 3

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 02, 2018 06:53 AM

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`

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 05, 2018 03:55 PM

This is what it looks

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 05, 2018 10:21 PM

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`

.)

Reply