-2

I am looking for a IF formula with 3 conditions. Request for help.

Conditions:

If Cell value is less than 10000 the value should be 0
If cell value is greater than 10000 and less than 15000, the value should be 150
if the cell value is greater than 15000, the value should be 200.

I am trying the below formula but for cell value less than 10000, The value is showing as "false"

=IF(cell>10000<15000,150,IF(cell>15000,200,0))
  • 2
    -1, no research effort – Dave Jun 11 '14 at 7:27
2

This will do what you want

=IF(A1<10000,0,IF(AND(A1>10000,A1<15000),150,IF(A1>15000,200)))

However, your logic is possibly (probably) flawed. What happens if the value is 10000 or 15000?

You don't ask for this to be catered for, so I left Excel to treat it as it wants to (by default will enter the word False for this)

0

Dave is right. If you want to deal with values 10k or 15k here is a slightly simpler formula where both boundary (not sure if this is the right word) values are always considered to be in higher interval

=IF(A1<10000,0,IF(A1<15000,150,200))

Therefore you have following intervals

(-infinity, 10000) - value 0
<10000, 15000) - value 150
<15000, infinity) - value 200

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.