constant cell

  • Thread starter Thread starter Rhys
  • Start date Start date
R

Rhys

Assume an investment sum.
Assume two columns.
Column A has a list of monetary figures increasing in
value as they go down the column.
Column B is a percentage of the increases the cells in
Column A.

For example Cell A would have value of $5000.00
Cell A2 $5140.00
Cell lA3 $5216 and so on own the column

Cell B1 would show a nil value as it is related to Cell A1
Cell B2 would show a % value of % value of $140.00 as it
related to Cell A1
Cell B3 would show a $216.00 as it related the Cell A1

In other words Cell A1 must be related to all the cells
in Column B.

I want to set the formula in Cell B2 so that when I drag
down the formula it reflects Cell A1 right down the
column. I have an idea the $ sign or # sign is inserted
somewhere primary cell (B2)

How is this done?

TIA
 
Rhys said:
Assume an investment sum.
Assume two columns.
Column A has a list of monetary figures increasing in
value as they go down the column.
Column B is a percentage of the increases the cells in
Column A.

For example Cell A would have value of $5000.00
Cell A2 $5140.00
Cell lA3 $5216 and so on own the column

Cell B1 would show a nil value as it is related to Cell A1
Cell B2 would show a % value of % value of $140.00 as it
related to Cell A1
Cell B3 would show a $216.00 as it related the Cell A1

In other words Cell A1 must be related to all the cells
in Column B.

I want to set the formula in Cell B2 so that when I drag
down the formula it reflects Cell A1 right down the
column. I have an idea the $ sign or # sign is inserted
somewhere primary cell (B2)

How is this done?

TIA
Click to expand...

You use $A$1 instead of A1.
$A$1 is called an absolute reference whereas A1 is a relative reference.
Your formula in B2 may be something like =(A2-$A$1)/$A$1
When you copy this down to B3 it will become =(A3-$A$1)/$A$1
The relative reference changes but the absolute references do not.

You can type in the $ signs from the keyboard. Alternatively, if you are
constructing the formula by navigating to a cell to enter its reference,
pressing F4 immediately after navigating to the cell will change the
reference from relative to absolute.

It's also worth mentioning that there are mixed references, $A1 and A$1
(that come up if you keep pressing F4). As you might imagine, $A1 does not
increment when copied across, but does increment when copied down. The
reverse is true for A$1.
 
-----Original Message-----


You use $A$1 instead of A1.
$A$1 is called an absolute reference whereas A1 is a relative reference.
Your formula in B2 may be something like =(A2-$A$1)/$A$1
When you copy this down to B3 it will become =(A3-$A$1)/ $A$1
The relative reference changes but the absolute references do not.

You can type in the $ signs from the keyboard. Alternatively, if you are
constructing the formula by navigating to a cell to enter its reference,
pressing F4 immediately after navigating to the cell will change the
reference from relative to absolute.

It's also worth mentioning that there are mixed references, $A1 and A$1
(that come up if you keep pressing F4). As you might imagine, $A1 does not
increment when copied across, but does increment when copied down. The
reverse is true for A$1.
Click to expand...


Thank you Paul. Exactly what I wanted.

Rhys
 
Back
Top