google sheetsgoogle-sheets-charts
I have a Google spreadsheet that has values like:
1/1/10 0
1/2/10 43
1/3/10 17
etc.
I want to be able to visualize the data on a day-by-day basis and on a month by month basis.
I managed to create a chart based on the day – but I can't figure out how to automatically group the months.
What can I do?
Ultimately, you need to do what you already predicted: adding a leading zero. The TEXT function will make that happen like this:
=TEXT(A2, "00")
I've created a formula that takes on the complete column, filter for empty cells, brings together the MONTH and DAY and SORTs the lot.
=SORT(ARRAYFORMULA(TEXT(MONTH(FILTER(A2:A;A2:A<>""));"00") & " - "
& TEXT(DAY(FILTER(A2:A;A2:A<>""));"00"));1;1)
Here's a break-down description of the formula:
FILTER function will filter for all rows that have something.MONTH and DAY functions will extract the respective values
from the date.TEXT function will convert the value from point 2 into a pre-formatted STRING.ARRAYFORMULA function will apply all the above to the complete column (skipping the header).SORT function will sort the result, given by the
ARRAYFORMULA.The SORT function allows for sorting. If you do that via a column sort, the entry of the ARRAYFORMULA is taken into account as well and gets re-positioned, causing mayhem.
See example file I've created: sorting dates as text
Use "Advanced edit" option in the dropdown shown in upper right corner when the char is clicked. (It can also be accessed by right-clicking the chart.)
It will bring you back to the dialog you saw when inserting the chart. Change the data range on "Chart types" tab, and click "Update".
Best Answer
Basically what you're looking for is something like Pivot Tables of Excel, where you can group by anything you want.
Unfortunately there is no such support in Google Spreadsheet. What you (or someone) could do though is to write a simple script to group them for you. If you're interested you can start with some tutorials.