array product formulas

S

Smokeyhavoc

I have a colum of data almost 2500 entries long, what I want Excel to do is
take each cell and multiply it by a given value and then display it in a
corresponding cell
for instance:
colum d
BV
197
99
213
2465
886
1478
156
take each value and multiply it by 1.05 for:
colum f
ADJ BV
206.85
103.95 and so on down the colum

ANY help on this would be greatly appreciated as it would more than likely
save me several hours worth of tedious work
 
B

Biff

Assume your numbers in column D start in D2. Enter this formula in F2 and
copy down as needed:

=D2*1.05

Biff
 
S

Smokeyhavoc

doing it the way your saying would require me to do it in each cell, for
almost 2500 entries. I was looking for a formula to do each one indivually,
but the whole colum in one fel swoop. Reason being is I need to do this
several times with other factors with the same base data
 
J

JMB

Nope. He means type it for the first cell then copy (emphasis on copy) for
the remaining 2,499.

After you enter the formula, select the cell w/the formula and place the
mouse over the lower right corner. When it becomes a small black cross,
click the left mouse button and drag the formula down your column. Then let
up on the mouse button.
 
B

Biff

You don't type in a formula for each of 2500 cells. You type in 1 formula
and copy it to 2500 cells. It takes about 2 seconds!

For example, if the range for the formulas is F2:F2500, type the formula in
F2.

Select cell F2 and goto the menu Edit>Copy.
Hit function key F5
Enter F3:F2500
Hit OK
Goto the menu Edit>Paste

Or, you can just "grab" the fill handle with the mouse and drag copy the
formula down the column. This might take 20 seconds. The fill handle is the
little black square in the lower right corner of the selected cell. When you
hover the mouse over the fill handle it will change from a "fat" + to a
"skinny" + . Then just "grab" it with the right mouse button and drag.

Biff
 
A

Alan Beban

What Biff meant was that after entering the formula in F2, fill down as
needed. I.e., put the cursor on the lower right corner of Cell F2, click
and drag down column F as far as needed.

Alan Beban
 
S

Smokeyhavoc

I tried what you said and it didn't work the first time, then after a little
reading into it I figured out what you meant. thanks for the help
 
B

Biff

Then just "grab" it with the right mouse button and drag.

I meant grab it with the LEFT mouse button although you can also do it with
the right button but then you have another menu to go through when let up.

Biff
 
R

Roger Govier

Hi

Just to add to what has already been said, dependent upon your version
of Excel, instead of holding the fill handle and dragging down, giving a
double left click on the fill handle, will fill down the column for the
extent of data in the column to the left.

Also, in your second post you said
I need to do this several times with other factors with the same base
data
Click to expand...

If that is the case, you would be better placing your factor in another
cell e.g. G1 than make the formula
=D2*$G1
and fill down as described.

Now you can just change the value in G1 to obtain your other results.
 
Top