Plot with hours that have a sign on Libreoffice-Calc
Clash Royale CLAN TAG#URR8PPP
up vote
1
down vote
favorite
On Libreoffice calc I have a spreadsheet that can compute certain quantities in time (hours). These quantities can be positive or negative.
For example, the values in the spreadsheet can be a daily commuting time to work minus an average/nominal value. Thus a negative value in a certain day would indicate that the trip on that day was faster than usual.
I have also created a plot to visualize this, but I cannot put the correct value on the y-axis. Somehow the values are set with a circular algebra, and if they are above 24 hours the labels on the y axis are resets to 0. Also, the negative values are shown as positive values, while I would like to show them with their sign.
Any clue on how to correctly choose the labels for the y-axis?
In the figures you see the plot, the y-axis options for that plot and part of the data used for the plot. As you can see, the values on the H column are measured in hours and minutes and they have a sign. Note also the discrepancy between the values on the H column, which are both positive and negative, and the ones in the y-axis, which are only positive.
libreoffice spreadsheet plotting
asked Sep 19 at 16:32
cholo14
83
 |Â
show 2 more comments
up vote
1
down vote
favorite
On Libreoffice calc I have a spreadsheet that can compute certain quantities in time (hours). These quantities can be positive or negative.
For example, the values in the spreadsheet can be a daily commuting time to work minus an average/nominal value. Thus a negative value in a certain day would indicate that the trip on that day was faster than usual.
I have also created a plot to visualize this, but I cannot put the correct value on the y-axis. Somehow the values are set with a circular algebra, and if they are above 24 hours the labels on the y axis are resets to 0. Also, the negative values are shown as positive values, while I would like to show them with their sign.
Any clue on how to correctly choose the labels for the y-axis?
In the figures you see the plot, the y-axis options for that plot and part of the data used for the plot. As you can see, the values on the H column are measured in hours and minutes and they have a sign. Note also the discrepancy between the values on the H column, which are both positive and negative, and the ones in the y-axis, which are only positive.
libreoffice spreadsheet plotting
asked Sep 19 at 16:32
cholo14
83
Hi cholo14, do you mean that you would like to add the label of the y axes (e.g. hours) and for the x axes e.g. (something)?
– Goro
Sep 19 at 16:53
There are a few questions: 1 Is the y axis "time of day" or "time in hours/minutes" ... 2 Did you check superuser ... 3 Can you provide a sample of the spreadsheet data in CSV format, or something tabular in your question?
– RubberStamp
Sep 19 at 16:59
I think I see what you mean... Try the following: 1 Subtract the End Time from Start Time ... 2 Subtract your nominal time value ... 3 Format a new answer column as a plain Number format ... 4 Multiply by the time subtracted value by the number of minutes in a 24 hour day ... 1440 ...
– RubberStamp
Sep 19 at 17:28
hi @Goro, no, I want the labels on the y-axis to have negative values and not to follow a circular clock algebra. I have updated the question, I hope it is more clear now
– cholo14
Sep 20 at 8:02
@RubberStamp thanks but I want to keep the time in hours, not in minutes, so I cannot use your workaround. 1) the y-axis represents the data on the H column 2) no...would that help? 3) Done, I have updated the question
– cholo14
Sep 20 at 8:03
 |Â
show 2 more comments
up vote
1
down vote
favorite
up vote
1
down vote
favorite
On Libreoffice calc I have a spreadsheet that can compute certain quantities in time (hours). These quantities can be positive or negative.
For example, the values in the spreadsheet can be a daily commuting time to work minus an average/nominal value. Thus a negative value in a certain day would indicate that the trip on that day was faster than usual.
I have also created a plot to visualize this, but I cannot put the correct value on the y-axis. Somehow the values are set with a circular algebra, and if they are above 24 hours the labels on the y axis are resets to 0. Also, the negative values are shown as positive values, while I would like to show them with their sign.
Any clue on how to correctly choose the labels for the y-axis?
In the figures you see the plot, the y-axis options for that plot and part of the data used for the plot. As you can see, the values on the H column are measured in hours and minutes and they have a sign. Note also the discrepancy between the values on the H column, which are both positive and negative, and the ones in the y-axis, which are only positive.
libreoffice spreadsheet plotting
asked Sep 19 at 16:32
cholo14
83
On Libreoffice calc I have a spreadsheet that can compute certain quantities in time (hours). These quantities can be positive or negative.
For example, the values in the spreadsheet can be a daily commuting time to work minus an average/nominal value. Thus a negative value in a certain day would indicate that the trip on that day was faster than usual.
I have also created a plot to visualize this, but I cannot put the correct value on the y-axis. Somehow the values are set with a circular algebra, and if they are above 24 hours the labels on the y axis are resets to 0. Also, the negative values are shown as positive values, while I would like to show them with their sign.
Any clue on how to correctly choose the labels for the y-axis?
In the figures you see the plot, the y-axis options for that plot and part of the data used for the plot. As you can see, the values on the H column are measured in hours and minutes and they have a sign. Note also the discrepancy between the values on the H column, which are both positive and negative, and the ones in the y-axis, which are only positive.
libreoffice spreadsheet plotting
libreoffice spreadsheet plotting
asked Sep 19 at 16:32
cholo14
83
asked Sep 19 at 16:32
cholo14
83
edited Sep 20 at 8:00
asked Sep 19 at 16:32
cholo14
83
asked Sep 19 at 16:32
cholo14
83
asked Sep 19 at 16:32
cholo14
83
83
Hi cholo14, do you mean that you would like to add the label of the y axes (e.g. hours) and for the x axes e.g. (something)?
– Goro
Sep 19 at 16:53
There are a few questions: 1 Is the y axis "time of day" or "time in hours/minutes" ... 2 Did you check superuser ... 3 Can you provide a sample of the spreadsheet data in CSV format, or something tabular in your question?
– RubberStamp
Sep 19 at 16:59
I think I see what you mean... Try the following: 1 Subtract the End Time from Start Time ... 2 Subtract your nominal time value ... 3 Format a new answer column as a plain Number format ... 4 Multiply by the time subtracted value by the number of minutes in a 24 hour day ... 1440 ...
– RubberStamp
Sep 19 at 17:28
hi @Goro, no, I want the labels on the y-axis to have negative values and not to follow a circular clock algebra. I have updated the question, I hope it is more clear now
– cholo14
Sep 20 at 8:02
@RubberStamp thanks but I want to keep the time in hours, not in minutes, so I cannot use your workaround. 1) the y-axis represents the data on the H column 2) no...would that help? 3) Done, I have updated the question
– cholo14
Sep 20 at 8:03
 |Â
show 2 more comments
Hi cholo14, do you mean that you would like to add the label of the y axes (e.g. hours) and for the x axes e.g. (something)?
– Goro
Sep 19 at 16:53
There are a few questions: 1 Is the y axis "time of day" or "time in hours/minutes" ... 2 Did you check superuser ... 3 Can you provide a sample of the spreadsheet data in CSV format, or something tabular in your question?
– RubberStamp
Sep 19 at 16:59
I think I see what you mean... Try the following: 1 Subtract the End Time from Start Time ... 2 Subtract your nominal time value ... 3 Format a new answer column as a plain Number format ... 4 Multiply by the time subtracted value by the number of minutes in a 24 hour day ... 1440 ...
– RubberStamp
Sep 19 at 17:28
hi @Goro, no, I want the labels on the y-axis to have negative values and not to follow a circular clock algebra. I have updated the question, I hope it is more clear now
– cholo14
Sep 20 at 8:02
@RubberStamp thanks but I want to keep the time in hours, not in minutes, so I cannot use your workaround. 1) the y-axis represents the data on the H column 2) no...would that help? 3) Done, I have updated the question
– cholo14
Sep 20 at 8:03
Hi cholo14, do you mean that you would like to add the label of the y axes (e.g. hours) and for the x axes e.g. (something)?
– Goro
Sep 19 at 16:53
Hi cholo14, do you mean that you would like to add the label of the y axes (e.g. hours) and for the x axes e.g. (something)?
– Goro
Sep 19 at 16:53
There are a few questions: 1 Is the y axis "time of day" or "time in hours/minutes" ... 2 Did you check superuser ... 3 Can you provide a sample of the spreadsheet data in CSV format, or something tabular in your question?
– RubberStamp
Sep 19 at 16:59
There are a few questions: 1 Is the y axis "time of day" or "time in hours/minutes" ... 2 Did you check superuser ... 3 Can you provide a sample of the spreadsheet data in CSV format, or something tabular in your question?
– RubberStamp
Sep 19 at 16:59
I think I see what you mean... Try the following: 1 Subtract the End Time from Start Time ... 2 Subtract your nominal time value ... 3 Format a new answer column as a plain Number format ... 4 Multiply by the time subtracted value by the number of minutes in a 24 hour day ... 1440 ...
– RubberStamp
Sep 19 at 17:28
I think I see what you mean... Try the following: 1 Subtract the End Time from Start Time ... 2 Subtract your nominal time value ... 3 Format a new answer column as a plain Number format ... 4 Multiply by the time subtracted value by the number of minutes in a 24 hour day ... 1440 ...
– RubberStamp
Sep 19 at 17:28
hi @Goro, no, I want the labels on the y-axis to have negative values and not to follow a circular clock algebra. I have updated the question, I hope it is more clear now
– cholo14
Sep 20 at 8:02
hi @Goro, no, I want the labels on the y-axis to have negative values and not to follow a circular clock algebra. I have updated the question, I hope it is more clear now
– cholo14
Sep 20 at 8:02
@RubberStamp thanks but I want to keep the time in hours, not in minutes, so I cannot use your workaround. 1) the y-axis represents the data on the H column 2) no...would that help? 3) Done, I have updated the question
– cholo14
Sep 20 at 8:03
@RubberStamp thanks but I want to keep the time in hours, not in minutes, so I cannot use your workaround. 1) the y-axis represents the data on the H column 2) no...would that help? 3) Done, I have updated the question
– cholo14
Sep 20 at 8:03
 |Â
show 2 more comments
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
As pointed out by OP in the comments, negative time values can be displayed in LibreOffice by formatting the cell as [HH]:MM.
Time calculations result in a fraction of the amount of time in a day. In order to find the departure from some nominal value, subtract the value from the time difference between End and Start.
I've created a sample spreadsheet in CSV format which includes a statically listed nominal value as well as a calculated average value:
Start,End,Commute Time,,Normalized ΔT,Departure from Average
09:30,10:00,=B2-A2,,=C2-A10,=C2-C10
13:45,14:00,=B3-A3,,=C3-A10,=C3-C10
08:15,09:00,=B4-A4,,=C4-A10,=C4-C10
10:20,11:35,=B5-A5,,=C5-A10,=C5-C10
07:00,08:30,=B6-A6,,=C6-A10,=C6-C10
09:00,09:01,=B7-A7,,=C7-A10,=C7-C10
,,,,,
Static Nominal,,Computed Average,,,
00:20,,=SUM(C2:C7)/6,,,
You can copy and paste the above into a new spreadsheet to see how it works. Make sure you use "Paste Special" option and select comma as the delimiter.
Following are screenshots showing the sample data in graph form...
Copy and Special Paste the CSV
Change cell format to Time with [HH]:MM
![Change cell format to Time with [HH]:MM](https://i.stack.imgur.com/skFAY.png)
Plot the commute time variances from a static and average values
answered Sep 19 at 18:06
RubberStamp
1,5101216
there are negative time values in LibreOffice-Calc, it is enough to use as format code [HH]:MM. Thanks for your help, but I don't want to convert the time into minutes, I want the plot to show the time in hours with sign
– cholo14
Sep 20 at 7:56
add a comment |Â
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
As pointed out by OP in the comments, negative time values can be displayed in LibreOffice by formatting the cell as [HH]:MM.
Time calculations result in a fraction of the amount of time in a day. In order to find the departure from some nominal value, subtract the value from the time difference between End and Start.
I've created a sample spreadsheet in CSV format which includes a statically listed nominal value as well as a calculated average value:
Start,End,Commute Time,,Normalized ΔT,Departure from Average
09:30,10:00,=B2-A2,,=C2-A10,=C2-C10
13:45,14:00,=B3-A3,,=C3-A10,=C3-C10
08:15,09:00,=B4-A4,,=C4-A10,=C4-C10
10:20,11:35,=B5-A5,,=C5-A10,=C5-C10
07:00,08:30,=B6-A6,,=C6-A10,=C6-C10
09:00,09:01,=B7-A7,,=C7-A10,=C7-C10
,,,,,
Static Nominal,,Computed Average,,,
00:20,,=SUM(C2:C7)/6,,,
You can copy and paste the above into a new spreadsheet to see how it works. Make sure you use "Paste Special" option and select comma as the delimiter.
Following are screenshots showing the sample data in graph form...
Copy and Special Paste the CSV
Change cell format to Time with [HH]:MM
Plot the commute time variances from a static and average values
answered Sep 19 at 18:06
RubberStamp
1,5101216
there are negative time values in LibreOffice-Calc, it is enough to use as format code [HH]:MM. Thanks for your help, but I don't want to convert the time into minutes, I want the plot to show the time in hours with sign
– cholo14
Sep 20 at 7:56
add a comment |Â
up vote
1
down vote
accepted
As pointed out by OP in the comments, negative time values can be displayed in LibreOffice by formatting the cell as [HH]:MM.
Time calculations result in a fraction of the amount of time in a day. In order to find the departure from some nominal value, subtract the value from the time difference between End and Start.
I've created a sample spreadsheet in CSV format which includes a statically listed nominal value as well as a calculated average value:
Start,End,Commute Time,,Normalized ΔT,Departure from Average
09:30,10:00,=B2-A2,,=C2-A10,=C2-C10
13:45,14:00,=B3-A3,,=C3-A10,=C3-C10
08:15,09:00,=B4-A4,,=C4-A10,=C4-C10
10:20,11:35,=B5-A5,,=C5-A10,=C5-C10
07:00,08:30,=B6-A6,,=C6-A10,=C6-C10
09:00,09:01,=B7-A7,,=C7-A10,=C7-C10
,,,,,
Static Nominal,,Computed Average,,,
00:20,,=SUM(C2:C7)/6,,,
You can copy and paste the above into a new spreadsheet to see how it works. Make sure you use "Paste Special" option and select comma as the delimiter.
Following are screenshots showing the sample data in graph form...
Copy and Special Paste the CSV
Change cell format to Time with [HH]:MM
Plot the commute time variances from a static and average values
answered Sep 19 at 18:06
RubberStamp
1,5101216
there are negative time values in LibreOffice-Calc, it is enough to use as format code [HH]:MM. Thanks for your help, but I don't want to convert the time into minutes, I want the plot to show the time in hours with sign
– cholo14
Sep 20 at 7:56
add a comment |Â
up vote
1
down vote
accepted
up vote
1
down vote
accepted
As pointed out by OP in the comments, negative time values can be displayed in LibreOffice by formatting the cell as [HH]:MM.
Time calculations result in a fraction of the amount of time in a day. In order to find the departure from some nominal value, subtract the value from the time difference between End and Start.
I've created a sample spreadsheet in CSV format which includes a statically listed nominal value as well as a calculated average value:
Start,End,Commute Time,,Normalized ΔT,Departure from Average
09:30,10:00,=B2-A2,,=C2-A10,=C2-C10
13:45,14:00,=B3-A3,,=C3-A10,=C3-C10
08:15,09:00,=B4-A4,,=C4-A10,=C4-C10
10:20,11:35,=B5-A5,,=C5-A10,=C5-C10
07:00,08:30,=B6-A6,,=C6-A10,=C6-C10
09:00,09:01,=B7-A7,,=C7-A10,=C7-C10
,,,,,
Static Nominal,,Computed Average,,,
00:20,,=SUM(C2:C7)/6,,,
You can copy and paste the above into a new spreadsheet to see how it works. Make sure you use "Paste Special" option and select comma as the delimiter.
Following are screenshots showing the sample data in graph form...
Copy and Special Paste the CSV
Change cell format to Time with [HH]:MM
Plot the commute time variances from a static and average values
answered Sep 19 at 18:06
RubberStamp
1,5101216
As pointed out by OP in the comments, negative time values can be displayed in LibreOffice by formatting the cell as [HH]:MM.
Time calculations result in a fraction of the amount of time in a day. In order to find the departure from some nominal value, subtract the value from the time difference between End and Start.
I've created a sample spreadsheet in CSV format which includes a statically listed nominal value as well as a calculated average value:
Start,End,Commute Time,,Normalized ΔT,Departure from Average
09:30,10:00,=B2-A2,,=C2-A10,=C2-C10
13:45,14:00,=B3-A3,,=C3-A10,=C3-C10
08:15,09:00,=B4-A4,,=C4-A10,=C4-C10
10:20,11:35,=B5-A5,,=C5-A10,=C5-C10
07:00,08:30,=B6-A6,,=C6-A10,=C6-C10
09:00,09:01,=B7-A7,,=C7-A10,=C7-C10
,,,,,
Static Nominal,,Computed Average,,,
00:20,,=SUM(C2:C7)/6,,,
You can copy and paste the above into a new spreadsheet to see how it works. Make sure you use "Paste Special" option and select comma as the delimiter.
Following are screenshots showing the sample data in graph form...
Copy and Special Paste the CSV
Change cell format to Time with [HH]:MM
Plot the commute time variances from a static and average values
answered Sep 19 at 18:06
RubberStamp
1,5101216
edited Sep 20 at 13:06
answered Sep 19 at 18:06
RubberStamp
1,5101216
answered Sep 19 at 18:06
RubberStamp
1,5101216
answered Sep 19 at 18:06
RubberStamp
1,5101216
1,5101216
there are negative time values in LibreOffice-Calc, it is enough to use as format code [HH]:MM. Thanks for your help, but I don't want to convert the time into minutes, I want the plot to show the time in hours with sign
– cholo14
Sep 20 at 7:56
add a comment |Â
there are negative time values in LibreOffice-Calc, it is enough to use as format code [HH]:MM. Thanks for your help, but I don't want to convert the time into minutes, I want the plot to show the time in hours with sign
– cholo14
Sep 20 at 7:56
there are negative time values in LibreOffice-Calc, it is enough to use as format code [HH]:MM. Thanks for your help, but I don't want to convert the time into minutes, I want the plot to show the time in hours with sign
– cholo14
Sep 20 at 7:56
there are negative time values in LibreOffice-Calc, it is enough to use as format code [HH]:MM. Thanks for your help, but I don't want to convert the time into minutes, I want the plot to show the time in hours with sign
– cholo14
Sep 20 at 7:56
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f470064%2fplot-with-hours-that-have-a-sign-on-libreoffice-calc%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Hi cholo14, do you mean that you would like to add the label of the y axes (e.g. hours) and for the x axes e.g. (something)?
– Goro
Sep 19 at 16:53
There are a few questions: 1 Is the y axis "time of day" or "time in hours/minutes" ... 2 Did you check superuser ... 3 Can you provide a sample of the spreadsheet data in CSV format, or something tabular in your question?
– RubberStamp
Sep 19 at 16:59
I think I see what you mean... Try the following: 1 Subtract the End Time from Start Time ... 2 Subtract your nominal time value ... 3 Format a new answer column as a plain Number format ... 4 Multiply by the time subtracted value by the number of minutes in a 24 hour day ... 1440 ...
– RubberStamp
Sep 19 at 17:28
hi @Goro, no, I want the labels on the y-axis to have negative values and not to follow a circular clock algebra. I have updated the question, I hope it is more clear now
– cholo14
Sep 20 at 8:02
@RubberStamp thanks but I want to keep the time in hours, not in minutes, so I cannot use your workaround. 1) the y-axis represents the data on the H column 2) no...would that help? 3) Done, I have updated the question
– cholo14
Sep 20 at 8:03