Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 10, 2022 04:16 AM
I am building a savings calculator, and I'm using Airtable to store a Rate Table, and also to do the formula based calculations needed for the project.
Here's what I want to do: Have a formula that takes value x and checks if it is in a range of a and b in a specific row. Once it identifies what row, and therefore what range it is in, it needs to show/equal value y.
Here is what I have so far: (But it is giving me an error)
SWITCH(
{Inputed debit swipe},
>= 10000, <= 20000, 2.75/100
>= 20001, <= 30000, 2.75/100
>= 30001, <= 40000, 2.75/100
>= 40001, <= 50000, 2.75/100
>= 50001, <= 60000, 2.75/100
>= 60001, <= 70000, 2.75/100
>= 70001, <= 80000, 2.75/100
>= 80001, <= 90000, 2.75/100
>= 90001, <= 150000, 2.75/100
>= 150001, <= 250000, 2.75/100
>= 250001, <= 500000, 2.75/100
>= 500001, <= 750000, 2.75/100
>= 750001, <= 1000000, 2.75/100
""
)
See the screenshot below for how my current table looks: (The first 3 columns are the ones to use in this formula)
I'd really appreciate some help here! Feeling lost 😞
Dec 10, 2022 11:09 AM
While switch statements are elegant, it can get overwhelming trying to cram all that logic inside there. Might be easier to use IF's & AND's for this example. See below. I went up to 40K, but you get the idea; just keep expanding it. If a value is < 10001 or >40000 (in my example), the function returns a default value ("Default / Out of range value").
IF(
AND({Inputed debit swipe} >= 10001, {Inputed debit swipe} <= 20000),
2.75,
IF(
AND({Inputed debit swipe} >= 20001, {Inputed debit swipe} <= 30000),
2.5,
IF(
AND({Inputed debit swipe} >= 30001, {Inputed debit swipe} <= 40000),
2.3,
"Default / Out of range value"
)
)
)
Best,
Zemanir Airtable Consulting: www.zemanir.com