Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

IF or SWITCH Function for multi-range values

3580 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