Find the 1st Instance of a negative Number in a Column in Numbers

sorry, not at my computer so I can’t give screen shots.

I think I would use a formula. Fill it down from the top, counting how many negative numbers from that row up. Not the whole column.

if the answer is 1 and it’s negative show a 1, otherwise show 0.

you can then do an index match from another table that looks for 1 in that column and brings back that row value in the actual value column,

use the match function to bring back what row that 1 is on.

hope that made sense.

jason

Here's a simple example with formulas to retrieve the row number or the date on which the balance first goes below zero.

To get the row number:

=MATCH(REGEX("^-."),Table 1::B,0)

To get the date:

=XLOOKUP(REGEX("^-"),Table 1::B,Table 1::A,"No neg",2)

Replace the , in the formulas with ; if your region uses , as a decimal separator.

Keep in mind that instead of struggling with formulas you can also just apply a Conditional Highlighting rule to show negative values in, say, red.

SG

I'm not sure what I was thinking with my 1st formula but it will not work. One of the others will, depending on your number formatting, or REGEX can handle both in the same formula.

=XLOOKUP(REGEX("[-(]"),B,B,"nf",2,1)

It is looking for the first row that has a "-" or "(".

davestang wrote:

how do I have it tell me what row that answer comes from?

Did you see my post above? It shows both how to look up the row number using MATCH and how to get the value using XLOOKUP.

SG

If the column is B, there are few formulas that can do it

=XLOOKUP(−1×10^−126,B,B,"not found",-1,1)

Anything more negative than -1E-126 (-1/1000000000000.....000) will be considered negative

The -1 is to search for "equal to or less than"

The 1 is to search "first to last"

It is not exactly perfect, there are negative numbers between 0 and -1E-126 that are not included.

You could instead look for the "-" sign

XLOOKUP("-*",B,B,"not found",2,1)

or, for accounting style, you need to look for currency symbol tab (

XLOOKUP("$‎ (*",B,B,"not found",2,1)