Applying a formula to all cells in a column, not just one at a time?

Question

In OpenOffice.org Calc, I would like to apply a formula to a column that references a cell from the same row but in a different column. I.e., =C1*48 in cell D1, but I want all cells in column D to do this without having to copy the formula to each one manually. Or another way of saying it, for each cell in C that has a number, I want to fill in the corresponding row-cell in D with a formula value based on that number. So far, all I can see from the documentation is that I'd have to copy/adjust the formula for every new row in the spreadsheet. And since I have 4 such columns that need calculation, this will get to be tiresome. I have little experience with spreadsheets at all, so I'm hoping that my problem is actually very simple, and that I just am looking at the wrong parts of the documentation.

Answer 1

I don't have OpenOffice in front of me, but it tries really hard to be Excel like in many ways, so usually assumptions about Calc based on Excel are fairly close to reality.

In Excel, a formula in cell D1, that points to a cell in C1, is treated as a relative reference - that is, one column back from where I am now. So when that formula is filled into other cells (either by Fill Down, or dragging the little handle in the corner of the cell outline, or by copy-pasting the formula into a range of selected cells) the new formulas are similarly treated, by default, as referring to the cell that is one column back from them, in the same row.

To force a formula to use an absolute reference, one specifies the cell address with a dollar sign - $C$1 will always point to (use) the contents of cell C1, regardless of where the formula ends up. ($C1 and C$1 are alternates that allow one parameter of the address to change in the usual relative sense while fixing the other half in place... this probably isn't important to you yet).

In other words, I'd expect that you can type the formulas in the first row of your OpenOffice Calc sheet and copy them down to the rest of the row, and things will just work.

Answer 2

If you want to extend your range down you can do this by calculating the first 2 cells with your formula and then highlighting them. You now grab the little square on the bottom right of the highlighted area and drag that down, across or both.

If you have a specific cell (e.g. D2) which you wish to have remain in all the cells you extend your range to, then in your 2 initial cell calculations use the following:

Extending Down

=G2*D$2
=G3*D$2

Extending Across

=G2*$D2
=H2*$D2

Extending Down and Across

Use $D$2