Forum Discussion
mikenizzle101
Sep 29, 2022Copper Contributor
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.