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
|