find row number of first cell in a relative range that is less than $B$3

Here's an answer to your initial question:

The result formula (shown) returns the minimum value in the three cells of column D ending with the row on which the formula sits. The IF part of the formula suppresses the result where the three rows are 'empty'.

D2 contains the formula: =IF(C>$B$3,"",ROW())

This returns a null string where the corresponding column C value is greater than the value in B3, or the Row number where the value in C is less than or equal to the value in B3. Column D may be hidden or moved to a separate table if needed.

Regards,

Barry

And here's a modification that returns the date rather than the row number.

The layout is a little confusing, as I've left the results cells in the same space and added a Dates in column A.

The only formula change is shown on the table. This formula is filled down to the rest of column B.

Details on all of the functions used is available in the iWork Formulas and functions User Guide, which can be downloadd via the Help menu in Numbers.

Regards,

Barry

The single formula in D2, filled down that column, returns the row number of every row where the value in column C is less than or equal to the value in $B$3.

The second formula, which I placed in B4, but which can go wherever it's needed, searches a limited range of column D for the minimum value in that range.

In the example, based on your initial question, the range is three cells. In B4, it's D2:D4; in B5 it's D3:D5, etc., with Numbers automatically adjusting the cell references as the formula is filled down the column. In each case, the formula returns the minimum value it finds in the three cell range, which is the row number on which the value in column C first meets the condition (less than or equal to $B$3) within that three cell range.

In the second example, the formula retrieves the same value (the row number) as above, but instead of displaying it, uses it as the row-offset value in an OFFSET statement to return the Date from the row where the row number was collected.

NOTE: Looking back at it, I see that in my rush to other duties I made an error in this formula, setting the row offset too high. Revise the formula as shown below to get the date from the correct row:

Existing: =IF(MAX(D2:D4)=0,"",OFFSET($A$1,MIN(D2:D4),0))

Revised: =IF(MAX(D2:D4)=0,"",OFFSET($A$1,MIN(D2:D4)-1,0))

You wrote:

If I'm following the logic, it would require one column (like D in this example) for every set of rows searched (in my example, B5:B15 filled down, so the next search is B6:B16 and so on).

No. Provided the values of X and Y are constant for each set of searches, you'll need an auxiliary column for each column of values to be searched. One auxiliary column is used to mark the rows in which the value in B meets the condition ">x", the second to mark the rows where the value in C meets the condition "<y".

The search formula (above) searches a subset of its column and returns the lowest row number where the condition for that column is met,and either displays that number or uses it to return and display a date.

Regards,

Barry

RJ mac wrote:

Well, shoot. I didn't explain it well enough in the first place. Unfortunately, X and Y do change... at every row, going down for hundreds of rows.

So the reference in the initial question should not have been to the fixed cell $B$3. 😉

What is the possible range of the values of X and Y? Are they, as suggested in the initial post, integer values? If not, can the X and Y values be treated as a range of discrete values?

Is the search range at each step 11 cells?

Regards,

Barry

The iWork Formulas and Functions User Guide has very little to say about "array functions":

A small number of functions are described as

“array function,” meaning the function returns an

array of values rather than a single value. These

functions are commonly used to provide values

to another function.

MATCH does not seem to be one of this "small number," according to its description in the same guide. 😟

OOo (OpenOffice.org—the application's name includes the.org for reasons explained at the website) is available for Mac OS 10.5.8 and newer, starting with version 3.0.

Regards,

Barry

Barry,

TRANSPOSE

FREQUENCY

LINEST

are the ones that come to mind.

Jerry