Hi!

But the match number and the search range should be dynamic.

They are dynamic simply because they're user inputted. The range is user
input and the match value should also be user input.

What you probably actually want is automation. It sounds like everyday the
whole data range shifts down 1 row. If that's the case and if you don't
actually input the match value, rather it is selected by default when on the
next day the data range moves down 1 row, That would more than likely
require VBA programming. I can't help you with that!

Biff

"Les" wrote in message
...
Hi Biff,
It's really close.
The formula works,
But the match number and the search range should be dynamic. As new data
is added, the match number changes along with the range:

Day 1: [a4] 6.75 is the match#, the range is a5:a9 (or a spread of 5,
set by user).
a4 6.75
a5 5.00
a6 6.25
a7 7.00
a8 6.50
a9 4.00
a10 6.75

Day 2: [a5] 5.00 is the match#, the range is a6:a10 (or a spread of 5,
set by user).
a5 5.00
a6 6.25
a7 7.00
a8 6.50
a9 4.00
a10 6.75
a11 4.50

Day 3: [a6] 6.25 is the match#, the range is a7:a11 (or a spread of 5,
set by user).
a6 6.25
a7 7.00
a8 6.50
a9 4.00
a10 6.75
a11 4.50
a12 5.00

Day 4: and so on....

I had originally used the offset() function as it allows the input of a
range (determined by a spread). I do not expect to continue to take
advantage of your time, talent, and kind intentions. They have been
greatly appreciated. I am learning alot.

Les


"Biff" wrote in message
...
Hi!

One thing you have to understand is that everything works from the top
down. You keep using references from the bottom up. Worksheet formulas
don't work from the bottom up!

See if this does what you want. It's a little complicated....

Based on your posted sample in the range A5:A10

Create a defined named range formula:

Goto InsertNameDefine
Name: Rng
Refers to: =INDIRECT("A"&Sheet1!$B$1&":A"&Sheet1!$C$1)

A1 will hold your lookup (match) value
B1 will hold the number of the range starting point
C1 will hold the number of the range ending point

Enter this formula in B5 using the key combo of CTRL,SHIFT,ENTER:

=IF(OR(A$1="",B$1="",C$1="",ROW()<B$1,ROW()C$1)," ",IF(A5=A$1,"Match",IF(COUNTIF(B$4:B4,TRUE)=1,"",I F(A5=MIN(IF(RngA$1,Rng)),TRUE,""))))

Copy down to B10

Here's how it will work:

A1 = 6.75
B1 = 5
C1 = 10

It will search the range A5:A10 for the lookup value in A1, 6.75. If
there is a match, it will return MATCH. Based on your sample B10 will
return MATCH. Then it will return TRUE for the next highest value that is
greater than the lookup value. Based on the sample data B7 will return
TRUE.

If there is no match of the lookup value no cell will return MATCH but
the next highest value that is greater than the lookup value will still
return TRUE.

You can have the user select the size of the range used by entering in B1
and C1 the row number that corresponds to the range. For example:

B1 = 5
C1 = 10

The range will be A5:A10

B1 = 8
C1 = 10

The range will be A8:A10

B1 = 5
C1 = 100

The range will be A5:A100

If none of the criteria are met within the selected range the formula
will leave all cells blank.

Biff

"Les" wrote in message
...
Hi Biff,

Care to elaborate on that. Do you mean a numerical range like: 5.0 to
7.5, or do you mean the rangeof cells like A5:A10 ?

A range of cells like A5:A10.

If the user could set the range of cells to be tested. In the example
the True signal was found after checking a range of 3 cells (A9, A8, and
A7). How would the user input a variable where say, a range of 2 cells
(A9 and A8) were the only cells tested? Or, the user could change this
variable to where 6 cells (A9:A4) were the cells to be tested?
When I first started experimenting with this problem I had success
inputting different ranges (the variable I spoke of above) using the
offset() function. But wasn't able to get what I wanted to do using it
with the match() function.

Les



"Biff" wrote in message
...
Hi!

If [a10] where the number to match (6.75), the formula would examine:

(1) [a9] to see if greater than [a10] ....... False
(2) [a8] to see if greater than [a10] ........ False
(3) [a7] to see if greater than [a10] .........True

After three tries (in this example) the formula generated a 'True'
signal.

If that's the kind of test and output you were looking for, we can do
that easily but it would work from the top down, not from the bottom up
as in your example.

If the user could set the range of cells to be examined

Care to elaborate on that. Do you mean a numerical range like: 5.0 to
7.5, or do you mean the rangeof cells like A5:A10 ?

Biff

"Les" wrote in message
...
Biff,
Thank you very much for your input and solution to my question. I am
relatively new to spreadsheets, functions and nested formulas (boy are
they confusing). But I like working with numbers. I have been
experimenting with stock pricing data, trying to find a buy / sell
discipline.
After plugging in your formula below I understand why you insisted on
specifics.

Specifically:
a5 5.00
a6 6.25
a7 7.00
a8 6.50
a9 4.00
a10 6.75

If [a10] where the number to match (6.75), the formula would examine:

(1) [a9] to see if greater than [a10] ....... False
(2) [a8] to see if greater than [a10] ........ False
(3) [a7] to see if greater than [a10] .........True

After three tries (in this example) the formula generated a 'True'
signal.

I understand I could nest a bunch of =if() statements, but I was
trying to find a way where the user could set the range of cells to be
examined. If the user could set the range of cells to be examined, one
could test a buy / sell discipline over different spans of time. The
formula you came up with helps. Thank you very much.

Les

"Biff" wrote in message
...
Hi!

A1 = lookup (match) value

=IF(ISNA(MATCH(A1,A5:A9,0)),MIN(IF(A5:A9A1,A5:A9) ),INDEX(A5:A9,MATCH(A1,A5:A9,0)))

This is an array formula and must be entered using the key combo of
CTRL,SHIFT,ENTER.

This will do what you want but it's not very robust as you haven't
given many details about what you're trying to do.

Biff

"Les" wrote in message
...
Biff,
A column, a5 to a9, of random numbers:
a5 5.00
a6 6.25
a7 7.00
a8 6.50
a9 4.00

The given 'Match' number is 5.50.
The equal or greater number I expect to discover is a6 6.25.
The Match function will find 6.25 only of the 'lookup value' is 6.25
also.

Thanks for your input.
Les

"Biff" wrote in message
...
Hi!

Is there a way or a function which will find a equal or greater
value in a random order range?

Short answer: yes

Describe what you want to do and be *very specific*. Include range
locations, data type(s), etc.

Biff

"Les" wrote in message
...
The match function will search a random order range to find an
exact match.
It will search an ascending order range to find an equal or lesser
value. And inversely it will find an equal or greater value in a
descending range.
Is there a way or a function which will find a equal or greater
value in a random order range?

Thanks,
Les