# How to change a number after every fifth cell

1. ## How to change a number after every fifth cell

Hi Guys,

So i have to create a column of numbers, where every sixth number needs to take the number above it and get multiplied by a cell that is fixed. After that this number needs to continue to be the same for 5 cells and then follow the exact same procedure as before.

So current formula looks something like this but now it wont automatically do that for the every sixth cell.

1
1
1
1
1
=\$B\$6*\$F\$2
=\$B\$6*\$F\$2
=\$B\$6*\$F\$2
=\$B\$6*\$F\$2
=\$B\$6*\$F\$2
=\$B\$11*\$F\$2
=\$B\$11*\$F\$2
=\$B\$11*\$F\$2
=\$B\$11*\$F\$2
=\$B\$11*\$F\$2

1
1
1
1
1
5
5
5
5
5
25
25
25
25
25

In essence the theory is that the rent of a house increases every 5 years by 5% or 10% or whatever.
I need to do this for a **** load of years but cannot figure out if there is any way i can do this automatically and not manually.
Your help would be really appreciated.  Register To Reply

2. ## Re: How to change a number after every fifth cell

I find that a big part of spreadsheet programming is figuring out the right mix of relative and absolute referencing. I cannot be sure exactly what your references are. If I assume that your constant multiplier is F2, and column B is the column of interest, I would:

1) Select B6:B10, type 1, ctrl-shift-enter to enter that 1 in all 5 cells
2) In B11, enter =B6*\$F\$2
3) copy B11, select B12:B15, paste. (B15 will be =B10*\$F\$2)
4) select B16:B20, paste again.
5) steps 3 and 4 illustrate how this mix of relative and absolute references works. Once you understand what is going on, then select the desired cells in column B and paste again. You should be done.  Register To Reply

3. ## Re: How to change a number after every fifth cell

This is a simple single formula that should help

The following example uses the MOD function. In this example, you must begin on row five, because you cannot calculate rows that don't exist, since you're attempting to calculate the five rows above the formula.

So the formula here will only make the calculation if it satisfies the criteria of being a MOD of 5. that is, on ever 5th row do the calculation.

1. In your spreadsheet, go to Column A Row 5
2. Type in the start number (e.g 1)
3. In row 6 enter the formula
4. Formula:  `Please Login or Register  to view this content.`
5. Fill down

You can change the MOD criteria as required, but hopefully this will give you a good starting idea.  Register To Reply

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1