Formula that returns a B when a sum of random numbers is zero with neg & pos #

[lar9149]

Hello all

Hope you night is going well.

In the provided sample, column A-M is a set of random numbers that can be positive, neg or zero and columns R through AF is the cumulative sum of these numbers starting from column A in the corresponding row.

I am trying to make a formula in column AJ that returns B, otherwise blank ("") when when either 2 of the following condition is met (highlighted bold in the sample)

1. The first cumulative sum or earliest sum in columns R-AF that is zero and the corresponding numbers that represent the cumulative sum are of opposite signs either negative numbers at the beginning with positives after without another negative once a positive number appears (prior to the first cumulative sum of zero) or vice versa.
2. the first 2 numbers in column A & B are zero (like in row 22).

As an example in row 6, the first 2 numbers are -1,0,-1,2..which the cumulative sum is zero and meeting condition 1.
In row 7, the numbers are -1,0,-1,1,-2,1,2..the sum of these numbers are zero, but after the first positive number, 1, a -2 appears so it doesn't meet condition one.

Thanks again for any help.

[Glenn Kennedy]

It would have been easier if you'd left the formulae in place!!

=IFERROR(IF(OR(AND($A1=0,$B1=0),SIGN($A1)<>SIGN(INDEX($A1:$M1,MATCH(0,$R1:$AF1,0)))),"B",""),"")

Your expected answer at row 7 seems to be incorrect.

[lar9149]

It would have been easier if you'd left the formulae in place!!

=IFERROR(IF(OR(AND($A1=0,$B1=0),SIGN($A1)<>SIGN(INDEX($A1:$M1,MATCH(0,$R1:$AF1,0)))),"B",""),"")

Your expected answer at row 7 seems to be incorrect.

Thanks again Glenn Kennedy, I will make sure to include the formula next time.