Forum Discussion

mikenizzle101's avatar
mikenizzle101
Copper Contributor
Sep 29, 2022

Count the number of zeroes, from right to left

I'm looking to count the number of zeroes from right to left, based on the latest value. This count stop if a non-zero appears.

 

So for example, I have a row of the following values:

Example #1:

(10, 0, 0, 2, 0, 0, 3, 1, 0, 0, 0, 0, 0, 5, x - the formula)

X = 0, because the last value is a non-zero

 

Example #2:

(10, 0, 0, 2, 0, 0, 3, 1, 0, 0, 5, 0, 0, 0, x - the formula)

X = 3, because there were three zeroes before a non-zero popped up

 

I have an array formula that returns the position of the first column on the right that isn't a 0, but I'm not sure where to go from here (if this is even a viable solution on the right track).

 

=COLUMN(INDEX($E22:$AF22,MAX(($E22:$AF22>0)*ISNUMBER($E22:$AF22)*(COLUMN($E22:$AF22)-COLUMN($E22)+1))))




EDIT: I'm an idiot. I just took the # of columns I had, minus the function above to get what I needed.

Resources