Excel - HOW DO I MULTIPLY MANY CELLS BY A CONSTANT CELL ?

Asked By FROGGYDOLL on 15-Feb-08 01:56 PM
I HAVE A COLUMN OF 200 CELLS THAT I WANT TO MULTIPLY BY A CONSTANT CELL.

HOW DO I LOCK THE CONSTANT CELL IN THE FORMULA SO WHEN I COPY IT DOWN THE
COLUMN THAT CELL STAYS THE SAME ?

EXAMPLE: THE CONSTANT CELL IS H7, WHEN I COPY THE FORMULA FROM THE FIRST
CELL (A1*H7), I WANT THE 200 CELLS TO FOLLOW HAVING THE SAME CONSTANT CELL  -
B1*H7, C1*H7, D1*H7, E1*H7, ......




T. Valko replied on 15-Feb-08 02:05 PM
Try it like this:

=A1*H$7

The $ "locks" the row number.

Likewise, if you were copying across a row =A1*$H7, "locks" the column
letter.

$H$7 "locks" both column and row.

--
Biff
Microsoft Excel MVP
JP replied on 16-Feb-08 09:10 PM
Please turn off the caps lock, it looks like you are SHOUTING.

If you want to multiply a column of cells by a single cell, simply
change the formula to an absolute reference.

i.e. A1*$H$7

When you fill this down/across, it will change to B1*$H$7, C1*$H$7,
etc.


HTH,
JP

On Feb 15, 1:56=A0pm, FROGGYDOLLY
=A0-
kbackman replied on 15-Feb-08 02:06 PM
When you enter the formula you can make the reference to H7 abslolute by

Either typing $H$7 or by typing H7 and pressing the F4 key.

Then, when you copy the formula the cell reference doesn't change.

The $ before the column letter says don't adjust the column reference and
the $ row number says don't adjust the row reference.

Look up Absolute References in Help for a more detailed explanation.
--
Kevin Backmann
FSt replied on 15-Feb-08 02:07 PM
hi
use absolute references ie
not H7
use $H$7
or
=(A1*$H$7)

regards
FSt1
Roni replied on 15-Feb-08 02:08 PM
Use the dollar sign to freeze the column ($H7), the row (H$7), or both ($H$7)
demechani replied on 15-Feb-08 02:08 PM
=A1*$H$7
The dollar sign will "fix" it, ie make the cell ref absolute

Btw, pl don't use all caps. Hard on the eyes, and considered impolite by many.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
Gord Dibben replied on 15-Feb-08 02:40 PM
You say you have a column of 200 cells but then you describe multiplying cells
across row 1(A1, B1, C1 etc.) by a constant in H7

Which is it?  A column or a row.?

Whatever the case........If a one-time operation enter the constant number in an
unused cell.

Select and copy.

Select the range to of cells to multiply and edit>paste special>multiply>ok>esc.


Gord Dibben  MS Excel MVP

On Fri, 15 Feb 2008 10:56:03 -0800, FROGGYDOLLY
FROGGYDOLL replied on 15-Feb-08 03:19 PM
KEVIN,

THANKS SO MUCH.  I WAS DRAWING AN "ABSOLUTE" BLANK ON HOW DO IT.  NOW THAT
I'VE READ YOUR RESPONSE - I REMEMBER.

THANKS,

MICHELLE
FROGGYDOLL replied on 15-Feb-08 03:21 PM
THANKS !
FROGGYDOLL replied on 15-Feb-08 03:24 PM
THANKS SO MUCH.

MICHELLE
T. Valko replied on 15-Feb-08 04:16 PM
You're welcome!

--
Biff
Microsoft Excel MVP
Nic Gur replied on 12-May-09 09:50 AM
You are required to be a member to post replies.  After logging in or becoming a member, you will be redirected back to this page.
t c replied to T. Valko on 22-Apr-10 10:18 AM
thanks for the tip