Hi everyone,
I have data arranged in three columns:
1st column contains the month (in format: Jan, Feb, Mar, and os on),
2nd column the year
3rd column the value of a quantity corresponding to the specific month of the year.
How can I plot the data versus the month-year in LibreOffice Calc? Is there a fast way? Or do I have to convert by hand first the format in MM/YYYY and then plot the values versus this variable?
Thanks in advance and best regards.
Date values represented internally by numbers the common way are not suitable for Scatter-Charts if the month (or the year) should be shown on one axis. The simple reason is that months (and years) differ in length while the intervals on the axis need to be of same length (labels equidistant).
If the difference in length of the months definitely is not relevant, this is one of the rare cases where I might probably use the so-called Line-Chart. A Column-Chart may suit better:
Create an auxiliary column with an integer sequence contaíning the ordinals of the months, and a formula creating the wanted labels based on these integers as texts.
Not much more words: Study this attached example - and tell me if I misunderstood you.
(Use good formatting like YYYY-MM instead of outdated MM/YYYY. Reserve the slash for fractions.)
===Edit 2020-03-16 about 12:50 UTC===
The attachment announced in my recent comment: ask233774monthsAsAxisLabels_1.ods
===Edit 2020-03-16 about 19:45 UTC===
Sorry! Just came back to this thread and noticed that the second attachment I had made wasn’t the one I had intended.
Now (hopefully) the correct attachment: ask233774plotWithMonthNames_1.ods
Thanks Lupp,
It kind of helped, yes. Mainly to understand how to create a date, given a spreadsheet like yours.
But my question was more related to something like the spreadsheet I’ve just added (and made up using your data).
Is there a way to create a date considering the first and second column (no matter the order). Or do I have to convert the month names into a sequence of numbers (1-12) first and then use the function DATE?
I have found out that if I merge the two cells, Calc already format the merged value as a date, which is exactly what I want. But I have to do it row by row, selecting for each row the two cells (year and month) and then merging. Is there a way to do it more efficiently, something similar to “merge across” in Excel?
Thanks
Obviously I still don’t understand for sure.
About terms: Dates are a real-world thing on the one hand, but represented in many different ways on the other hand. Concerning spreadsheets the mostly assumed meaning is “A real-world-date represented the default way as an ordinal number in the wider sense (allowing for 0 and negative values : a generalized index) contained in a cell or returned to it by a formula, if the cell is formatted to any Numberformat of the Date type”**.
Of course not everything related is as simple as this periphrasis, but most people tend to even refuse trying to be precise on this level.
Textual representations of real-world-dates (as unfortunately used in this silly real world) heavily depend on local and cultural traditions and lots of different influences. LibO tries to cope with this mess partly using the locale concept. No satisfying results.
My advice: Never use localized means to enter dates. “NeverEnterLocalized!”
Even more important: Never merge cells trying to achieve something as discussed here. Accepting the locale-nonsense, you would get what you want with the help of the DATEVALUE() function, but moving the sheet to a different locale, or as a side-effect of some changes in options or formatting it would easily fail.
Do things by explicit means to control the effects!
See new attachment to my answer. I made it from the attachment to your question.
AND
Deprecate and abandon localized date formats. Always use ISO 8601 complying format
YYYY-MM-DD. This in specific without exception if you actually convert to a textual representation.
Thanks a lot Lupp!
And thanks for clarification and suggestions, too.
Well, about the date format it was more for personal use, but I agree that a standardized format should be spread and used.