[Solved] Formula transfer

Discuss the spreadsheet application

[Solved] Formula transfer

Postby crabmann » Sat Nov 09, 2019 8:31 pm

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

Postby FJCC » Sat Nov 09, 2019 9:13 pm

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
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7484
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Formula transfer

Postby robleyd » Sun Nov 10, 2019 12:23 am

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
User avatar
robleyd
Moderator
 
Posts: 3140
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Formula transfer

Postby crabmann » Sun Nov 10, 2019 7:45 pm

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


Return to Calc

Who is online

Users browsing this forum: jhoward1950, Villeroy and 11 guests