1

I am hoping someone can help me with a Google Sheets formula to calculate the minimum sum of consecutive 0s and -1s in a column. 0s and -1s are consecutive until interrupted by any other integer.

For example, in the column below:

  1. There are two sets of consecutive 0s and -1s separated by a 1 in A4.
  2. The first set, in A1:A3, adds up to -2
  3. The second set, in A5:A10, adds up to -4
  4. The formula should therefore return -4, which is the minimum sum of consecutive -1s and 0s,
      A  
    +----+
  1 | -1 |
  2 | -1 |
  3 |  0 |
  4 |  1 |
  5 | -1 |
  6 |  0 |
  7 | -1 |
  8 | -1 |
  9 |  0 |
 10 | -1 |
    +----+
3

2 Answers 2

1
=LET(x,TOCOL(A1:A, 1),
  -MAX(
     INDEX(
       LEN(
         SPLIT(
           JOIN(,
             IF(x<-1, 2,
               IF(x>0, 2, SUBSTITUTE(ABS(x), 0,)))),
             2)))))

As a single line:

=LET(x,TOCOL(B1:B,1), -MAX(INDEX(LEN(SPLIT(JOIN(, IF(x<-1, 2, IF(x>0, 2, SUBSTITUTE(ABS(x), 0,)))), 2)))))
  1. x stores column A (assuming that's where your numbers are) after first removing any blank rows with TOCOL

  2. Next the numbers in the array x are replaced as follows:

    1. if x<-1 or if x>0 it's replaced with 2
    2. if x=0 it is removed
    3. if x=-1 it's replaced with 1
      For example,
    # Original Values 
    {-1; -1; 0; 1; -1; 0; -1; -1; 0; -1}
    # After Replacement {1; 1; 2; 1; 1; 1; 1}
  3. JOIN creates a text string from the array of 1s and 2s without any delimiter:

    # Array  (from #2)
    {1; 1; 2; 1; 1; 1; 1}
    # Text String "1121111"
  4. SPLIT separates the text string into an array using the 2 as the delimiter, resulting in one array member for each original uninterrupted group of -1s and 0s because each 2 represents a number in the original array that was neither a -1 or a 0:

    # Text String  (from #3)
    "1121111"
    # Array {11, 1111}
  5. LEN counts the characters in each group:

    # Array  (from #4)
    {11, 1111}
    # After LEN {2, 4}
  6. The negative value of the maximum length is returned from the array:

    # Array  (from #5)
    {2, 4}
    # After -MAX -4
    # Equivalent to SUM({-1; 0; -1; -1; 0; -1})
1
  • 1
    I am humbled by your answer and your explanation on how you reached it. Thank you so much this worked perfectly and helped me learn how to use these functions. Have a wonderful weekend ahead :)
    – Will
    Commented Apr 5 at 6:19
3

Here's an alternative simpler answer, if you are interested:

=min(scan(0,A1:A10,lambda(acc,arr,ifs(arr=-1,acc-1,arr=0,acc,true,0))))

scan the array, subtracting 1 from the accumulator each time a '-1' is seen, leaving the accumulator unchanged every time a '0' is seen, and resetting the accumulator to '0' if any other value is observed. This generates a running sum with local minima at the positions of the final '-1's in the array. Then simply use min on the whole thing to obtain the global minimum.

2
  • Nice approach. Was already thinking the way I did because of my previous answer on SU. Your approach here makes sense. FYI if referencing another answer (as you did here) link to it as there is no guarantee in the future that there will be only one other answer or that they will be beside each other, and it is simply good practice to link to things that are referenced. +1
    – Blindspots
    Commented Apr 5 at 14:25
  • FYI I updated my original answer based on the shake you gave me. TY!
    – Blindspots
    Commented Apr 5 at 20:37

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.