Skip to main content

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 😞

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

Reply


Terms & Conditions