CODEWITHSUNDEEP
exceldynamicoffset
roarknuppel
roarknuppel

Reputation: 269

Dynamic use of offsets excel

I got a problem about the use of offset functions in excel. Basically I got two col of consideration: First I need the calculation in col AD, so: AD = AA + AG_(+1). With AG_(+1) I mean the next coming value in col AG. Similarly I need the following calculation in col AF: AF = R + M_(-1), where M_(-1) means the previous value in col M.

Now this would be straight forward if I had values in all rows, this is however not the case. I have tons of blanks in my 6000+ rows which complicates matters for me.

I reckon you can use the offset functions, but I cant seem to get it right. Any suggestions?

A small dummy example is seen below:

enter image description here

Best

Upvotes: 1

Views: 243

Answers (1)

glh
glh

Reputation: 4972

If you use a combination of an array formula, relative referencing and the if, index, row and min formulas you'll get a costly but sufficient result.

My test data:

enter image description here

And the formula added in B1 to get the 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 the cells not blank note: you'll have to alter the 9999 to get your true outer limits or change to ROWS(C:C)+1
  2. MIN(IF(...)*ROW(C1:$C$20)) get the first non blank row
  3. INDEX($C$1:$C$16,MIN(...)) get the value on the first non blank
  4. IF(A1="","",... will remove leave blank if an cells to the left are blank

And the formula added in B6 to get the cell up:

{=A6 + INDEX($C$1:C6,MAX(IF($C$1:C6="",0,1)*ROW($C$1:C6)))}

Upvotes: 1

Related Questions