If formula with greater than resulting in a text

Question

I am trying to do a formula where I have a row with numbers (=calculation field/column) and depending on the number a certain text value (Tier 0 / Tier 1 / Tier 2 / Tier 3) should be the result in my formula field

Tier 0 : 500,000 - Unlimited
Tier 1: 100,000 - 500,000
Tier 2: 40,000 - 100,000
Tier 3: 500 - 40,000

I tried several approaches but none of them worked, I always get an error message.

IF( {Calculation} < 40000, "Tier 3", IF( {Calculation} < 100000, "Tier 2", IF( {Calculation} < 500000, "Tier 1" , IF( {Calculation} < 100000000, "Tier 0"))))

OR

IF({Calculation} < 40000, “Tier 3”, IF({Calculation} < 100000, “Tier 2”, IF({Calculation} < 500000, “Tier 1”, “Tier 0”)))

OR

IF({Calculation} < 40000, “Tier 3”, IF({Calculation} > 40000, “Tier 2”, IF({Calculation} > 100000, “Tier 1”, IF({Calculation} > 500000, “Tier 0”, “n.a”))))

Any ideas what I did wrong?

Thanks 🙂

Best answer by Sho

The error may be due to a mixture of “ and ”.
Use " for all of them.

Either one.

IF(Calculation>=500000,"Tire 0",
  IF(Calculation>=100000,"Tire 1",
    IF(Calculation>=40000,"Tire 2",
      "Tire 3"
    )
  )
)

IF(Calculation<40000,"Tire 3",
  IF(Calculation<100000,"Tire 2",
    IF(Calculation<500000,"Tire 1",
      "Tire 0"
    )
  )
)

Sho · Accepted Answer

The error may be due to a mixture of “ and ”.
Use " for all of them.

Either one.

IF(Calculation>=500000,"Tire 0",
  IF(Calculation>=100000,"Tire 1",
    IF(Calculation>=40000,"Tire 2",
      "Tire 3"
    )
  )
)

IF(Calculation<40000,"Tire 3",
  IF(Calculation<100000,"Tire 2",
    IF(Calculation<500000,"Tire 1",
      "Tire 0"
    )
  )
)

Helenelene · Answer

thanks &#128578;

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded