Ubuntu
openoffice.org package

using 'fill' and the '$' with formulas in calc

Asked by WillH

It's been a while but to my recollection you could use the $ symbol in excel placed before a cell identifier (e.g. $A1) so that when you dragged that formula to other cells the cell identifier with the $ prefix would not change. The $ doesn't work in open office calc and I'm wondering if there is some other symbol I can use so that the cell identifier remains constant when I drag the formula to other cells.

To be clear, I want to write out a formula =((Q13-Q9)/Q13)*Q9 and then I want to repeat that formula for the three cells below it but I need Q13 to remain constant in all the calculations. When I drag this formula downward all the cells increase by one (e.g. =((Q14-Q10)/Q14)*Q10 ) but I want the Q13 to remain constant. In other words I want the formula for the next cell down to read as follows: =((Q13-Q10)/Q13)*Q10

Question information

Language:
English Edit question
Status:
Solved
For:
Ubuntu openoffice.org Edit question
Assignee:
No assignee Edit question
Solved by:
Tony Pursell
Solved:
Last query:
Last reply:
Revision history for this message
Best Tony Pursell (ajpursell) said : 		#1

I think you need $A$1, not just $A1

Tony

Revision history for this message
WillH (whauser) said : 		#2

Fantastic, you're right. Thank you.

Evidently the cell identifier consists of two parts, the column and the row. The $ prefix keeps constant whatever it precedes. So to keep the row constant you'd need to put the $ before the row identifier (the number) and to keep the column constant you'd need it before the column identifier (the letter). To keep both constant - to always refer to that particular cell you'd need a $ before both the letter and number.

I say all this in case someone else has the same question sometime in the future.

Revision history for this message
WillH (whauser) said : 		#3

Thanks Tony Pursell, that solved my question.