Help

Re: When >2000 but <3000 then add bonus

Solved
Jump to Solution
837 1
cancel
Showing results for 
Search instead for 
Did you mean: 
administratiepl
4 - Data Explorer
4 - Data Explorer

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.
1 Solution

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

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.

See Solution in Thread

2 Replies 2
kuovonne
18 - Pluto
18 - Pluto

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.

Yes that is the solution, thank you very much!