## [Solved] Formula transfer

### [Solved] Formula transfer

Formula in cell D10 is =SUM(D9/B9)-1 but when I copy using highlight and cross hairs on block in lower left corner it to cell E10 it becomes =SUM(E9/C9)-1 substituting a C for a B. B9 is a constant (base value) for that row It does this no matter how many cells I copy it to both horizontally or vertically even when I correct it in subsequent cells it changes the B to a C. . It transfers correctly for other cells i.e.=SUM(F13/E13)-1 transfers to=SUM(G13/F13)-1.
Last edited by RoryOF on Sun Nov 10, 2019 7:47 pm, edited 1 time in total.
Reason: Added [Solved] and green tick. [RoryOF, Moderator]
Open Office 3.4.1 on Windows 8
crabmann

Posts: 33
Joined: Thu Dec 27, 2012 2:34 am

### Re: Formula transfer

I do not understand why =SUM(D9/B9)-1 becoming =SUM(E9/C9)-1 is wrong but =SUM(F13/E13)-1 becoming =SUM(G13/F13)-1 is correct. In any case, if you want the B9 to remain unchanged when you copy the cell, use \$ to make the reference absolute
Code: Select all   Expand viewCollapse view
`=SUM(D9/\$B\$9)-1`

Also, the SUM() function serves no purpose in that formula. You can write
Code: Select all   Expand viewCollapse view
`=D9/B9-1`
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7484
Joined: Sat Nov 08, 2008 8:08 pm

### Re: Formula transfer

You might find [Tutorial] Absolute, relative and mixed references a useful resource.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine

robleyd
Moderator

Posts: 3140
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

### Re: Formula transfer

SOLVED!! Thank you very much. The \$ took care of the problem.
Open Office 3.4.1 on Windows 8
crabmann

Posts: 33
Joined: Thu Dec 27, 2012 2:34 am