Repeat formula 5 times and repeat?

[Jenny345]

Hi
Trying to figure out if it's possible to repeat the same formula 5 times on a range of cells, and then restart the formula 5 times on the following 5 cells and then again...

E.g.

I have a cell range from A1 to A20

I want the formula to run in B1 to B20

E.g. of how the formula will run over 5 cells:

First in B1: I want: A1 / 2,
Then in B2: A2 / 2 + B1,
Then in B3: A3 /2 + B2,
Then in B4: A4 /2 +B3,
Then in B5: A5/2 +B4.



Then I want the whole shebang to repeat its self over the next 5 cells etc.

Not sure if this is possible?

Jenny

[Pecoflyer]

Hello
could you please define the word "shebang" please?
Please give an example of the repetition you need, there are various ways to interpret your request

[Jenny345]

Hi

Thanks for getting back to me.
Shebang - 'The whole lot'

So,

I have data spanning across cells A1 to A20.
I want the data in cell A1 to be divided by 2.
Then I want to divide the data in cell A2 by 2 and add the answer from A1 (A1 divided by 2) to it...

-A1 / 2 - Calculation to appear in Cell B1
-A2/2 + A1 - Calculation to appear in Cell B2
-A3/2 +A2 -Calculation to appear in Cell B3
-A4/2 +A3 -Calculation to appear in Cell B4
-A5/2 +A4 - Calculation to appear in Cell B5

repeat...

-A6/2 -Calculation to appear in Cell B6
-A7/2 + A6 -Calculation to appear in Cell B7
-A8/2 + A7 -Calculation to appear in Cell B8
-A9/2 + A8 -Calculation to appear in Cell B9
-A10/2 + A9 -Calculation to appear in Cell B10

repeat...

I hope that this explains it a little bit better

Jenny

[BobBridges]

Well, the simple way to do it (it seems to me) would be in four steps:

1) In B1: =A1/2
2) In B2: =A2/2+B1
3) Copy the formula in B2 to B3:B5

Now you have the first block of five. You don't have to repeat all three steps throughout the rest of the worksheet; instead, just do this:

4) Copy the block B1:B5 to B6:B20 (or whatever).

[BobBridges]

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.