BETWEEN function in Excel? (is there one)?

[trice-nae]

I just realized I inadvertently posted the following to the Mac/Excel
group. I know x-posting is not desirable/necessary, but I'm using
Windows and thought the mac group might have less traffic. Thanks.

****************************************************************************************


The short story: does excel have any function that can tell me whether
a value is between two other values?

It seems like this is a pretty basic one (easy to do from a programming

point of view) as well as very useful, so I'm surprised that excep
doesn't seem to have it. I have needed this many times, and always had
to find a workaround - I'm hoping there's just something I don't know
about.


Here's the long story: I have two columns of numbers (min loan size
and max loan size) and each min/max combination may be different. I am
trying to package them into some kind of "standard" (e.g. 10-25K,
25-75K, 75-150K, 150+)


I need to write a formula to assign a category to each line. It would
test whether the midpoint of my categories (17.5K, 50K, 112.5K, 500K)
is between the min/max amounts in the 2 columns OR whether the min/max

amounts are both between the bounds of my categories. I can do this by
writing a monster formula using IFs, ANDs and ORs, but it seems like a
big PITA, y'know? I'm looking for something a little easier.


Reply »

 

[Pete_UK]

If you want to allocate a simple code to the loan amounts, then set up
this small table somewhere in your sheet (eg X1 to Y4):

10 A
25 B
75 C
150 D

then with a loan amount in A1, enter this formula in B1:

=VLOOKUP(A1,X$1:Y$4,2)

and this will return the appropriate code. Of course, you could use
words instead of the letters in Y1:Y4.

If you want to check for a value being between two other values, then
you would normally use a combination of less than and greater than. For
example, to test if A1 is between 20 and 30 inclusive, use this:

=IF(AND(A1>=20,A1<=30),"yes","no")

Hope this helps.

Pete