Search code examples
arraysexcelexcel-formulaworksheet-function

Average of dynamic row range

I have a table of rows which consist of zeros and numbers like this:

A   B   C   D   E   F   G   H   I   J   K   L   M   N
0   0   0   4   3   1   0   1   0   2   0   0   0   0
0   1   0   1   4   0   0   0   0   0   1   0   0   0
9   5   7   9   10  7   2   3   6   4   4   0   1   0

I want to calculate an average of the numbers including zeros, but starting from the first nonzero value and put it into column after tables end. E.g. for the first row first value is 4, so average - 11/11; for the second - 7/13; the last one is 67/14.

How could I using excel formulas do this? Probably OFFSET with nested IF?

Solution

  • This still needs to be entered as an array formula (ctrl-shift-enter) but it isn't volatile:

    =AVERAGE(INDEX(($A2:$O2),MATCH(TRUE,$A2:$O2<>0,0)):$O2)

    or, depending on location:

    =AVERAGE(INDEX(($A2:$O2);MATCH(TRUE;$A2:$O2<>0;0)):$O2)