Excel 2003

[Guest]

I have limited knowledge of Excel but I have a simple question.

How do I apply a formula to an entire column? I want to enter a number into
columns C and D and have it automatically multiply the numbers and insert it
into column E. I figured out how to insert a formula into column E that will
multiply D by E but I have to specify the exact row number each time I enter
the formula. I might as well do the math myself as re-write the formula on
each line in column E. How do I get Excel to automatically complete column E
when I enter the numbers into columns C and D?

(this is hard to describe...I hope it's easier done than said)

 

[Guest]

Hi,

Enter the formula blow in the cell E1 and then copy drag down the formula to
whereever you need:

=IF(AND(ISBLANK(C1),ISBLANK(D1)),"",C1*D1)

Thanks,

 

[Peo Sjoblom]

It will do this automatically in Excel 2003 as long as formulas appear ion 3
of the last 5 rows so you only have to do this manually for 4 rows, when you
enter numbers in the 5th row in C and D E will automatically add the result
as a formula. For this to work you need to make sure
"Extend list formats and formulas" is checked under tools>options>edit

Of course you can change this formula

=C2*D2


to



=IF(COUNT(C22),C2*D2,"")


and copy down the formula a couple of thousands of rows but I would go for
the first option

 

[Guest]

Another way of quickly filling a range. Enter your formula into a cell in
column E (let's presume E2, and formula looks something like =C2*D2).
in the 'Name Box' type E2:E150 [Enter]

The Name box is that area above the blank cell above the 1 row number and
left of the A column letter. It usually shows the address of the cell you
have selected. When you type E2:E150 and press the [Enter] key, cells E2:150
will become selected. Then go to Edit, choose Fill and then choose Down and
E2:E150 will fill with the formula, all adjusted for each row.

 

[Guest]

Thank you Farhad, I got it to work. It does seem like a rather complicated
formula for such a simple task but it works and that's what I needed.

Chuck

 

[Guest]

Hi,

if you put =C1*D1 it works but if column C and column D both would be blank
it show 0 and i thought you may not like to see zeros that is why i put AND
condition inthe formula. glad to hear it worked for you.

Thanks,

 

[Guest]

THANK YOU ALL - you have been most helpful

 

[Peo Sjoblom]

If you look at my answer you will see it is not complicated, all it takes
are 5 rows of manually entered formulas, the rest will be automated