Description: When using the fill down cross to fill empty cells with a simple series, calc fills with an illogical series: (screen-cap below) Steps to Reproduce: manually entered: J5 "=AVERAGE(B6:B25)" manually entered: J6 "=AVERAGE(B26:B45)" Actual Results: highlight J5:J6 and fill down: J7 "=AVERAGE(B8:B27)" highlight only J6 and fill down: J7 "=AVERAGE(B27:B46)" Expected Results: desired result would be J7 "=AVERAGE(B46:B65") etc, etc, next logical series. This can be really tedious when doing dozens (or hundreds) of calcs. Reproducible: Always User Profile Reset: No Additional Info:
Created attachment 151837 [details] screencap of undesired result
Created attachment 151838 [details] undesired result spreadsheet -- thanks
Drag formulas it's copy formulas not like fill series, in your example it works properly. Maybe not what you like, but it's not a bug. A formula like I6: =AVERAGE(OFFSET($B$1;((ROW()-5)*20)-15;;20)) can be dragged
I debated whether or not to open this as a feature enhancement or bug, but it is a bug. drag fill fails given a very basic logical series, i.e.: A1=AVERAGE(B1:5) A2=AVERAGE(B6:B10) by producing: A3=AVERAGE(B7:B11) instead of the next logical result in the series: A3=AVERAGE(B11:B15) the formula solution, while workable and appreciated, requires too advanced knowledge for 99.9% of users who will encounter this problem. It's a switch back to Office type problem.
If you are happy reopening and thinking that what you like in this particular case it's what should be, on your own, but it is not a bug.
@Eike, any comment here ?
Calc is not filling a calculated series when dragging down formulas, it's like copy&paste instead. That's also how other major spreadsheet implementations work. In the attached example, as only J8 is chosen as endpoint, it's the same as copy-pasting J6 to J8. It becomes clearer when drag filling J6:J7 down until J9, then J8:J9 are a copy-paste of J6:J7 with keeping the relative references (and thus making them point to 2 rows below). There is no calculation of relative differences between two selected formulas, and there shouldn't be as it would be utterly confusing. Furthermore things would get unpredictable if a third cell was selected that again differs from the previous difference, or if formula cells were "incompatibly" different.