=if(and(a2>100,a2<200),....if Only....

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

nui1 - 02 Jan 2006 01:34 GMT

Hi, I'm trying to give a cell a value based on the number in another
cell with =IF(AND(A2>100,A2<200),"This is between 100 and 200","No it's
not between 100 and 200") this works fine, I just don't understand how
to nest other OR or AND functions in there as well to give a different
value if the number is between 200 and 300 and so on. This is what I've
got and keep getting errors....Hope someone can help.

cheers,

=IF(AND(A2>100,A2<200),"This is between 100 and 200","No it's not
between 100 and 200",IF(AND(A2>200,A2<300,"This is between 200 and
300","No it's not between 200 and 300")))

Signature

nui1

Reply to this Message

Ron Rosenfeld - 02 Jan 2006 01:50 GMT

>Hi, I'm trying to give a cell a value based on the number in another
>cell with =IF(AND(A2>100,A2<200),"This is between 100 and 200","No it's
[quoted text clipped - 8 lines]
>between 100 and 200",IF(AND(A2>200,A2<300,"This is between 200 and
>300","No it's not between 200 and 300")))

A lookup will be easier.

First set up a two column table with your ranges:

e.g:

G1:H6

0    100
100    200
200    300
300    400
400    500
500    600

Then use this formula:

="This is between " & VLOOKUP(A2,$G$1:$H$6,1)
& " and " & VLOOKUP(A2,$G$1:$H$6,2)
--ron

Reply to this Message

Ron Rosenfeld - 02 Jan 2006 12:06 GMT

>>Hi, I'm trying to give a cell a value based on the number in another
>>cell with =IF(AND(A2>100,A2<200),"This is between 100 and 200","No it's
[quoted text clipped - 29 lines]
> & " and " & VLOOKUP(A2,$G$1:$H$6,2)
>--ron

And another way, if you are always going to have 100 unit 'bins':

="This is between " & ROUNDDOWN(A2,-2) & " and " & ROUNDDOWN(A2,-2) + 100

--ron

Reply to this Message

joeu2004@hotmail.com - 02 Jan 2006 03:46 GMT

> I'm trying to give a cell a value based on the number in another
> cell with =IF(AND(A2>100,A2<200),"This is between 100 and 200",
> "No it's not between 100 and 200") this works fine, I just don't
> understand how to nest other OR or AND functions in there as
> well to give a different value if the number is between 200 and 300
> and so on.

To begin with, you are nesting IF() functions, not AND() and
OR() functions.

> =IF(AND(A2>100,A2<200),"This is between 100 and 200",
>"No it's not between 100 and 200",IF(AND(A2>200,A2<300,
>"This is between 200 and 300","No it's not between 200 and 300")))

=IF(AND(100<=A2,A2<200), "Between 100 and 199",
IF(AND(200<=A2,A2<300), "Between 200 and 299",
IF(AND(300<=A2,A2<400), "Between 300 and 399",
"Greater than 399")))

Be careful with the use of "<" and "<=" to be sure that you
include all intended values, one way or another. There is
no right or wrong choice. It depends on your application.

FYI, arguably the above could be simplied as follows:

=IF(A2<100, "",
IF(A2<200, "Between 100 and 199",
IF(A2<300, "Between 200 and 299",
IF(A2<400, "Between 300 and 399", "Greater than 399"))))

Keep in mind that Excel has a limit of 7 or 8 nested IF()
functions (depending on how you count; it's a semantic
thing -- Excel calls it 7.) If you need more, a LOOKUP
function must be used.

Reply to this Message

Arvi Laanemets - 02 Jan 2006 07:03 GMT

Hi

On fly:
=IF(A2<100,"",CHOOSE(INT(A2/100),Expr1,Expr2,...ExprN))
where N can be up to 28

Signature

Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

> Hi, I'm trying to give a cell a value based on the number in another
> cell with =IF(AND(A2>100,A2<200),"This is between 100 and 200","No it's
[quoted text clipped - 8 lines]
> between 100 and 200",IF(AND(A2>200,A2<300,"This is between 200 and
> 300","No it's not between 200 and 300")))

Reply to this Message