Average groups of non-zero cells

[Markeus]

Hi,

I'm sure this has been asked before in some way but I wasn't sure how to search it...

I have a column of data which has intermittent non zero values. I would like to average the groups of non zero values and need a dynamic formula for this as the rows of data are many. here is an example of the data:

0
6
5
4
0
0
6
5
4
3

<tbody>
</tbody>

I would like to average the first 6,5,4 and then the next 6,5,4,3 in a second column. I have 20k rows of data in reality and there is no pattern to how many rows appear in a grouping.

Thanks for any help.

 

[Damo71]

Hi Markeus

I am by no means an expert, but I think you might be asking the impossible from a formula, and you probably need vb code to do what you are asking.

But to clarify, you are saying that all the data you want is in a single column? (i.e. you have numbers (including zeros) in, say, A1:A20000?)

If so, where would you want the results returned? In column B? And then, would you want the result to appear next to every cell that has a non zero value? Or just next to the first non-zero value in each group?

Finally, is there any other data available that might help us? For example, if each grouping of numbers represents figures for a single month or day, maybe a pivot table would be a good solution.

For me at least it was hard to understand what you're asking for. Sometimes explain why helps.

 

[barry houdini]

With data starting at A1 try this formula in B1 copied down to give an average on the last row of each group

=IF(AND(A2=0,A1<>0),AVERAGE(A1:INDEX(A$1:A1,IFERROR(MATCH(2,INDEX(1/(A$1:A1=0),0)),0)+1)),"")​

​

 

[Damo71]

That's amazing. I'm going to delete my answer! (Oh, I can't)

 

[Markeus]

Thanks barry! this was super helpful..

 

[Markeus]

I have one request for a tweak to the formula barry...
I'd like to now average the data on a second column beside the "A". This column has data continuously and no zeros, but I still want to average only the data beside a group from the A column. for example

0	9
0	8
6	7
5	6
4	5
0	4
0	3
6	2
5	1
4	2
3	3
0	4
0	5

<tbody>
</tbody>

Now I would like to average the 7,6,5 in Column B beside the first grouping in Column A and then the 2,1,2,3 beside the second grouping of 6,5,4,3... and so on.