OpenOffice Calc - calculating percentage for every cell in a column

Question

I have a somewhat common task of calculating percentage of column. For example I have a column with 20 numbers on each row. I add 21st row with formula for the sum of all those 20 rows and then I need to add another column with formula like =A1/A21. The only problem is to copy/paste this formula 19 times. If I just create one cell with this formula, copy it, select 20 rows and paste - it will create formulas like =A2/A22...=A20/A40, but I need to divide only by one cell - A21. So what I have to do right now is to go through every cell and change the divisor in every single one of them. It's ok if there's 20 of rows, but what if there would be hundreds of them? Does anyone know a way to solve my problem?

Answer 1

When you want to create a formula that can be copied to other locations and retain the reference to the row or column, you use the $ to anchor it. In your example, you want to copy down the column and keep pointing to row 21, so you would use:

    =A1/A$21

If your data was horizontal and you wanted to copy across columns, you would put the $ in front of the column letter. If you have something like an input cell and you want the reference to point to that cell regardless of where the formula is copied, prefix both the column letter and row number with $, like:

    =$A$1