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.