That method requires four steps, using two different formulae. If you don't care for that option, a single formula can work for the whole thing. Let's see, I think it would work this way for B2:
=A2/2+IF(MOD(ROW(),5)=1,0,B1)
Note what each of these pieces does:
A2/2: You want that in every row, right?
Usually you want to add to that the previous value, B1. But only usually; one time in 5, you don't want to add anything — or to put it another way, you want to add 0. So you put in an IF clause adding either 0 or B1.
But what does IF evaluate? It looks at the current row number. The MOD function divides the row number by 5 and returns the remainder; if the remainder is 1 then the row number is 1, 6, 11, 16 and so forth, which is when you want add 0 rather than the previous row.
Copy that formula from row 2 to rows 1 through whatever and it works right on my machine. There are two catches:
1) In row 1 there is no previous row; in that row the function itself shows up as an error (=A1/2+IF(MOD(ROW(),5)=1,0,#REF!)), but the result is displayed correctly. Just don't copy that formula to other rows.
2) If you ever add rows, say for column headers, then the row numbers have changed and you have to modify your IF function. A bit of a pain; maybe there's an even fancier way to do it.
|