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
|