Forum Discussion

BLOMCHOP's avatar
BLOMCHOP
Copper Contributor
Jun 26, 2020
Solved

Getting rid of zeros.

So I have a column of numbers in the even numbered cells some of which are zero and the odd numbered cells are blank. For instance I have the numbers 1 , 2 , 3 , 0 , 4 , 0 , 0 , 5 , 6 in cells A2 , A...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Jun 26, 2020

    BLOMCHOP 

    Like this?

    =IF(ISODD(SEQUENCE(2*COUNT(FILTER($A$2:$A$18,$A$2:$A$18<>0)))),
     INDEX(FILTER($A$2:$A$18,$A$2:$A$18<>0),
      (SEQUENCE(2*COUNT(FILTER($A$2:$A$18,$A$2:$A$18<>0)))+1)/2),
"")
mtarler's avatar
mtarler
Silver Contributor
Jun 26, 2020

BLOMCHOP  By chance are your numbers going to always be sequential except for the 0's in between the way your example is?  I'm guessing not, but if you are doing some sort of counting formula in that column it is possible.  I doubt it but if so, the formula could then be (starting in B4):

 

= IF(AND(B2<MAX(A:A),A4<>""),B2+1,"")  

 

 

 

 

Resources