Line graphs and most other types in Google Sheets have a checkbox for "aggregate" in the setup tab. I've tried looking through the instructions but I can't find any documentation on how to use this feature. Can anyone point me in the right direction? An example data set where it's useful would be really helpful.
I've tried enabling it on various charts with different data, but it never changes anything.
Google-sheets – How to use “aggregate” chart feature on Google Sheets
google sheetsgoogle-sheets-charts
Related Solutions
From your spreadsheet, select the cells with data you'd like to include in the chart. Alternatively, you can select a range or multiple ranges of data from within the charts dialog. You can do so by clicking Select range... and entering one or more ranges by clicking Add another range.
Note: It helps to label the data in your spreadsheet before creating a chart. For example, if you want to chart your expenses, you might have a row of numbers labeled 'Rent' and another labeled 'Groceries.' Then you might label columns by month or week, etc. These labels will appear automatically in the window where you create and preview your chart, as long as the labels are the first row and column of your selected range of cells.Select the Chart
icon in the menu bar or choose Insert > Chart. The charts dialog box appears.
In the Start tab, you’re able to edit the range of cells to be included in your chart, select basic layout settings, and view recommended charts.In the Start tab, you’re able to edit the range of cells to be included in your chart, select basic layout settings, and view recommended charts.
Note: If you included labels for your data in your spreadsheet, you can specify that you want to use the first row and first column of your data as labels by checking the box next to ‘Use 1st row as headers’ and ‘Use 1st column as labels.’ These settings are automatically selected when you include labels in Row 1 and Column A in your spreadsheet.If you decide that one of the recommended charts isn’t the right thing for your data or if you want to see more chart options, you can either click More >> or move on to the Charts tab.
- Preview your chart and edit as necessary.
- Click Insert. The chart appears in your spreadsheet. If you would like to go back to editing your spreadsheet without an inserted chart, click Cancel.
From the Creating a chart help page.
For the average, you can use the formula =ArrayFormula(AVERAGE(IF(A1:A7 <> 0; A1:A7))). To use this formula within the chart instead of its value, I don't know how to do it or even if it's possible. You could, however, add another cell (say A8) in which you compute the average value for the respective week and use that value to make you average values chart.
What you need to do is a few steps:
- Insert to Chart then add your Data Series, as you normally would with a Single Axis chart.
- (Optional) Change chart type to Line. You can change it to anything else later. Line seems to be a safe bet for the later options to appear. Not sure if this step is by Google design...
- Right-click on the chart and select Series.
- Change Apply Series to whichever Data Series you want to move to the Second Axis.
- Change Axis to Right Axis.
- Now you can go back to Chart type to make it a scatter, or bar, or anything else - the program will remember the Axis assigned to the Data Series, regardless of whatever options may be available.
tldr; the Google Support answer is broken for a few chart types. Do your Axis assignments with Line charts, and change the Chart Type after.
Related Topic
- Google-sheets – How to reorder labels on Google sheets chart
- Google-sheets – Sum of time in Google Sheets’ chart
- Google-sheets – Google Sheets time sorting chart
- Google-sheets – How to rename a series in Google Sheets chart
- Google-sheets – Google Sheets: aggregate and extrapolate irregular data over time
Best Answer
The aggregate feature allows you to aggregate all values that share an identical x-axis key.
Normally, if you have duplicate x-axis keys, your chart will look something like this, with values having the same key forming vertical lines:
(I've increased the width of the lines to make it easier to see what's happening.)
But perhaps this isn't the behavior you want. Perhaps, instead, you want all y-values with the same x-value to be summed, and only the summation to be plotted. The aggregate feature lets you do this.
First, turn on the aggregate feature, and select the desired aggregation method:
After making these edits, instead of y-values with identical x-values resulting in vertical bars, the aggregation of the y-values for a given x-values will be graphed: