constant in formulas

[ed]

i have excel 2003 and am learning how to use it i have written a formula
and am using g4 as constant part of the formula .when i try to copy the
formulato the rest of the column g4 changes to g5, g6 ,g7 how do i keep g4 as
constant?

example the formula is =g4*a7*4
when i copy to cell directly below the formula changes to g5*a7*4

 

[Chip Pearson]

If you don't want part of a reference to change, put a $ character
before that part of the reference. Excel won't change it when you copy
it down. E.g.,

=$G$4 change neither row nor column
=G4 change both row and column
=$G4 change row but not column
=G$4 change column but not row

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[ed]

Thank You Chip you just made everything easier for me

 

[ShaneDevenshire]

Hi,

Here is a discussion from another post I made today:

As an additional piece of info:

=C3*(D2/100)
can be written
=C3*D2/100
no need for parens here. If my memory serves me division and multiplication
are said to be distributive, which means their order is unimportant, so you
don't need the parens.

Primer on relative and absolute cell references

By default almost all Excel formulas are relative - they adjust when you
copy them. For example =SUM(A1:A10) in cell A11 is copied to B11 it becomes
=SUM(B1:B11) its references are relative to the location of the formula.
You can make any portion of a reference absolute by putting a $ in front of
it. An absolute reference does not change when you copy it.

Cell references come in 4 variations:
A1 relative
$A$1 absolute
$A1 mixed - the A is absolute, the 1 is relative
A$1 mixed - the A is relative, the 1 is absolute

These ideas are a core component of Excel's formulas and everyone should
master them!

When you copy a formula down a column, in a formula reference like
=SUM(A11), the row numbers change but the column letter do not. To make
the row numbers fixed, absolute, you can modify the formula to read
=SUM(A$1$1). We might call this absolute but actually its mixed, but since
we are copying up or down a column and not left or right, all portions of the
references remain unchanging.

Why should you know this? If you write a formula and copy it and then find
you need to go back and modify it to get it to refer to the correct cells, it
usually means you don't understand the concept of absolute/relative cell
references. But more importantly you are working too hard, editing those
formulas.


If this is helpful, please click the Yes button.