Ron's between function in excel..

[Angela]

Hi friends,

Somehow I could not find the reply link on this previous August post.
Below is the function..

=========================
Option Explicit
Function Between(Number As Double, Bound1 As Double, Bound2 As Double)
As Boolean
Dim LowerLimit As Double
Dim UpperLimit As Double
LowerLimit = IIf(Bound1 <= Bound2, Bound1, Bound2)
UpperLimit = IIf(Bound1 >= Bound2, Bound1, Bound2)
Between = Number >= LowerLimit And Number <= UpperLimit
End Function
===============================


I was wondering if someone can refine this a bit more.

I want to get the information from the table as follows:

FILE NAME START END
A 1 5
B 6 10
C 11 12

Search Value 7
File Name B

If I use above formula I will get false, true, false against 1-5,6-10
& 11-12 respectively. True shows it falls between 6 & 10.
I want to get file name "B". How can I modify this function to get me
this information?
I hope I have asked clearly.

 

[Pete_UK]

You can do that with normal worksheet functions easily enough. Assume
your table occupies A1:C4 with the search value in B6. You can use
this formula:

=INDEX(A2:A4,MATCH(B6,B2:B4))

Hope this helps.

Pete

 

[joeu2004]

Function Between(Number As Double, Bound1 As Double, Bound2 As Double)
As Boolean
    Dim LowerLimit As Double
    Dim UpperLimit As Double
LowerLimit = IIf(Bound1 <= Bound2, Bound1, Bound2)
UpperLimit = IIf(Bound1 >= Bound2, Bound1, Bound2)
Between = Number >= LowerLimit And Number <= UpperLimit
End Function [....]
I was wondering if someone can refine this a bit more.

Pete provides a good answer to your question. I want to comment on
the function above.

There really is no need to invent UDF, IMHO. Excel already has a
"between" function; it's called AND(). To wit:

AND(bound1<=number,number<=bound2)

The UDF above adds the feature of permitting bound2<bound1 and
reversing the logic effectively to AND(bound2<=number,number<=bound1).

That, too, can be implemented directly in Excel relatively easily.
But "easy" is subjective. If you want the UDF for that feature,
fine. If you want to avoid it (and the concomitant hassles with macro
security), use one of the following:

AND(MIN(bound1,bound2)<=number,number<=MAX(bound1,bound2))

or

IF(bound1<=bound2,AND(bound1<=number,number<=bound2),
AND(bound2<=number,number<=bound1))

I suspect the IF expression is more efficient, but the AND/MIN-MAX
expression is obviously more compact. I suspect both are more
efficient than the UDF.

 

[Angela]

Hi Peter,

Your formula works on sorted list.. =INDEX(A2:A4,MATCH(B6,B2:B4))

Two things:
#01:
What if the list is not sorted i.e. this part of the formula B2:B4 is
unsorted.
Its easy to sort the list first & then use above formula but sorting
the list is not a good idea in the file I'm working in.
Can this be catered in the formula itself?
#02:
For any value greater or not pat of the grid, the formula is returning
last file name.
This should not be the case. Can this be corrected as well?
For instance if search value is 16, the file name return is still "C".


Many thanks for the response.

Hi Joeu,
You formula is calculating same wht the function was returning.
I have to place it with the list and get false/true to get to the file
name.

Peter's formula is however returning the result but little needs to be
done with sorting which I have posted to him.

Thanks both of you for your input.

 

[Pete_UK]

Angela,

There is a third optional parameter that can be used with MATCH, set
to TRUE (or 1) or FALSE (or 0). If it is missing, as in my formula, it
defaults to TRUE. This parameter controls how MATCH works - if it is
set to FALSE then the function will look for an exact match and the
data in the table does not need to be sorted, whereas if it is TRUE
(or missing), the function will look for the highest value that is
less than or equal to the sought-item. This is suitable for your
table, as you have ranges of values that relate to a particular file
name. For this to work, though, the table MUST be sorted. I take it
from your comments that the example table you posted does not bear
much resemblance to your real data.

To avoid the filename being returned for values out of range you can
amend the formula like this:

=IF(B6>MAX(C:C),"",INDEX(A2:A4,MATCH(B6,B2:B4)))

or, as an alternative, you can type a space in A5 and put this formula
in B5:

=C4

and amend the ranges in the original formula like this:

=INDEX(A2:A5,MATCH(B6,B2:B5))

Hope this helps.

Pete

 

[Charabeuh]

hello !

Try this formula (the value to find is in cell E2):

=INDEX($A$1:$A$4,IF(SUMPRODUCT(($B$2:$B$4<=E2)*(E2<=$C$2:$C$4)*
ROW($A$2:$A$4))=0,NA(),SUMPRODUCT(($B$2:$B$4<=E2)*
(E2<=$C$2:$C$4)*ROW($A$2:$A$4))))

It will return #N/A if the value is not found
(for example with 0, 0.5, 5.5, 10.5, 13, 16...)




"Angela" a écrit dans le message de groupe de discussion :
(e-mail address removed)...

Hi Peter,

Your formula works on sorted list.. =INDEX(A2:A4,MATCH(B6,B2:B4))

Two things:
#01:
What if the list is not sorted i.e. this part of the formula B2:B4 is
unsorted.
Its easy to sort the list first & then use above formula but sorting
the list is not a good idea in the file I'm working in.
Can this be catered in the formula itself?
#02:
For any value greater or not pat of the grid, the formula is returning
last file name.
This should not be the case. Can this be corrected as well?
For instance if search value is 16, the file name return is still "C".


Many thanks for the response.

Hi Joeu,
You formula is calculating same wht the function was returning.
I have to place it with the list and get false/true to get to the file
name.

Peter's formula is however returning the result but little needs to be
done with sorting which I have posted to him.

Thanks both of you for your input.

 

[Angela]

Hi Peter,

I think we are close..

FILE NAME START END
A 1 2
B 6 10
C 15 17

Search Value 5
File Name "File not found."

The formula works if search value is 20 however if search value is 5,
the formula =IF(B6>MAX(C:C),"",INDEX(A2:A4,MATCH(B6,B2:B4))) is
returning "A" which is in correct.

Since 3, 4 & 5 are not in the table, it should not return anything,
just like any number > 17.

Many thanks for your replies.

Hi Charabeuh,

Your formula works if the data is placed in a grid starting from cell
A1.
However I get a ref error if the table is elsewhere in the sheet.

Thanks.

 

[Pete_UK]

Angela,

the table is a bit different than the one you first posted, as the
other one had contiguous ranges. However, this will cope with the
current requirements:

=IF(SUMPRODUCT((B6>=$B$2:$B$4)*(B6<=$C$2:$C$4),ROW($B$2:$B
$4)),INDEX($A:$A,SUMPRODUCT((B6>=$B$2:$B$4)*(B6<=$C$2:$C$4),ROW($B$2:$B
$4))),"not found")

This is all one formula in B7 - be wary of spurious line-breaks that
some newsgroup viewers can insert.

Hope this helps.

Pete

 

[Angela]

Bulls eye : )

Thanks!!

 

[Angela]

Peter, how can this logic be put into a one time function?

It requires a search value, start value, end value, and an optional
column as result to get.

= Between(SearchValue, StartRange,EndRange, ReturnColumn,
ErrorMessage)
=between(5,columnB,columnC,ColumnA, "Not Found")

 

[Pete_UK]

You're welcome, Angela - I'm glad we got there in the end.

Pete

 

[Pete_UK]

Sorry, I don't understand what you mean. Are you asking for a User-
defined function (UDF) which will take those parameters and return the
appropriate result?

Pete

 

[Angela]

Hi Peter,

Yes.. and may as well ask microsoft to add in next release of office
2010 as a built-in feature.
Sure will be great to have such a function.. with of course few
improvements.

O well, for the time being, yes I would like to see if it can be
brought into a UDF and relief from sumproduct/index etc. formulas
Since we got the logic in place

Much appreciate your help.

Angela.

 

[Pete_UK]

Well, Angela, I'm no expert in VBA, and I'm going away for a week, so
I would suggest that you repost in the programming group or even start
using the MS Forums which you can find here:

http://social.answers.microsoft.com/Forums/en-US/category/officeexcel

and hopefully someone else will be able to help you.

Hope this helps.

Pete