IF/WHEN for when a cell falls between two numbers

[philo351]

I'm trying to use a multiple "multiple" IF/WHEN formula for when a number falls between two numbers. For example, if a number falls anywhere between 1000 and 1999, then I need result A. If the number falls between 2000 and 2999, then I need result B. For anything falling between 3000-3999, I need result B ...etc.

Currently I'm hoping to apply this to a multiple IF/WHEN forumula which is as follows
<blockquote>
=IF((B2>1000),A,IF((B2>2000),B,IF((B2>3000),C,IF((B2>4000),D,""))))</blockquote>now obviously this isn't going to work. I need each condition to apply to a range between two numbers which would operate this way<blockquote>=IF((<font color="red">B2 is between 1000 and 2999</font>),A,IF((<font color="red">B2is between 2000 and 2999</font>),B,IF((<font color="red">B2is between 3000 and 3999</font>),C,IF((<font color="red">B2 is between 4000 and 4999</font>),D,""))))</blockquote>

any ideas? do I need to write a VBA script for this?

 

[quansei]

You could possibly use "and" in the "if" formula

i.e.

=IF(AND(E24>1000,E24<2000),"value if true","value if not")

Then keep nesting this. Although this will be limited to the number of conditions you may want.

Cheers,

 

[Scott Huish]

Perhaps:

=LOOKUP(B2,{-9.99999999999999E+307,"";1000,"A";2000,"B";3000,"C";4000,"D";5000,""})

 

[Krishnakumar]

Hi philo351,

Welcome to board!

Try,

=LOOKUP(B2,{0,0;1000,"A";2000,"B";3000,"C";4000,"D";5000,""})

HTH

 

[philo351]

that was exactly what I needed. Thanks for the replies! You all really pulled me out of a bind here.

 

[juliehmac]

Hi, I know this question was answered a while ago, but I am having trouble with the same type of thing. I have the three statements below that work, but when I try and combine them it just returns #VALUE!. Can someone show me the correct way to combine them? Thank you!

=IF(AND(C5>=0,C5<5),5)

=IF(AND(C8>=5,C8<10),6.67)

=IF(C2>=10,8.355)

=IF(AND(C2>=0,C2<5),5), IF(AND(C2>=5,C2<10),6.67), IF(C2>10,8.355)

 

[blaow]

=IF(AND(C2>=0,C2<5),5, IF(AND(C2>=5,C2<10),6.67, IF(C2>10,8.355)))

Try that out.

 

[cyglor]

I am sure there is an easy way of doing this, but I can't figure it out. I have a VERY long list of values in column A that follow the general pattern 1950.9 , 1950.8 , 1950.7, ...1950.0, 1949.9, 1949.8...etc. The decimal part of the number is variable. I would like to have a column that has only the decimal part. Is there a way to do this with an If statement?
For example, If A2 is between 1949 and 1950, subtract 1949, if not then subtract 1948 (or 1950, which ever the whole number happens to be).

 

[Aladin Akyurek]

I am sure there is an easy way of doing this, but I can't figure it out. I have a VERY long list of values in column A that follow the general pattern 1950.9 , 1950.8 , 1950.7, ...1950.0, 1949.9, 1949.8...etc. The decimal part of the number is variable. I would like to have a column that has only the decimal part. Is there a way to do this with an If statement?
For example, If A2 is between 1949 and 1950, subtract 1949, if not then subtract 1948 (or 1950, which ever the whole number happens to be).

=A2-TRUNC(A2)

would return the decimal part of A2. Is this what you are after?

 

[cyglor]

Yes. That is what I needed. Thank you.