Dynamic use of offsets excel -

i got problem use of offset functions in excel. got 2 col of consideration: first need calculation in col ad, so: ad = aa + ag_(+1). ag_(+1) mean next coming value in col ag. need following calculation in col af: af = r + m_(-1), m_(-1) means previous value in col m.

now straight forward if had values in rows, not case. have tons of blanks in 6000+ rows complicates matters me.

i reckon can use offset functions, cant seem right. suggestions?

a small dummy example seen below:

best

if use combination of array formula, relative referencing , if, index, row , min formulas you'll costly sufficient result.

my test data:

and formula added in b1 cell down:

{=if(a1="","",a1 + index($c$1:$c$20,min(if(c1:$c$20="",9999,1)*row(c1:$c$20))))} 

explanation:

1. if(c1:$c$20="",9999,1) find cells not blank note: you'll have alter 9999 true outer limits or change rows(c:c)+1
2. min(if(...)*row(c1:$c$20)) first non blank row
3. index($c$1:$c$16,min(...)) value on first non blank
4. if(a1="","",... remove leave blank if cells left blank

and formula added in b6 cell up:

{=a6 + index($c$1:c6,max(if($c$1:c6="",0,1)*row($c$1:c6)))}