Help

Formula Writing Help Needed - IFS

Topic Labels: Formulas
1409 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Matthew_Barton
4 - Data Explorer
4 - Data Explorer

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

2 Replies 2

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
4 - Data Explorer
4 - Data Explorer

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
))))))))))