IF/WHEN forumla fo when a cell is 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


=IF((B2>1000),A,IF((B2>2000),B,IF((B2>3000),C,IF((B2>4000),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?

[Michael Gill]

Hi philo

This one should do it for you
=IF(AND(B1>1000,B1<1999),"A",IF(AND(B1>1999,B1<2999),"B",IF(AND(B1>2999,B1<3999),"C",IF(AND(B1>3999,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((B2>1000),A,IF((B2>2000),B,IF((B2>3000),C,IF((B2>4000),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
>
>

[Ron Rosenfeld]

On Mon, 9 Jan 2006 21:22:51 -0600, philo351
<[email protected]> 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((B2>1000),A,IF((B2>2000),B,IF((B2>3000),C,IF((B2>4000),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",IF(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

[philo351]

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

[Roger Govier]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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((B2>1000),A,IF((B2>2000),B,IF((B2>3000),C,IF((B2>4000),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
>