Formula Writing Help Needed - IFS

[Matthew_Barton]

I’m currently migrating from using google sheets to Airtable, I have a majority of my formulas working, however, I need assistance with this:

=IFS(A2>1000001,A254%,A2>=400001,A251%,A2>=200001,A247%,A2>=100001,A242%,A2>=80001,A237%,A2>=60001,A234%,A2>=40001,A231%,A2>=20001,A227%,A2>=10001,A224%,A2>=1,A221%)

The formula looks up a value and depending on the amount it then calculates a value based upon a percentage.

I look forward to any assistance I can get

[kuovonne]

It looks like you are mixing the syntax of an IF with a SWITCH.
When dealing with ranges of values, you need to nest multiple IF statements. Here is an example. The numbers don’t exactly match yours, but you should get the general pattern.

IF(A2 > 1000,
  A2 * 0.54,
  IF(A2 >= 400,
    A2 * 0.51,
    IF(A2 >= 200,
      A2 * 0.47
)))

[Matthew_Barton]

Thanks for the feedback and guess what? It works, this is the formula in effect:

IF({Deal : Automated Value} > 1000001,
{Deal : Automated Value} * 0.54,
IF({Deal : Automated Value} > 400001,
{Deal : Automated Value} * 0.51,
IF({Deal : Automated Value} > 200001,
{Deal : Automated Value} * 0.47,
IF({Deal : Automated Value} > 100001,
{Deal : Automated Value} * 0.42,
IF({Deal : Automated Value} > 80001,
{Deal : Automated Value} * 0.37,
IF({Deal : Automated Value} > 60001,
{Deal : Automated Value} * 0.34,
IF({Deal : Automated Value} > 40001,
{Deal : Automated Value} * 0.31,
IF({Deal : Automated Value} > 20001,
{Deal : Automated Value} * 0.27,
IF({Deal : Automated Value} > 10001,
{Deal : Automated Value} * 0.24,
IF({Deal : Automated Value} > 1,
{Deal : Automated Value} * 0.21
))))))))))