2

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.

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

1 Answer 1

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
  • Have you tried the given solution? Did it work for you? If yes, as per site guidelines when an answer addresses your question, you should accept it and maybe upvote it so others can benefit as well. If not, please read how to and share a test sheet so as you can be easier helped. Jan 29, 2021 at 6:33

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.