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
- Formulas
- Help with a formula not allowing me to format as n...

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

Topic Labels:
Formulas

Solved

Jump to Solution

0
2215
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

May 14, 2020 10:53 AM

I have rate fields low, mid, high.

The rate fields are lookups - formatted as decimals.

Then I have a formula field

IF(rate = “low”, {low rate}, IF(rate = “mid”, {mid rate}, IF(rate=“high”, {high rate},"")))

which results in the correct number, but the formula will not allow me to format as a number.

What am I overlooking?

Thanks!

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

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

May 14, 2020 11:30 AM

For your final “”, swap it out for `BLANK()`

instead.

So your formula would look like this:

`IF(rate = “low”, {low rate}, IF(rate = “mid”, {mid rate}, IF(rate=“high”, {high rate},BLANK())))`

The final “” that you originally had in your formula yielded a “string” result (i.e. a “text” result). If Airtable’s formula engine sees that your formula can have any other results BESIDES a number, then it won’t let you format your formula as a number.

It would actually be really cool if Airtable’s formula engine was smart enough to see multiple different results within your formula — and then let you set a bunch of different types of formatting for the formula field (based on the different types of results that your formula might yield).

But since it doesn’t do that, you would need to make sure that all of your results end up a number.

Luckily, the BLANK() function doesn’t actually result in a string/text (according to Airtable’s formula engine), so then you can apply number formatting – if all of your other formula results end in a number.

Reply

3 Replies 3

Solved
See Solution in Thread

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

May 14, 2020 11:30 AM

For your final “”, swap it out for `BLANK()`

instead.

So your formula would look like this:

`IF(rate = “low”, {low rate}, IF(rate = “mid”, {mid rate}, IF(rate=“high”, {high rate},BLANK())))`

The final “” that you originally had in your formula yielded a “string” result (i.e. a “text” result). If Airtable’s formula engine sees that your formula can have any other results BESIDES a number, then it won’t let you format your formula as a number.

It would actually be really cool if Airtable’s formula engine was smart enough to see multiple different results within your formula — and then let you set a bunch of different types of formatting for the formula field (based on the different types of results that your formula might yield).

But since it doesn’t do that, you would need to make sure that all of your results end up a number.

Luckily, the BLANK() function doesn’t actually result in a string/text (according to Airtable’s formula engine), so then you can apply number formatting – if all of your other formula results end in a number.

Reply

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

May 14, 2020 11:57 AM

Or just leave out the blank (and make sure you use straight quotes instead of curly quotes).

```
IF(rate = "low", {low rate}, IF(rate = "mid", {mid rate}, IF(rate="high", {high rate})))
```

Or use a switch statement:

```
SWITCH(rate,
"low", {low rate},
"mid", {mid rate},
"high", {high rate}
)
```

Reply

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

May 14, 2020 12:15 PM

Thank you both! I appreciate such a quick solution

Reply