I have thousand of rows in excel and I need to average the 2 preceding rows every time a zero is found. For example

Column1

23
12
0
32
10
0

I need the average of 23 and 12 to replace the zero found in the third row. As well as the average of 32 and 10 to replace the zero found in the sixth row. Any help would be much appreciated!

up vote 3 down vote accepted

Without using VBA this requires an extra/helper column.


Worksheet Screenshot

Enter the following formula in B2 and ctrl-enter/copy-paste/fill-down the rest of the column:

=IF(A2<>0,A2,(INDEX(A:A,ROW()-1)+INDEX(A:A,ROW()-2))/2)

See this post for tips on how to easily fill the formula down a large number of rows.

If you need the the original column modified, simply copy the second column and Paste Special as Values into the the first one.

  • This works great, thank you! However, if by chance, is it possible to average the preceding and the succeeding non zero if a zero is found? Like in the example I wrote above, it would be like (12+32)/2 for the third column. Apologies if it's inappropriate to ask sub-questions in the comment section. – Karl Jun 16 at 13:22
  • @Karl Technically sub-questions are frowned upon, but very simple ones like yours are OK. Just use ROW()+1 instead of ROW()-2. ROW() returns the current row index, so ROW()-1 is the previous row, and ROW()+1 is the next row. – robinCTS Jun 16 at 13:27

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.