Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here
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.
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.
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.”
Aug 20, 2020 06:14 PM
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?
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
)
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.
Aug 20, 2020 11:02 PM
Wow, NICE EYE, @Justin_Barrett!! Super awesome that you were able to reduce this to a single math formula. :slightly_smiling_face:
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!
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?
Aug 21, 2020 08:50 AM
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.
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?
Aug 21, 2020 01:09 PM
Make the new {Custom Amount}
number field first, then paste the new formula below into your commission field. If the {Custom Amount}
field doesn’t exist when you save the updated formula, Airtable will throw an error saying it can’t find the field.
IF(
OR({Custom Amount}, PPW),
IF(
{Custom Amount},
{Custom Amount},
(200 + ((VALUE(PPW) - 3.75) * 500)) * (VALUE(PPW) >= 3.85)
) * kW,
0
)