Perhaps there is a macro for this? (It's about Charts!)

[StainlessB]

I am working with a 3D scatter plot, so all data sets has 3 variables. It is necessary to move data sets around based on proximity to different centers (centroids) and as things get moved, there is a constant need to recalculate the centroid values, which is th eaverage of all values currently placed into that ‘cluster’

although I am getting quick at =average(grab a range of cells) and then copy, paste the next two rows, there’s seems there should be a better way. The number of cells will constantly vary. Any ideas?

and because the data is being used in a python coded app, I have to cope and paste as numbers, as the formulas create havoc with the python code…

thank you in advance

[Wanderer]

@StainlessB

and because the data is being used in a python coded app

Wouldn’t it be better to do all the calculations in python, instead of a macro for Calc?

[StainlessB]

There are a number of functions in the code: K-means, ∆E, data highlight, isolation, linear average and polynomial trending functions already. Initially I was working to embed the worksheet into the app, but this seems to create n endless assortment if issues. Having the worksheet formatted specifically for the app, but still being separate allows the ability to work with the data without the encumbrance of constantly updating/refreshing.

It has been a challenge to have the existing calculations save to the worksheet without issues.

[Villeroy]

References to spreadsheet ranges adjust automatically as you insert and delete cells.
In most practical use cases, you want to check Tools>Options… Calc>General>" Expand references when new columns/rows are inserted" (which is off by default).
So if your calculations refer to X1:Z20 and the new data set is 30 rows tall, insert 10 rows before dumping data to X1:Z30. All spreadsheet references in formulas, range names, charts etc. that referenced X1:Z20 will refer to X1:X30.

[StainlessB]

I will have change the setting! . I have tried leaving formulas outside the area the plot app reads, which works. However, the data is in chunks of rows, each chunk separated by a blank line. As theses change, I am sure (but I will need to check) if the new ‘top’ row, is no longer the first cell in the formula, the formula still includes the now blank cell… but perhaps this will take care of that!