excel averege from curent cell till previous non blank cell -

excel averege from curent cell till previous non blank cell -

to go on quest from: excel multiple average in single column

i have moved excel 2013 , have learned file has 180k rows hence suggested formula not come consideration (it uses much memory , pc crashes).

my thought of solution create column flag when product number changes:

a | b | c | d

1 | 0:17| " " |" " | 1 | 0:18 | " " | " " | . . 1| 0:19 | 0:18 | 1 | 2| 1:12 | " " | " " | 2| 1:12 | " " | " " | . . 2| 1:13| 1:12.6| 1 | 3| 0:45| 0:45.0| 1 |

and create average between 2 non blank cell.how can address of non blank cell column e? how can address of non blank cell without calculating entire column? have tried: column c:

=if(d1=1;averageif((value in)e1:d1;a1;b$1:b1);"")

it should average current cell previous non blank cell in d - lastly alter in product name

column d:

=if(a2=a3;"";1)

column e:

=address(lookup(1,1/(d:d<>""),row(d:d)),1)

to cell address of non blank cell, 1 time again calculates entire column have solved nothing.

pleas help!

in cell c1 set 1 in cell c2 set =if(a2=a1,c1+1,1) , re-create down: calculates number of consecutive occurrences of product number in cell d1 set =if(a2<>a1,average(offset(b1,0,0,c1*-1,1)),"") , re-create down offset grabs cells average lastly of adjacent block of product numbers.

this should fast calculate, downside offset volatile column d recalculate @ every recalculation.

excel