Can I do a formula for one card?

[Brett_Lee]

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!

[ScottWorld]

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.”

[Brett_Lee]

That could work… But I do not know how to create an “override field”. Would you be able to show me the formula or explain how to do it, please?

[ScottWorld]

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
)

[Justin_Barrett · Accepted solution]

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.

[ScottWorld]

Justin_Barrett:

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.

Wow, NICE EYE, @Justin_Barrett!! Super awesome that you were able to reduce this to a single math formula. :slightly_smiling_face:

[Brett_Lee]

Justin_Barrett:

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

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!

[Justin_Barrett]

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?

[Justin_Barrett]

Something just occurred to me: when you need to override the core formula, do you want the manually-entered number (400, 600, etc) to also be multiplied with what is in the {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.

[Brett_Lee]

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?