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,""))))</blockquotenow
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[b]=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=499623

You should be able to use the PRODUCT function I think....

You'd need to create a table of you values such as....

This example would sit in cells A1 to C3 on Sheet2.

Lower Bound Upper Bound Value
1000 1999 A
2000 2999 B

etc....

The you could do this....

=PRODUCT(--({Enter cell reference with the numeric value you're interested
in}=Sheet2!A1:A3), --({Enter the same cell reference as
before}<=Sheet2!B1:B3), Sheet2!C1:C3)

This should just about work. If not, look up the PRODUCT function and it
should help further.

HTH.

"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,""))))</blockquotenow
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[b]=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=499623