Need help to remove formatting

English
#1

Need help to remove formatting

I have columns A, B and C. Column B are numbers like 3, 5, 7 etc.

Column A is division of each previous cell in column B 5/3, 7/5 etc. - i.e. coefficient.

Column C is average of column A - it is just one number. I used to write the average (same number) and just Ctrl+drag it down to the bottom of the sheet which I use later for additional calculations for next to be columns. Suddenly I was deleting some rows, reinstating columns and I can’t get former memory out. If I do calculations for every single cell it is ok but is not possible to do that with 10,000 rows. I tried Clear Content and Clear Direct Formatting Ctrl+M but it does not work. Basically in column C I need formula =average (r29:r9622) in all column and I can do it for each cell individually (it is impossible to do it that way for a column of 10,000 rows) and instead it gives me calculations for =average (r31:r9618).

How to get rid of former memory (calculations) in that column (C) ?

New LibreOffice x64-7.3.4.2 - Dell laptop with windows 10

Posted same question on reddit - got one help and guy thinks it is a bug in new librecalc

Thanks

#2

Is your Data|Calculate|AutoCalculate checked?

FTR: https://www.reddit.com/r/LibreOfficeCalc/comments/vmuz6i/need_help_to_remove_formating/

#8

Would you, the questioner, please explain in what way formatting is supposed to be relevant for the issue. I have no clue insofar.
In what way -exactly- did you (try to) fill the average-column with a newly entered single-cell formula. There’s no tool I would know of resulting in a
=AVERAGE(R31:R9618) based on an entered
=AVERAGE(R29:R9622) - 6 rows more included!- without any additional actions like insertion/deletion of rows.
Any kind of Fill or Copy/Paste would either respect absolute addressing for contained ranges (not occurring in the example) or leave the number of rows/columns unchanged. What happened actually?

Further guessing on behalf of contributors may not make any sense.

#9

:wink: ⁠⁠⁠⁠⁠⁠⁠⁠

#11

(To administrators:
Once again: Let me decide myself if I want to delete something I accidentally posted as an answer, and to post it again, as a comment. How should silly discourse software know better?)

Did you, @MikeKaganski, actually understand this as giving a real clue to the issue?
I would emphasize the lack in information, and confirm the need for clearer explanations. Of course, I am prepared to engage with beginner’s problems - within reason.

#12

@Lupp please don’t take my comment above as offence :wink: - I myself wrote even before:

So I agree that the description is very unclear, confusing, etc. What my comment was intended for was to help to emphasize the lines in the question that could be related to your comment - and which, if not mentioned by an answerer in some form like “I saw you mentioned this and that - but does that mean exactly A or B” - could create some frustration in a questioner, like “I wrote that already, did you try to read my description?”.

#13

Sorry if I worded my comment badly. I feel sure that you (@MikeKaganski) would never be offensive against me. :slightly_smiling_face:

#3

yes it is checked

#4

Use R$29:R$9622 so Calc will not modify the row-number.
Put the modified formula in your first row, check if result seems ok, then copy the cell. Now don’t drag down, but click the cell below your copied cell, jump to the End of your table (I mostly use Ctrl+End Keys) and select the last cell of your column while holding SHIFT-Key. Now the target area is marked, so you can paste your formula.
.

#5

While I described how to get the formula there, I don’t think it is a good idea to calculate the same value 10000 times.
Calculate once in the first row and use something like =$C$1 in the following lines.

2 Likes
#6

It could be a moving average (the task is not explained) - then it could need only first bound of the range fixed.

And to investigate the problem, a sample document is needed.

#7

Possible, but either some $ was missing in the question, or it would not be possible to Ctrl-Drag down this

#14

I think that C$29 is what is needed here.

#15

If already there are data in B39:B9622, there is no need to select the range, just double click in the fill handle:
imagen

See LibreOffice Help on AutoFill.

EDIT 06-30: Just noted that I made the example in row 39, not in row 29. So, ignore row headings in my screenshot.
EDIT 07-01: To fill with double click, pointer must be seen as a plus sign (+).

#16

Thanks for the tip. Saves hell lot of time. Anyway when I do that I get this in column C
1.38872453242189
1.38872453242189
1.38864918964147
1.38875560751328
1.38879616203106
1.38877710804991
1.38882715878018
1.3888051225868
1.38891744831447
1.38887454229718
1.38896152757763
1.3890438912406
1.3887016879857
1.38877210836512
1.38882314972982
1.38889857077244
1.38893919105349
1.38897981982096
1.38898563328591
1.38894791367807
So first 2 cells numbers 1.38872453242189 are done individually and that is what whole column should look like (that is average for column A). So it means in other cells LibreCalc uses previous calculations and Ctrl+M does not work (my opinion can be wrong).
I used first backspace with everything checked to delete previous content and that should by itself clear formulas later I tried Ctrl+M and both - it seems it just does not work properly any more in this version of LibreCalc.

#19

What you write looks like you need a constant. Then you could use what @Wanderer suggested; but on the other hand, why not use $C$1 in all your formulas in column A, and not try to have such a whole column C? (as a follow-up, that cell could become a named range or named formula, to make its use in the formula more telling)

#17

I never experienced this problem before - have been working same calculations with LibreCalc for about 2 years - so guy on reddit is probably right - this is probably very nasty bug in newest version I am using now.
I will try to check all these recommended solutions for a few days and maybe downgrade to previous version is the best solution. Previous version worked just like all other versions before - just flawless. Really appreciate your suggestions and help guys.

#18

If there is a bug, please provide specific steps how to reproduce - either from scratch, or using a sample document. Like “in this file, select these rows, use this menu, click this button, save to this format, now check this”… Without such a description, there is no way to fix.

#20

OK it seems =AVERAGE (R$29:R$9618) on the top of highlighted column worked - only double click did not work (I probably did something wrong) so had to ctrl+drag it down but 1.38872453242189 is there in the column all the way down.
Thanks a lot guys really appreciate for patience with my problem.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.