When >2000 but <3000 then add bonus

[administratiepl]

Hello all,

I am new at this and english is not my first language, but i'll try to explain.

I have a number of columns with numbers in it.

I want to make a formula that if the number is between 2000 en 3000 in that column then in the formula colomn it states 50 bonus and if the number is between 3000 en 4000 it states 100 bonus and when not met is says no bonus.

So:

Colomn 1          Formula field

1899               No Bonus

3999              100 bonus

2545               50 bonus

So far i have this formula:

IF({Jul} < 2000,"No bonus",

IF({Jul} >= 2000 <3000,"50 bonus",

IF({Jul} >= 3000 <4000,"100 bonus",

IF({Jul} >= 4000 <5000,"150 bonus"))))

but all this does that it returns No Bonus or 50 bonus in every cell...

What am i doing wrong?

Thank you in advance for helping me.

[kuovonne · Accepted solution]

Your conditions are not written correctly.

{Jul} >= 2000 <3000

You probably meant this to test if {Jul} is between 2000 and 3000, but that is not how the formula works. You cannot chain comparison operators this way. 

However, you are also in luck in that you do not actually need to test both conditions. You only need to test if the value is less than 3000, because you already know that the value must be above 2000 due to the previous If.

IF(
  {Jul} < 2000,
  "No bonus",
IF(
  {Jul} < 3000,
  "50 bonus",
IF(
  {Jul} < 4000,
  "100 bonus",
IF(
  {Jul} < 5000,
  "150 bonus"
))))

 You also might want to decide what the bonus should be if {Jul} is over 5000. Currently the formula will provide no value.

[administratiepl]

Yes that is the solution, thank you very much!