Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
philo351
 
Posts: n/a
Default 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

  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Michael Gill
 
Posts: n/a
Default 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


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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding numbers in one cell and showing total in seperate cell Deernad Construction Excel Discussion (Misc queries) 12 November 29th 05 07:32 PM
How do I hide the zero in the "sum" cell until numbers are put in the cells that are being added? Radman Excel Worksheet Functions 6 October 9th 05 04:25 AM
cell color index comparison MINAL ZUNKE New Users to Excel 1 June 30th 05 07:11 AM
up to 7 functions? ALex Excel Worksheet Functions 10 April 12th 05 06:42 PM
create a cell that accumulates, numbers from another cell... sombull Excel Discussion (Misc queries) 1 February 18th 05 04:45 PM


All times are GMT +1. The time now is 09:58 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"