Ask Your Question

Dynamic Graph in Calc?

asked 2016-01-11 17:44:50 +0200

LibreGuy gravatar image

Hi guys,

I'm wondering if it possible to create a dynamic graph in Calc. Something that looks like this:

image description

I will explain a bit more based on a simple example (not the exact case I need it for, but easier to understand). In the image above you see several categories like "running" and "walking".

Now here's the deal. In my Calc spreadsheet file there a 2 different tabs/sheets. In the first tab I want to register what I'm doing during the day. In real life I want to register my business activities, but to keep things simple let's stick to the example of physical excercise as seen in the image.

So in the first sheet I want to register what I'm doing and for how long. So for example, column A contains the activity, B the start time, C the end time and D the calculated period of time I have been excercising. For example, something like this:

        A        B        C        D
1   activity   start     end     time
2   running    13:10    13:25    00:15
3   walking    13:45    14:00    00:15

The amount of rows will have a maximum of 5.000.

Now what I'm wondering is if it is possible to dynamically generate a horizontal graph (like the image) on the second sheet that shows the percentage (not the amount of time) I have spent on each activity?

So ... based on the input of the 2 rows above, the graph should show 2 horizontal bars. Since I have spend 15 minutes on each activity both bars should be of equal length and indicating a percentage of 50%.

Also ... when I would add another category in column A, for example tennis, this should automatically show up in the graph as well.

I hope my explanation is understandable (English is not my mother language). If not, please say so.

Thank you for taking the time to read this, and any help would be very welcome!

edit retag flag offensive close merge delete


Every Chart getting its data from a CellRange (or a few CellRanges) is dynamic. You have to prepare the data, however, to meet your purposes. Start with a complete list of your activities, use some kind of SUMIF or SUMPRODUCT, calculate the ratios ... and it will be easy to create the Chart.

Lupp gravatar imageLupp ( 2016-01-11 21:04:30 +0200 )edit

Thanks Lupp, the dynamic part was especially about recognizing the categories without hardcoding them and then find the total value of such a category. I can find the total time of all categories, but how do I "detect" the categories that are in column A and how do I add together all of the time periods of one category?

LibreGuy gravatar imageLibreGuy ( 2016-01-11 21:43:52 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2016-01-11 23:30:21 +0200

Lupp gravatar image

updated 2016-01-11 23:32:35 +0200

The 'Data range' of a chart must be a range address constant or a list of range address constants separated by semicolons.
It might be preferable if calculated ranges (using OFFSET) or named ranges (which well may be calculated) were allowed. However, I did not even find a feature request to that effect. There are similar ones for "Pivot Tables" and for "Print Ranges".
Thus you may prepare everything allowing for variable data ranges, but at the end you will have to open the 'Data Ranges...' dialogue to make the final adaptions.
See attached example: ask62889ChartWithAutomaticallyCompactedLabels001.ods

edit flag offensive delete link more


Thanks Lupp. You put some effort in it :-) I appreciate that very much.

Since this is new to me, it's quite hard to understand. However, am I right to say that you made 2 set-ups (possible scenario's) which I could use, and that the one one the left is more hard-coded where the one on the rigth is more dynamic? Is that a valid conclusion?

LibreGuy gravatar imageLibreGuy ( 2016-01-12 00:44:40 +0200 )edit

The left side is just doing as I suggested in my first comment. The right side (including the yellow helpers) creates a list of activities automatically compacted (in reverse order of the final occurrence). An automated adaption of the data ranges for the chart is (presently) not supported.
The ones in column K indicate the last occurrences. In column M the relative row numbers of these occurrences are found.

Lupp gravatar imageLupp ( 2016-01-12 01:02:58 +0200 )edit

Thanks I sort of understand now. I will figure out if and if so which method I will be using. At least I've got a starting point now. Thanks for your help! Truly appreciate it.

LibreGuy gravatar imageLibreGuy ( 2016-01-12 01:19:47 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-01-11 17:44:50 +0200

Seen: 1,579 times

Last updated: Jan 11 '16