How to find the first value greater than...

This solution assumes "the first value greater than..." does not mean "the smallest value greater than…"

Cell D2 reports the first value greater than zero found when Column B is scanned from row 2 to row 10

The calculations are done with the two formulas shown below the expanded table.

The first is entered in C2 and filled down to the last cell in that column, the second is entered in D2.

The Column C formula counts formula counts the number of values greater than 'this value' (0) in rows 2 to 'this row' of column B.

The D2 formula looks for values greater than 0 in column C, searching from top to bottom, finds the first one, and returns the value from that row of column B.

Regards,

Barry

If your goal is to match the first number greater than zero and you don't want to bother with adding an extra column then you can replace your original Excel formula with a formula that uses the REGEX function, something like this:

=XMATCH(REGEX("^[^-]"),C5:C12,2)

If you've got numbers starting with something like 0.9 then the regex needs to be adjusted, but this should cover most situations.

SG

If you don't want the extra column then a formula with this REGEX seems to work well, handling situations where you have something like 0.4 or 3,000.

=XMATCH(REGEX("^([^0-]|0.)"),$C$5:$C$12,2)

Regex can be hard to understand, but the first ^ is an "anchor" that says to check the beginning of the number and the one between the [ ] means not. The | used between the ( ) means or.

So this is checking for values not beginning with a 0 or -, or values that do begin with a 0 followed by a decimal point.

Anyway, it seems to work!

SG

Excel "array" formulas are pretty slick. Unfortunately I don't think that can be done in a single cell in Numbers. It will require a column of IF formulas to do the $C5:$C12>0 part of it (to generate the "array" of TRUE and FALSE results) then you use MATCH on that column.

Formula in D5 through D12 is =C>0

=MATCH(TRUE,$D5:$D12,0)

I didn't like my previous answer. Here is a better way. With the first method, if you have several (or many) of these formulas, each would require its own column of TRUE/FALSE and that would be painful. The method below still requires an extra column, but only one of them. It also makes it easy to change the criteria and have multiple criteria like ">0" and "<5" without having to edit a column of formulas.

In D5-D12 put the numbers 1 though 8. This will get you the same results as your formula. I usually use =ROW() to get the actual row number of the match but that isn't what your formula does.

Formula =MINIFS(D5:D12,C5:C12,">0")

Or you can have multiple criteria like this =MINIFS(D5:D12,C5:C12,">0", C5:C12,"<5")

Use MAXIFS if you want to find the last match rather than the first one.

To clarify, my solution using MINIFS gives the minimum number from column D where the value in C meets the criteria. It is giving the first match. If D is row numbers, it gives the row number of the first match.

You did not implement my solutions correctly. The first one attributed to me should have a column of formulas that result in TRUE or FALSE. MATCH should be looking at that column. The second one should have a column of ascending numbers. It is all in my posts.

My formulas are solutions to your question "How to find the first value greater than...". Your example was >0 but your question was more general than that. "Greater than ..." implies it could be >4 or >-200 or whatever. Is >0 the only case you care about?

If you always have the numbers in Currency format as in your screenshot then this should work:

=XMATCH(REGEX("[^$0.00]"),$A1:$A5,2)

This assumes no negative numbers.

SG