Skip to main content

Formula Writing Help Needed - IFS

  • March 21, 2020
  • 2 replies
  • 71 views
M

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

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • March 21, 2020

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
)))
M
  • Matthew_Barton
  • Author
  • New Participant
  • March 23, 2020

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

    Terms & ConditionsAccessibility statement