0

Thx for coming in! I need a formula to trace and sum the value in a column, upwards, until it hit an positive value.

For the following table, A will have the answers, and B will be the column where I need the formula.

I want a non-array formula as manual editing could be needed.

A B
1 0
0 0
-1 -1
-2 -3
-1 -4
0 -4
-4 -8
1 0

As you can see, I need an non-array formula for each cell in column B. In column A, where the answers appeared, if a negative value starts to show up, and finally a positive number shows up, I see it as a cycle is completed.

In column B it sums up all the negative value in a cycle upwards until it hits an positive value. Of course it also need to skip the value 0 and keep tracing upwards if that happened.

What formula could I use for column B?

Thx again!

0

1 Answer 1

1

Use ifs() and offset(), like this:

=ifs( 
  A1 > 0, 0, 
  row(B1) = 1, A1, 
  true, offset(B1, -1, 0) + A1 
)

See ifs() and offset().

3
  • it works amazingly, but there is a slight issue. Sometimes in A column the data will be missing, and the formula need to skip the empty cell and keep tracing upward. It is possible? Commented May 13 at 8:25
  • The formula will treat truly blank values the same as zeros. To also treat zero-length text strings "" the same as zeros, replace the first instance of A1 with value(A1). Please do not present new requirements after you have received an answer. Ask only one question per post. Post a new question instead. Commented May 13 at 8:40
  • understood, I will post a new question even it's similar but with new requirements next time. Thx 4 the help Commented May 13 at 9:04

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.