Help

IF or SWITCH Function for multi-range values

2101 1
cancel
Showing results for 
Search instead for 
Did you mean: 
mattnorris
4 - Data Explorer
4 - Data Explorer

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) 

Screenshot 2022-12-10 at 09.45.38.png

I'd really appreciate some help here! Feeling lost 😞

1 Reply 1
Marko_K
5 - Automation Enthusiast
5 - Automation Enthusiast

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