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
- Formulas
- Can I do a formula for one card?

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
2728
10

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

Aug 20, 2020 05:01 PM

With my business, I have WAY too many deals that come across that are unique and individual from others. I am in DESPERATE need to find a way to create a formula, (or simply type in a number for that matter) and NOT have it affect ANY OTHER record. Just change the formula on that one particular record instead of every single one.

For example. Right now my formula to figure out commissions looks like this:

IF({PPW}=“4.35”,500, IF({PPW}=“4.25”,450,IF({PPW}=“4.15”,400, IF({PPW}=“4.05”, 350, IF({PPW}=“3.95”,300, IF({PPW}=“3.85”, 250,0)) ))))*kW

This morning we sold a deal at $4.79. So none of the formula triggers did anything and they didn’t calculate. However, I want to pay him $600 at that price. So if I typed in $600, it would change that cell for every single record to $600 and completely ruin all our accounting.

This happens multiple times a week, and I’m fear I may have to go back to an excel spreadsheet where I can do individual cells like that. I REALLY don’t want to go back to excel because I love Airtable! However, this is a must and I really hope there is a way this can be done. I’m not the best with formulas so any help would be appreciated!

Any help would greatly be appreciated!

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

Aug 20, 2020 10:40 PM

On a side note, your core formula can be simplified using the `SWITCH()`

function. `SWITCH()`

is ideal when you need to test one thing for one of several possible values, with specific output for each value. The basic structure is:

```
SWITCH(
THING_TO_TEST,
Option1, Output1,
Option2, Output2,
...
Optional_Default_Output
)
```

Here’s how it would work in your situation, using the override setup that @ScottWorld mentioned. (On a side note, notice that a field name without spaces doesn’t require curly braces around it):

```
IF(
{Custom Amount},
{Custom Amount},
SWITCH(
PPW,
"4.35", 500,
"4.25", 450,
"4.15", 400,
"4.05", 350,
"3.95", 300,
"3.85", 250,
0
) * kW
)
```

To simplify this even further, I noticed a pattern in the values you’re checking for compared to those that are output. The trigger values are in intervals of one tenth, while the output values are intervals of 50. Based on that, you can use a little math do derive the final value directly from the selected value in the `{PPW}`

field, including defaulting to 0 if it’s not in your list of options. Here’s the formula with that final tweak:

```
IF(
{Custom Amount},
{Custom Amount},
(200 + ((VALUE(PPW) - 3.75) * 500)) * (VALUE(PPW) >= 3.85) * kW
)
```

This shows both versions outputting the same thing. `{Value}`

is the version using `SWITCH()`

, and `{Value 2}`

is the one that uses math. I wasn’t sure what to do for the `{kW}`

field, so I just left it at 1 so you could see the raw output.

Reply

10 Replies 10

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

Aug 20, 2020 05:45 PM

Just create an “override field“ where you type in a custom value, and use that value as the first part of your formula field.

In other words, “If custom value exists, use the custom value. Otherwise, here’s the rest of the formula.”

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

Aug 20, 2020 06:14 PM

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

Aug 20, 2020 07:21 PM

Just create a new field that is a number field. Name it whatever you want such as “Custom Amount”. Then, add it onto the beginning of your formula like this:

`IF(`

`{Custom Amount},{Custom Amount},`

`IF({PPW}="4.35",500, IF({PPW}="4.25",450,IF({PPW}="4.15",400, IF({PPW}="4.05", 350,`

`IF({PPW}="3.95",300, IF({PPW}="3.85", 250,0)) ))))*kW`

`)`

Solved
See Solution in Thread

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

Aug 20, 2020 10:40 PM

On a side note, your core formula can be simplified using the `SWITCH()`

function. `SWITCH()`

is ideal when you need to test one thing for one of several possible values, with specific output for each value. The basic structure is:

```
SWITCH(
THING_TO_TEST,
Option1, Output1,
Option2, Output2,
...
Optional_Default_Output
)
```

Here’s how it would work in your situation, using the override setup that @ScottWorld mentioned. (On a side note, notice that a field name without spaces doesn’t require curly braces around it):

```
IF(
{Custom Amount},
{Custom Amount},
SWITCH(
PPW,
"4.35", 500,
"4.25", 450,
"4.15", 400,
"4.05", 350,
"3.95", 300,
"3.85", 250,
0
) * kW
)
```

To simplify this even further, I noticed a pattern in the values you’re checking for compared to those that are output. The trigger values are in intervals of one tenth, while the output values are intervals of 50. Based on that, you can use a little math do derive the final value directly from the selected value in the `{PPW}`

field, including defaulting to 0 if it’s not in your list of options. Here’s the formula with that final tweak:

```
IF(
{Custom Amount},
{Custom Amount},
(200 + ((VALUE(PPW) - 3.75) * 500)) * (VALUE(PPW) >= 3.85) * kW
)
```

This shows both versions outputting the same thing. `{Value}`

is the version using `SWITCH()`

, and `{Value 2}`

is the one that uses math. I wasn’t sure what to do for the `{kW}`

field, so I just left it at 1 so you could see the raw output.

Reply

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

Aug 20, 2020 11:02 PM

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

Aug 21, 2020 08:34 AM

You guys are so incredibly smart! I’m just learning how to do formulas, and pretty much brand new to Airtable. Everything I’ve done thus far is VERY basic. So forgive me for asking a dumb question, but if I just copy paste your formula:

IF(

{Custom Amount},

{Custom Amount},

(200 + ((VALUE(PPW) - 3.75) * 500)) * (VALUE(PPW) >= 3.85) * kW

)

What am I inputting for the {Custom Amount} When I try to write down the custom commission, I get an error. And why are there two {Custom Amount}? Here’s the image of the error I get

When I do IF(

{4.79},

{400},

SWITCH(

PPW,

“4.35”, 500,

“4.25”, 450,

“4.15”, 400,

“4.05”, 350,

“3.95”, 300,

“3.85”, 250,

0

) * kW

)

This is the error I get as well

I’m not even going to try this formula because I don’t understand it. Lol

IF(

{Custom Amount},

{Custom Amount},

(200 + ((VALUE(PPW) - 3.75) * 500)) * (VALUE(PPW) >= 3.85) * kW

)

Again, I’m sure it’s simple for you, but I don’t really have much knowledge on formulas. Thank you again for all of your help!

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

Aug 21, 2020 08:45 AM

The formula that I wrote should be left untouched when pasting it into the formula field. Per the comment from @ScottWorld earlier, `{Custom Amount}`

is a reference to a new number field that you need to add. Look at the screenshot I shared. Whenever you want to override the programmed values internally, you put the total value that you want into that `{Custom Amount}`

field. I manually entered “600” into that field on the last sample record, and that overrides the formula’s main calculation.

The start of the formula can be read like this: *IF the {Custom Amount} field isn’t empty, use the value in {Custom Amount}. Otherwise run this formula.*

Does that make it more clear?

Reply

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

Aug 21, 2020 08:50 AM

`{kw}`

field? If so, then I’ll need to change the formula a bit, but I’m heading out for a few hours and won’t have time to work it over until later.

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

Aug 21, 2020 12:49 PM

Thanks for the clarification. You are a lifesaver! I’m beginning to understand. Yes, I would like the manually entered number (400, 600) to also be multiplied with what is in the {kw} field.

So, to be clear, the formula you’ll be sending (again, thank you) I should just copy paste it into the commission field where I want the formula to run. THEN, I need to create a new number field. Do I label that field Custom Amount so the formula points to that new number field?