Suppose column A contains numbers and zeroes in random order.
I want to create a formula in Column B where in case the value of the Column A for the specific row is non zero, it will produce the sum of that cell in Column A plus the three non zero cells of column A above that row.
For example
Column A contains the values, the non zero being e.g. A6, A9, A10, A14
Column B will sum for example in cell B14 (which is adjacent to the non-zero value at A column), A14 plus the three non zero cells above A14, ie A6+A9+A10
confirm with CTRL+SHIFT+ENTER and copy down column
If there aren't 3 non-zero numbers above you just get blanks, see attached example where A1 has random zeroes/non-zeroes. Press F9 to re-generate random numbers
If data starts at a differnt row just change all A6 refs as appropriate
regards, barry
0
mamelasAuthor Commented:
That's what I was looking for. Thank you very much for your help.
0
Featured Post
Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.
=IF(A6>0,A6+INDEX(A:A,LARG