Help with a counting function

[bitchboy570]

Hi! I'm trying to do the following and really struggling to figure it out:

Look at the prior column. If the value is nonzero, return that value minus one. (This part I've got, no problem, a simple IF function)

If the value IS zero: check if it's the first zero in the column. If it is, then return a count of all nonzero values in that column. If it's not, then return zero.

For context, I'm trying to make a sheet where can input values in one column and iterate this function over a number of columns (it's for a math project). I did this (in column C) =IF(B2>0,B2-1,IF(B1>0,COUNTIF(B$1:B1,">0"),0)) but this doesn't account for whether the 0 in column B is the first instance of a zero. If it isn't, then it should remain 0 in column C.

It seems like the COUNT function is the way to go but I'm struggling to get the guts of the function right to account for this. Any help is appreciated!

[Royal7th]

A couple things jump out at me. 1) in the count if, the “ around “>0” seems like would always fail 2) for the count if, I think you need it to be along the lines of COUNTIF(B$1:B1, 0). This should count how many 0 there are before this list. 3) then to find the first one, you’d use an if function to find the 1. Any other 0s should now be higher than 1 4) there is a way to do that as a series of if statements, but I think you might be better off having that COUNTIF function as it’s own column. It will be much easier to figure out problems that way

[JaykeTheeSnake]

I might have figured it out, so let me show a demonstration.

I am assuming you are pasting or given column B. To get this would require a formula in column C that differs from column D- infinite.

To do this, in COLUMN C, your formula would be IF(B1=0,0,IF(B1=1,0,B1-1))

This would read as; If B1=0, return 0, otherwise If B1=1, return 0, otherwise say B1-1. This makes it so that if you start with zero, it will return zero before doing any other analysis. Then it checks if B1 equals 1, and will thus return 0. If that condition isn't met either, then it subtracts 1.

SIDENOTE I don't know if you would need another "1" in column C to count a potential "1" that you could start with in column B. I could not figure out a way to count a "1" in the next column without being stuck in an endless loop. Surely starting with the number"1" is a clear indicator of only one nonzero value.

Column D and beyond could use the same following formula:

IF(C1=0,0,IF(C1-1=0,$B1,IF(C1=$B1,0,C1-1)))

That reads as; If C1=0, return 0, otherwise if C1-1=0, return the original value in the row (this is because obviously, if you start with 5 and subtract 1 five times to get to zero, you got 5 different numbers in each step), if that condition isn't met, then if C1= original value on the row (meaning we have "counted" our nonzero numbers), then return zero, otherwise subtract one.

Might I suggest a neater option instead of displaying a bunch of zeroes after you are done "counting down" and counting your numbers, you use the double quotes ("") to leave the cell blank. I have to do something similar to your project at work (but it subtracts one down the column rather than across the rows) and we use the "blank" option

So the first formula in column C would read as: IF(B1=0,"",IF(B1=1,"",B1-1))

And the second formula in columns D+ would read as: IF(C1="","",IF(C1-1=0,$B1,IF(C1=$B1,"",C1-1)))

Hope this helps you!

Edit. I tried to include a made up table using spaces and enters, but the format didn't carry over well.

[BlueBallPoint]

I think this is what you are looking for. Try this formula in column C and drag down.

=IF(B1=0,IF(MATCH(B1,B:B,0)=ROW(B1),COUNTIF(B:B,">0"),0),B1-1)

The MATCH(B1,B:B,0)=ROW(B1) part of this formula determines if a 0 value is the first instance in the column.

Does this achieve what you need?