[Solved] Transfer formula to adjoining cell
[Solved] Transfer formula to adjoining cell
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
Reason: Tagged [Solved]; add green tick
Open Office 3.4.1 on Windows 8
Re: transfer formula to adjoining cell
Can you upload a sample spreadsheet (not an image) that shows what you are trying to do?
How to upload a file
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
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
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
-
- Posts: 310
- Joined: Sun Sep 06, 2020 8:27 am
Re: transfer formula to adjoining cell
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.
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
Re: Transfer formula to adjoining cell
Mountaineer, That is what I do but the formula gets scrambled sometimes. What is counting?
Robleyd, I will create one and upload it.
Robleyd, I will create one and upload it.
Open Office 3.4.1 on Windows 8
Re: Transfer formula to adjoining cell
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Transfer formula to adjoining cell
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
Re: Transfer formula to adjoining cell
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
Re: Transfer formula to adjoining cell
The file I have is an Open Office spread sheet.
Open Office 3.4.1 on Windows 8
Re: Transfer formula to adjoining cell
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
Re: Transfer formula to adjoining cell
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
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
- MrProgrammer
- Moderator
- Posts: 4894
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Transfer formula to adjoining cell
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.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.
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).
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).
Re: Transfer formula to adjoining cell
Solved
Worked it out. Thank you all.
Worked it out. Thank you all.
Open Office 3.4.1 on Windows 8