Here is the following table: So far I have tried:

=Average(OFFSET($A2,0,COUNT($A2:$A5642)-1,1,0))

but I am not

CodePudding user response：

InB2 use: =IFERROR(AVERAGE(INDEX(A:A,MAX(ROW($A$1:A1)*($A$1:A1="")) 1):A1)/(A2=""),"")

Enter with ctrl shift enter in Excel versions prior to Office 365.

CodePudding user response：

Doing things step by step makes it a lot easier (and clearer for anybody else in the future)

Add a column (let's say C) which has: in C2: =if(a2='',0,1)
And D in D2: 1 in D3: =if(a3='',d2 1,d2) and drag it down

Now you have some choices.
I would do a Pivot Table:
In C1 put Inclusion, in D1 put Group; In the Pivot table: Report Filter: Inclusion (=1) Row labels: Group Values: Purchase Values (and select Average)

Alternatively: in E1 put Item, E2 put 1, E3 =IF(A3="",0,E2 1) and copy down in F1 put Running, F2 put =A2, F3 put =IF(A2=0,A3,F2 A3) in G1 put Average, G2 put =IF(E3=0,IF(E2=0,"_",F2/E2),"_")

CodePudding user response：

If you have Excel 365, you can use Xmatch or Xlookup to do a reverse search, e.g.

=IF(OR(B1<>"",A2<>""),"",AVERAGE(A2:XLOOKUP(TRUE,A$1:A1="",A$1:A1,A$1,0,-1)))