Singletrack Mountain Bike Magazine

• Overview
• Bike Forum
• Chat Forum
• For Sale
• Wanted
• Forum Help
• Classified Rules

Chat Forum

1. soma_rich - Member

   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

   Posted 10 months ago #
2. The Flying Ox - Member

   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?

   Posted 10 months ago #
3. footflaps - Member

   why can't you use VBA?

   Posted 10 months ago #
4. thepurist - Member

   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?

   Posted 10 months ago #
5. Stoner - Member

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

   Posted 10 months ago #
6. soma_rich - Member

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

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

   Posted 10 months ago #
7. Stoner - Member

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

   Posted 10 months ago #

RSS feed for this topic

Reply

You must log in to post.