Calc: how to chart series "slices"? [closed]

edit

Thanks for the demo, I had somewhat an idea of this sort. However, I find it impractical: to get 24 time lines in the same chart from one sample column, your scheme needs 48 helper columns which I try to avoid. Nevertheless, your solution allows for missing samples in the original column and this is valuable in many circumstances.

Any other solution without intermediate table?

There is a general shortcoming of charts: They do not accept calculated ranges. (Validation ranges can be formulae!). But even if a version of LibO to come should support calculated ranges for charts, the formulae to get selections would be rather complicated. This may be significantly different if you can assure an absolutely constant time step from one sample to the next one. In this case the selection needn't match the time but can generate the indices. Gaps forbidden!

Never mind for typos, the idea is the important thing, not the details, which anyway are different in every application.

Your intermediate table with calculated indexes is exactly what I'm looking for: it is a "constant" sized table with automatic update whenever the original data changes. I did not know the use cases of OFFSET(). Thanks.

Of course, I agree with you about chart readibility concerning number of series.

But even your overcrowded cases are interesting: they show the same global behaviour over time and can be the "early bird" for further study.

My remark about missing samples was triggered by your first implementation. I keep the tip in an obscure corner of my brain.

My original concern is simply constant-step subsampling and your second suggestion fully fits my need.

I have played a bit with your updated demo. Your "incredibly overcrowded" chart has no readability issue as long as the lines do not bear information: I have dimmed them using very thin lines with transparency 50%. I added Min and Max lines with medium width and 0% transparency and an Average think line with 0% transparency again. Color has been selected to give maximum contrast (thus highlight) against raw data. Now, information is in the 3 added lines (with background references).

@ajlittoz: You leave me curious. Would you mind to attach a realistic example of how you use the concept?

I've attached the file to the initial question. The idea is to dim enough the clutter so as not to distract attention from the main focus, but to leave it visible enough so that you can grab other properties, such as standard deviation, difference between average and median, ...