I want to replace zeros with the number that was previously before it. so in column D here there should only be 80’s then change to 78’s and in column E There should only be 83’s.

These numbers are massively variable and I have 15,000 rows. I can do this in VBA but needs to be done with only Excel built in functions.

Any ideas how?

24/06/2011 18:05 0 83
24/06/2011 18:05 0 83
24/06/2011 18:05 0 83
24/06/2011 18:05 0 83
24/06/2011 18:05 0 83
24/06/2011 18:05 80 0
24/06/2011 18:05 0 83
24/06/2011 18:05 0 83
24/06/2011 18:05 78 0
24/06/2011 18:05 0 83
24/06/2011 18:05 0 83
24/06/2011 18:05 0 83
24/06/2011 18:05 0 83
24/06/2011 18:05 0 83
24/06/2011 18:05 0 83

I’ve got about 4 hours of doing naff all at work, so could maybe help, but I have no idea what you mean. Any chance of rewording the question?

why can’t you use VBA?

So if the data are in column E, use a function in column F, starting at F2 (Set F1=E1, but if E1 is zero what do you do?)

=IF(E2=0, F1, E2) (C&P down the column)

to define column F which will be the last non zero value in column E

Is that it?

if you mean replace “0” with the last positive integer in the row above then in column F, =IF(D2=0, F1, D2)

Got it now guys. OFFSET was the function I needed not heard of that one before..

=IF(d15366=0,OFFSET(f15366,-1,0),d15366)

note that offset uses lots of memory compared to a single “if” statement.