I AM TRYING TO USE BETWEEN IN IF THEN ELSE

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

aries - 15 Jun 2006 16:43 GMT

how do i use between instead of greater than or less than , in if then else
statement

Reply to this Message

Ardus Petus - 15 Jun 2006 16:50 GMT

To test A1 againts range 100,200:

=IF(AND(A1<=100,A1<=200),"In range","out range")

There is no "between" operator.

HTH
--
AP

> how do i use between instead of greater than or less than , in if then
> else
> statement

Reply to this Message

Toppers - 15 Jun 2006 18:22 GMT

Perhaps ....

=IF(AND(A1>=100,A1<=200),"In range","out range")

> To test A1 againts range 100,200:
>
[quoted text clipped - 9 lines]
> > else
> > statement

Reply to this Message

David Biddulph - 15 Jun 2006 16:59 GMT

> how do i use between instead of greater than or less than , in if then
> else
> statement

If you're looking for A1 to be between B1 and C1, try
=IF(AND(A1>B1,A1<C1),D1,E1)

Signature

David Biddulph

Reply to this Message

Harlan Grove - 15 Jun 2006 17:06 GMT

aries wrote...

>how do i use between instead of greater than or less than , in if then else
>statement

Excel lacks SQL's BETWEEN operator, but if you really can't use < or >
for whatever reason, you could try something like

="x "&IF(MEDIAN(x,a,b)=x,"","not ")&"between a and b"

Reply to this Message

Kevin Vaughn - 15 Jun 2006 21:22 GMT

Interesting. I modified it slightly so I could test it and it performed as
expected:

=C4&IF(MEDIAN(C4,A4,B4)=C4,""," not ")&" between " & A4 & " and " & B4

You learn something new every day (or in my case, every other day.)

Signature

Kevin Vaughn

> aries wrote...
> >how do i use between instead of greater than or less than , in if then else
[quoted text clipped - 4 lines]
>
> ="x "&IF(MEDIAN(x,a,b)=x,"","not ")&"between a and b"

Reply to this Message