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