2

In Google Sheets, I have a list of values in column A.

In column B I want to return the values from column A except I want to replace the first negative value with 0. If there are no negative values, the data should be unchanged.

I've been struggling with this for a day now.

Example Data

The value -90 in A4 is the first negative value so column B contains the same values as column A except that B4 is changed to 0

A    B                                                                                                          
1 Before  After 
2
3
4 -90  0 
5 -90  -90 
6 -90  -90 
7 -90  -90 
8 -90  -90 
9 -90  -90 
10
11
12
13
14
15

http://prntscr.com/xjizph

5
  • Hey David, do you want the values to be replaced in the same column? If not, then a helper column can help with that, else you need a script.
    – Jacob Jan
    Commented Jan 24, 2021 at 9:57
  • I'd prefer it to be the same column
    – David Ilic
    Commented Jan 24, 2021 at 10:20
  • But since I can't script, helper column is okay. How would I do that using the helper column? Thanks
    – David Ilic
    Commented Jan 24, 2021 at 10:28
  • David, what is producing the results of this column? I assume that it's a formula since, if you were manually typing the data in, you would likely just NOT type the value(s) you didn't want. If there is, in fact, a formula generating the column's results, it may be able to modified to do what you are wanting. Also, your post is unclear as to whether you would want just the FIRST -90 replaced with 0, or the entire block of instances of -90, or ALL instances of -90 (whether contiguous or not).
    – Erik Tyler
    Commented Jan 24, 2021 at 11:09
  • There is a formula that just subtracts one cell value from another. I also need only the first result less then zero (in this case only the first -90, in the third cell) to be set to 0. Sorry for not making that clear, it's my first question here
    – David Ilic
    Commented Jan 24, 2021 at 11:22

2 Answers 2

0

Please try the following

=ArrayFormula(IF(
               A2:A=QUERY({A2:A},"where Col1<0 limit 1"), 
                         "This value",A2:A))

enter image description here

(You can adjust ranges to your needs)

How the formula works:

We use the QUERY function to find all negative values and limit the result to just the first found.
Then the IF function returns This value (or whatever you want) if true or the rest of the range if false.

Functions used:

1
  • Your formula replaces 'all' instances of the first negative value whereas the OP wanted only the first (single) instance of a negative value replaced. To address that the formula would need to be something like: =ArrayFormula(IF(ROW(A2:A)=QUERY({ROW(A2:A),A2:A},"Select Col1 where Col2<0 limit 1"),"This value",A2:A))
    – Blindspots
    Commented Jun 11 at 19:02
0

IF w/ MATCH

=INDEX(IF(ROW(A:A)=MATCH(TRUE, A:A<0, 0), 0, A:A)) 

Variation:

=INDEX(IF(ROW(A:A)=MATCH(1,--(A:A<0), 0), 0, A:A))

There are a few valid approaches you could take however the key is that, whatever the approach, it is the row number not the row's value that is returned. This is because you only want to replace the first instance of the value.

Explantation

  1. A:A<0 to convert column A to an array of TRUE/FALSE values.
  2. MATCH to find the row number of the first instance of TRUE.
  3. The variation looks for 1 instead of TRUE. It uses the double unary operator -- to first convert the array of TRUE/FALSE values to 1/0 values.

IF w/ MIN & FILTER

=INDEX(IF(ROW(A:A)=MIN(FILTER(ROW(A:A), A:A<0)), 0, A:A))
  1. IF is used similarly to the previous formula to return 0 if the row number is matched.
  2. In this approach FILTER returns an array of row numbers where A:A<0 is TRUE and MIN then returns the lowest row number.
  3. MIN(row_num_array) approach was chosen simply because it is shorter than other approaches that achieve the same thing, such as INDEX(row_num_array,1) and QUERY(row_num_array, "Limit 1").

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.