Average of Count of Positive Values in a Row - with Gaps

[cena1987]

Hi all

If I want to find the average of the count totals of consecutive positive values in a row, with gaps of zeros, what formula could I use? An example would be:

1 1 1 1 2 2 2 3 1 1 0 0 0 0 0 0 0 1 2 5 3 3 2

There are two consecutive rows of positive values within the row. The first is 10 values long, the second is 6 numbers long. I'd therefore like the answer to be 8 (10 + 6 / 2).

Any help would be much appreciated.

Thanks

Tom

 

[Marcelo Branco]

Are the numbers in different cells? If so, what cells?

M.

 

[cena1987]

Are the numbers in different cells? If so, what cells?

M.

Hi Marcelo

Yes, each number is in an individual cell on the same row.

Thanks

Tom

 

[Marcelo Branco]

Assuming numbers in A2:W2 try

Array formula
=SUM(FREQUENCY(IF(A2:W2>0,COLUMN(A2:W2)),IF(A2:W2=0,COLUMN(A2:W2))))/SUM(IF(FREQUENCY(IF(A2:W2>0,COLUMN(A2:W2)),IF(A2:W2=0,COLUMN(A2:W2))),1))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[XOR LX]

Alternative, also with CSE:

=AVERAGE(IFERROR(1/(1/(FREQUENCY(IF(A2:W2,COLUMN(A2:W2)),IF(A2:W2=0,COLUMN(A2:W2))))),""))

Regards

 

[cena1987]

That worked perfectly, thanks very much Marcelo

 

[cena1987]

Alternative, also with CSE:

=AVERAGE(IFERROR(1/(1/(FREQUENCY(IF(A2:W2,COLUMN(A2:W2)),IF(A2:W2=0,COLUMN(A2:W2))))),""))

Regards

Thanks!

 

[XOR LX]

Wait, I think we can use something even simpler (and no CSE).

Assuming X2 is unoccupied:

=COUNTIF(A2:W2,">0")/SUM(COUNTIFS(A2:W2,">0",B2:X2,{0,""}))

Regards