[Solved] Transfer formula to adjoining cell

Discuss the spreadsheet application
Post Reply
crabmann
Posts: 39
Joined: Thu Dec 27, 2012 2:34 am

[Solved] Transfer formula to adjoining cell

Post by crabmann »

I am not a programmer and only barely get by. I'm using Win 10 and and Firefox as my browser. I have made a spread sheet where I calculate the changes of an input value of 15 items based on a start date value and on the row below the change from the previous week and the results in percent pos. or neg.. I have been dragging the formula to the adjacent column where it changes appropriately reflecting the new column and row values. My problem is that the formulas don't always transfer correctly and on rare occasions does not compute correctly. I see this when the answers are way off base and I go in and correct the formulas and format if required. My concern is when the difference is small and I don't notice it. My sheet has 52 weeks across and 3 values (input, yearly change and weekly change) in descending rows for each of the 15 items. What am I doing wrong that they do not transfer correctly?
Last edited by robleyd on Mon Mar 22, 2021 3:47 am, edited 1 time in total.
Reason: Tagged [Solved]; add green tick
Open Office 3.4.1 on Windows 8
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: transfer formula to adjoining cell

Post by robleyd »

Can you upload a sample spreadsheet (not an image) that shows what you are trying to do?

How to upload a file
Last edited by robleyd on Sun Mar 21, 2021 3:06 am, edited 1 time in total.
Reason: Edit broken link
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Mountaineer
Posts: 310
Joined: Sun Sep 06, 2020 8:27 am

Re: transfer formula to adjoining cell

Post by Mountaineer »

Some concepts:
If you "shift" a formula like =D1 to the right it will be adapted to =E1, same for down to =D2

With a $ you protect parts of your formula from adapting,
so =$A1 will always point to the first column, but the row will adapt, if you move the formula down.
=$A$1 protects both parts of the address.

If you autofill by dragging, numbers will increase, so be careful to use this, but you can prevent the "counting" by holding Ctrl-Key during/at end of the autofill.

J.
OpenOffice 3.1 on Windows Vista
crabmann
Posts: 39
Joined: Thu Dec 27, 2012 2:34 am

Re: Transfer formula to adjoining cell

Post by crabmann »

Mountaineer, That is what I do but the formula gets scrambled sometimes. What is counting?

Robleyd, I will create one and upload it.
Open Office 3.4.1 on Windows 8
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Transfer formula to adjoining cell

Post by Villeroy »

Scrambled in which way?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Transfer formula to adjoining cell

Post by RoryOF »

By "counting" Mountaineer means the adjustment of cell numbers as the formula is dragged down.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
crabmann
Posts: 39
Joined: Thu Dec 27, 2012 2:34 am

Re: Transfer formula to adjoining cell

Post by crabmann »

Robleyd, I have a file to upload but when I click on the "How To" in your reply it says file not found.
Open Office 3.4.1 on Windows 8
crabmann
Posts: 39
Joined: Thu Dec 27, 2012 2:34 am

Re: Transfer formula to adjoining cell

Post by crabmann »

The file I have is an Open Office spread sheet.
Open Office 3.4.1 on Windows 8
crabmann
Posts: 39
Joined: Thu Dec 27, 2012 2:34 am

Re: Transfer formula to adjoining cell

Post by crabmann »

Maybe i figured it out. The file "production" is attached/uploaded.
Attachments
Production.ods
(10.62 KiB) Downloaded 96 times
Open Office 3.4.1 on Windows 8
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Transfer formula to adjoining cell

Post by robleyd »

I've fixed the error in the link.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Transfer formula to adjoining cell

Post by MrProgrammer »

crabmann wrote:I have been dragging the formula to the adjacent column where it changes appropriately reflecting the new column and row values. My problem is that the formulas don't always transfer correctly and on rare occasions does not compute correctly.
I looked at your Production.ods attachment which says: When I shifted the formula from J8 to K8 it did not transfer properly. Dragging J8's formula =SUM(J6/$I6)-1 to K8 copies it and the copy should say =SUM(K6/$I6)-1, as it does. Calc transferred the formula properly. I believe it is your expectation which is improper.

Do you understand what the $ modifier preceeding I6 does? If not, you will have no success with Calc until you learn about that. Read section 8. Using formulas and cell references in Ten concepts that every Calc user should know.

Your use of the SUM function in these formulas is unnecessary. Nothing is being summed. It's a division! Read section 9. Using functions and cell ranges in that tutorial. In E8 I think you want =(E6/D6)-1, no SUM, no $, and then you can copy (drag) the formula to the right into F8:N8. Personally, I would use =E6/D6-1, no parentheses either, since divisions are performed before subtractions ("Operations" tutorial below).

In E7 I think you want =E6/$B6-1, no SUM but $. Then you can copy (drag) the formula to the right into F7:N7. You might want =E6/$B$6-1, with two $ modifiers. I don't know. It's your spreadsheet. Read the tutorial to learn the difference in meaning.

If you need any additional assistance explain what "did not transfer properly" means. Specify:
• the method you use to "transfer" the formula, if not via dragging it with the fill handle,
• the formula,
• which cell it was in,
• which cell you've transferred it to,
• what formula appeared in the cell after you transferred it, and
• what formula you expected to see instead.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Order of Operations in Calc
[Tutorial] Formula Adjustments during Copy and Move
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
crabmann
Posts: 39
Joined: Thu Dec 27, 2012 2:34 am

Re: Transfer formula to adjoining cell

Post by crabmann »

Solved
Worked it out. Thank you all.
Open Office 3.4.1 on Windows 8
Post Reply