# Replace first value in a given range (column)

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

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

• 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. Jan 24, 2021 at 9:57
• I'd prefer it to be the same column 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 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). 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 Jan 24, 2021 at 11:22

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

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.