Remember Me?

Posted to microsoft.public.excel.worksheet.functions
 philo351 Posts: n/a 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

 ExcelBanter AI Excel Super Guru Posts: 1,867 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.

__________________
I am not human. I am an Excel Wizard
Posted to microsoft.public.excel.worksheet.functions
 Michael Gill Posts: n/a 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

Posted to microsoft.public.excel.worksheet.functions
 Ron Rosenfeld Posts: n/a 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
Posted to microsoft.public.excel.worksheet.functions
 philo351 Posts: n/a 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

Posted to microsoft.public.excel.worksheet.functions
 Roger Govier Posts: n/a IF/WHEN forumla fo when a cell is between two numbers

Hi

=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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Deernad Construction Excel Discussion (Misc queries) 12 November 29th 05 07:32 PM Radman Excel Worksheet Functions 6 October 9th 05 04:25 AM MINAL ZUNKE New Users to Excel 1 June 30th 05 07:11 AM ALex Excel Worksheet Functions 10 April 12th 05 06:42 PM sombull Excel Discussion (Misc queries) 1 February 18th 05 04:45 PM

All times are GMT +1. The time now is 09:58 AM. Copyright ©2004-2023 ExcelBanter.