Sep 24 2019 12:41 PM - edited Sep 24 2019 12:45 PM
My data looks like this:
Temperature | Binned |
66.6 | MEIDUM |
70.3 | MEIDUM |
69.2 | MEIDUM |
68.1 | MEIDUM |
67.2 | MEIDUM |
72.6 | MEIDUM |
73.2 | MEIDUM |
70.3 | MEIDUM |
57.6 | LOW |
63.1 | LOW |
70.8 | MEIDUM |
78.7 | HIGH |
67.7 | MEIDUM |
53.4 | LOW |
67.3 | MEIDUM |
75.4 | MEIDUM |
70.7 | MEIDUM |
81.2 | HIGH |
76.6 | HIGH |
79.6 | HIGH |
75.4 | MEIDUM |
76.4 | HIGH |
58.6 | LOW |
It has one column of numerical values and one column of categorical values.
And I want to create a chart like this:
(Seems I can't post image here. Anyway, the pic is in https://user-images.githubusercontent.com/16349466/65544832-1bafec00-dee2-11e9-8d6f-8cadbe83eec8.png)
I want to left axis to be the count of different categories, right axis should be the avg temperature for each category. What should I do to achieve this kind of functionality?
Sep 26 2019 06:21 PM
@JP_ZhangTo create a combination chart, you need to select the ranges that your data is in and go to the insert tab. On the Insert tab, in the Charts group, there is a combo symbol that you must click. This symbol has a line graph above a bar graph (it is in the second row to the left of the PivotChart symbol). Once you click on the combo symbol, the chart dialogue box will appear. From here since you want a custom combination chart, you need to click the combo symbol with a blue pencil. This will allow you to customize where you want your data to be placed or what you wish to as a line or bar graph. Finally, to see your results, you will click to OK, which is located at the bottom of the dialog box. I hope this helps!
Sep 26 2019 07:48 PM
Hello @JP_Zhang , I'm having trouble visualising the chart you want to create. Can you mock it up with the drawing tools and post a picture? To post a picture, click on the camera icon in the toolbar.
If you want to plot the count and the average, you first need to calculate these numbers somewhere in the workbook. In the screenshot, I have manually entered the words in column E and then used Countif and AverageIF formula.
Count =COUNTIF(B2:B24,E2) in F2 copied down,
Average =AVERAGEIF($B$2:$B$24,E2,$A$2:$A$24) in G2 copied down.
Then I added a clustered column chart and changed the Average series to a line chart.
Does this help?