Google-sheets – Replace first value in a given range (column)

google sheets

How would I look up the first value < 0 in this range and replace it with 0? If the value <0 does not exist, I'd like the column to remain intact. I've been struggling with this for a day now

http://prntscr.com/xjizph

Edit: I need only the first result less then zero (in this case only the first -90) to be set to 0.

Best Answer

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:

Related Topic