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

N

nui1

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")))
 
R

Ron Rosenfeld

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")))
Click to expand...

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
 
J

joeu2004

nui1 said:
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.
Click to expand...

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")))
Click to expand...

=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.
 
A

Arvi Laanemets

Hi

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

Ron Rosenfeld

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
Click to expand...

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
 
Top