How to increment a formula by 1

[samisiddiq]

Hi Guys,

I would really really appreciate your help regarding a problem i am facing.
So this is my current formula

=B2/(1+$I$11)^1

I want to increment the "^1" by 1 for every single cell going down a column.

So for example i would like the formula to change to the following going down the column :

=B3/(1+$I$11)^2
=B4/(1+$I$11)^3
=B5/(1+$I$11)^4
=B6/(1+$I$11)^5

However excel does not change the last number when i drag down, it keeps the following

=B3/(1+$I$11)^1
=B4/(1+$I$11)^1
=B5/(1+$I$11)^1
=B6/(1+$I$11)^1

Any idea how i can make it also increment the last digit ??
Your help would be truly appreciated.

[Olly]

Try:

Please Login or Register  to view this content.

[samisiddiq]

Wow that works.
thank you sooooo much :D
no idea how you did that though

[Olly]

It just references the cell row number, which obviously increments by one each row you go down...

[samisiddiq]

Is there any chance you could please help me out with the problem below too ?

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.