CODEWITHSUNDEEP
excelexcel-formula
10101
10101

Reputation: 2412

4 if statements in Excel formula

How to generate formula for 4 if statements? There is value to be tested in T26.

Here are conditions:

1 - 500 000             -> 500k$
500 000 - 1 000 000     -> 1M$
1 000 000 - 2 000 000   -> 2M$

Here is what has been achieved:

=IF(T26="";"";IF(T26>1;"500k$";IF(T26>=500000;"1M$";IF(T26>=1000000;"2M$";""))))

Upvotes: 0

Views: 76

Answers (1)

GSerg
GSerg

Reputation: 78190

The first true comparison wins, so in order to correctly work through a list of overlapping intervals you need to start with the one that does not overlap and work backwards:

=IF(T26>=1000000;"2M$";IF(T26>=500000;"1M$";IF(T26>=1;"500k$";"")))

If you don't want to be concerned with the order of intervals, you need to specify both lower and upper bounds each time:

=IF(T26="";"";IF(AND(T26>=1;T26<500000);"500k$";IF(AND(T26>=500000;T26<1000000);"1M$";IF(T26>=1000000;"2M$";""))))

Upvotes: 3

Related Questions