# When >2000 but <3000 then add bonus

Topic Labels: Data Formulas
Solved
175 2
cancel
Showing results for
Did you mean:  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  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.

2 Replies 2  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.  4 - Data Explorer

Yes that is the solution, thank you very much! 