There are times when I receive a large data dump file and for various reasons, the information contains large numbers of zeros mixed in with the population and I want to exclude those zero values from my analysis.

I have used this feature so many times, I almost posting here so I can refer back to this useful formula myself.

There are times when I receive a large data dump file and for various reasons, the information contains large numbers of zeros mixed in with the population and I want to exclude those zero values from my analysis.

For example, if I wanted to trend out cumulative bank fees collected during the month, but fees aren’t collected on weekends and bank holidays, then the data file I would receive would have those fee amounts with zeros representing what was collected on Saturdays and Sundays (for example).

Assuming those fees are listed in cells A1:A180 and I want to show the fees collected in the order that they appear in Column B, I would use:

=IF(ROWS(B$1:B1)>COUNTIF(A$1:A$180,”>0″),””,INDEX(A$1:A$180,SMALL(IF(A$1:A$180>0,ROW(A$1:A$180)-ROW(A$1)+1),ROWS(B$1:B1))))

entered as an array (CTRL+SHIFT+ENTER).

The end result is this: