IF/WHEN forumla fo when a cell is between two numbers
 

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


=IF((B21000),A,IF((B22000),B,IF((B23000),C,IF(( B24000),D,""))))

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:

=IF((B2 is between 1000 and 2999),A,IF((B2is between 2000 and
2999),B,IF((B2is between 3000 and 3999),C,IF((B2 is between 4000 and
4999),D,""))))

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


--
philo351
------------------------------------------------------------------------
philo351's Profile: http://www.excelforum.com/member.php...o&userid=30295
View this thread: http://www.excelforum.com/showthread...hreadid=499624

Answer: IF/WHEN forumla fo when a cell is between two numbers
 

You can use the AND function along with the IF function to achieve this. Here's an example formula that you can use:

1. =IF(AND(B2=1000,B2<=1999),"Result A",
2. IF(AND(B2=2000,B2<=2999),"Result B",
3. IF(AND(B2=3000,B2<=3999),"Result C",
4. IF(AND(B2=4000,B2<=4999),"Result D",""))))[/*]

This formula checks if the value in cell B2 falls between two numbers using the AND function. If the condition is true, it returns the corresponding result. If not, it moves on to the next condition until it finds a match.

You don't need to write a VBA script for this, as it can be achieved using a simple formula.

IF/WHEN forumla fo when a cell is between two numbers
 

Hi philo

This one should do it for you
=IF(AND(B11000,B1<1999),"A",IF(AND(B11999,B1<299 9),"B",IF(AND(B12999,B1<3999),"C",IF(AND(B13999, B1<4999),"D"))))

Michael

"philo351" wrote:


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


=IF((B21000),A,IF((B22000),B,IF((B23000),C,IF(( B24000),D,""))))

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:

=IF((B2 is between 1000 and 2999),A,IF((B2is between 2000 and
2999),B,IF((B2is between 3000 and 3999),C,IF((B2 is between 4000 and
4999),D,""))))

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


--
philo351
------------------------------------------------------------------------
philo351's Profile: http://www.excelforum.com/member.php...o&userid=30295
View this thread: http://www.excelforum.com/showthread...hreadid=499624

IF/WHEN forumla fo when a cell is between two numbers
 

On Mon, 9 Jan 2006 21:22:51 -0600, philo351
wrote:


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


=IF((B21000),A,IF((B22000),B,IF((B23000),C,IF( (B24000),D,""))))

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:

=IF((B2 is between 1000 and 2999),A,IF((B2is between 2000 and
2999),B,IF((B2is between 3000 and 3999),C,IF((B2 is between 4000 and
4999),D,""))))

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

One way is to just reverse your scrip:

=IF(B2=4000,"D",IF(B2=3000,"C",IF(B2=2000,"B",I F(B2=1000,"A",""))))

Another method, which is more flexible, is to use a lookup table. Then you
can, if necessary, expand the formula to include many levels of conditions.

=IF(B2=1000,VLOOKUP(B2,H1:I4,2),"")

Where H1:I4 contains:

1000 A
2000 B
3000 C
4000 D



--ron

IF/WHEN forumla fo when a cell is between two numbers
 

Michael, thanks! That's exactly what I was trying to do. I really
appreciate it!:D


--
philo351
------------------------------------------------------------------------
philo351's Profile: http://www.excelforum.com/member.php...o&userid=30295
View this thread: http://www.excelforum.com/showthread...hreadid=499624

IF/WHEN forumla fo when a cell is between two numbers
 

Hi

Another variation to the answers you have already received would be
=CHOOSE(MIN(4,INT(B2/1000)+1),"A","B","C","D")


--
Regards

Roger Govier


"philo351" wrote
in message ...

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


=IF((B21000),A,IF((B22000),B,IF((B23000),C,IF(( B24000),D,""))))

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:

=IF((B2 is between 1000 and 2999),A,IF((B2is between 2000 and
2999),B,IF((B2is between 3000 and 3999),C,IF((B2 is between 4000 and
4999),D,""))))

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


--
philo351
------------------------------------------------------------------------
philo351's Profile:
http://www.excelforum.com/member.php...o&userid=30295
View this thread:
http://www.excelforum.com/showthread...hreadid=499624