Excel spreadsheet

[wallsall]

In Excel spreadsheet I want to make one cell 'constant'. How is this done please?

Thanks.

Wallsall.

[phkninja]

explain what you mean, do you want it to have a constant value or use it as a constant reference (like a variable that will be called by numerous equations).

if its the latter you can name the cell and use the cell name (so if you insert rows or coulmns the equations will still function. if you use a cell name like I3 etc in an equation, the functionality is broken when yuo change the sheet layout in any manner)

[wallsall]

Thanks, I just want the cell to have a constant value. For instance I want the cell to have a value of 10 and to remain at 10 when all all cells are referring to it.

Wallsall.

[jaclaz]

Sorry, I don't get it.

In a spreadsheet cell you can input either a formula, something like:

=3*4*A12

or a value, for example:

10

or :

=10

First one is a "variable" as the result depends on value of cell A12, the latter two are "constants" they will have 10 as result no matter what you change in other cells.

jaclaz

[wallsall]

Sorry to be so confusing, I will try again.

Cell A1 contains the number '10'.

Cell A2 contains the number '20'.

My formula in cell A3 is '=A2+A1'

But if I now scroll down the column the next cell would add A3+A2 and so on.

I don't want this, I want each cell to add A1 all the time.

Eg:

A2+A1

A3+A1

A4+A1

A5+A1

A6+A1

etc

etc

etc.

Hope you can help.

Thanks.

[phkninja]

make the equation

=A2 + $A$1

this makes A1 static. As i said before you are better yto name the cell, so you select the cell and highlight its name in the bar at the top. Then type in a name for it e.g. Constant. Now the equation is

=A2 + Constant

[jaclaz]

Yep.

for reference say you have on B2 the formula:

=A1

is called "relative" addressing, when copied to C3 it will become =B2

the way spreadsheets see the above is "point to the cell one column left and one row above"

=$A$1

is "absolute" addressing, when copied to C3 it will remain =$A$1

the way spreadsheets see the above is "point to the cell in first column and in first row"

There are two other ways:

=$A1

is absolute column/relative row, when copied to C3 it will become =$A2

the way spreadsheets see the above is "point to the cell in first column and one row above"

=A$1

is relative column/absolute row, when copied to C3 it will become =B$1

the way spreadsheets see the above is "point to the cell one column left and in first row"

In Excel, when you have the cell address highlighted, press the F4 key, it will cycle through the four possible ways of addressing each time you press it.

jaclaz

[wallsall]

Thanks a lot lads!

I've got it now and you been a great help.

Wallsall.