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

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

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

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

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

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

On Fri, 22 Jul 2005 04:36:39 GMT, "Les" wrote:

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


The ARRAY ENTERED formula:

=MIN(IF(A1<=rng,rng))

will do what you require.

Put your value to lookup in A1.

rng may be a named range, or a cell reference.

To ARRAY ENTER a formula, hold down <ctrl<shift while hitting <enter. Excel
will place braces {...} around the formula if you did it correctly.

The formula will return a zero (0) if A1 is greater than all the values in rng.
If this is not satisfactory, you could use either a custom format, or an IF
formula, to return something else.


--ron