RE: Match function

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

hi,

Thanks. This works as an array function:

=MIN(IF(IF(J697:R697>=$C$4,J697:R697)<$C$5,IFJ697:R697>=$C$4,J697:R697)))

to give the minimum value in a range with a min in C4 and a max in C5.

However I have a thousand rows. If I select all the column where I want the
results for each row and do control+shift+return, it just copies the formula
with the same row showing rather than the row each formula is locaetd in.
How can I paste a thousand row arrays in one go so they refer to each row?

I would like to have the formula stored in a cell at the top of the column
and then use VBA to populate the column each time I use it and then paste
values over the array so that the spreadhseet is more stable and faster (ie
without one thousand formulae recalculating volatile).

Please could you help

"Mike H" wrote:

Try,

with your lookup value in A2

=MIN(IF(A1:H1>=A2,A1:H1))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"rupert" wrote:

Hi
Please tell me workaround given my numbers are in ascending order and a need
a minimum.
I have to find the minimum value greater than a number in a row of numbers
placed in ascending order.
The MATCH function requires the numbers to be in descending order with last
parameter of Match set to -1.
Match_type Behavior
1 or omitted MATCH finds the largest value that is less than or equal to
lookup_value. The values in the lookup_array argument must be placed in
ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
0 MATCH finds the first value that is exactly equal to lookup_value. The
values in the lookup_array argument can be in any order.
-1 MATCH finds the smallest value that is greater than or equal to
lookup_value. The values in the lookup_array argument must be placed in
descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and
so on.

.

Relevant Pages

  • Re: Access a matrix row-wise without transpose?
    ... mean "I want to have vectors sorted by bb in ascending order"? ... random arrangement of 1's in the xx array. ... sort bb by using itself to sort in ascending order. ... to others, particularly those with the Crystal Ball Toolbox, it's ...
    (comp.soft-sys.matlab)
  • Re: qsort descending order
    ... in the detail of the qsort library function in 7.20.5.2, ... ascending order. ... You define what "ascending" means in your comparison function, ... we should be able to use the qsort to sort the array elements into ...
    (comp.lang.c)
  • Re: sorting int[] in descending order
    ... I know that one can sort in ascending order and reverse the array. ...
    (comp.lang.java.help)
  • Re: Reversible sort algorithm
    ... The job is to sort these numbers in ascending order ... The sorted array plus the sort-information will be ... of an available cell, ...
    (comp.programming)