Home

How to create a "combination chart" ?

%3CLINGO-SUB%20id%3D%22lingo-sub-873240%22%20slang%3D%22en-US%22%3EHow%20to%20create%20a%20%22combination%20chart%22%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-873240%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20data%20looks%20like%20this%3A%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3ETemperature%3C%2FTD%3E%3CTD%3EBinned%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E66.6%3C%2FTD%3E%3CTD%3EMEIDUM%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E70.3%3C%2FTD%3E%3CTD%3EMEIDUM%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E69.2%3C%2FTD%3E%3CTD%3EMEIDUM%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E68.1%3C%2FTD%3E%3CTD%3EMEIDUM%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E67.2%3C%2FTD%3E%3CTD%3EMEIDUM%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E72.6%3C%2FTD%3E%3CTD%3EMEIDUM%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E73.2%3C%2FTD%3E%3CTD%3EMEIDUM%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E70.3%3C%2FTD%3E%3CTD%3EMEIDUM%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E57.6%3C%2FTD%3E%3CTD%3ELOW%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E63.1%3C%2FTD%3E%3CTD%3ELOW%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E70.8%3C%2FTD%3E%3CTD%3EMEIDUM%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E78.7%3C%2FTD%3E%3CTD%3EHIGH%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E67.7%3C%2FTD%3E%3CTD%3EMEIDUM%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E53.4%3C%2FTD%3E%3CTD%3ELOW%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E67.3%3C%2FTD%3E%3CTD%3EMEIDUM%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E75.4%3C%2FTD%3E%3CTD%3EMEIDUM%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E70.7%3C%2FTD%3E%3CTD%3EMEIDUM%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E81.2%3C%2FTD%3E%3CTD%3EHIGH%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E76.6%3C%2FTD%3E%3CTD%3EHIGH%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E79.6%3C%2FTD%3E%3CTD%3EHIGH%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E75.4%3C%2FTD%3E%3CTD%3EMEIDUM%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E76.4%3C%2FTD%3E%3CTD%3EHIGH%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E58.6%3C%2FTD%3E%3CTD%3ELOW%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20has%20one%20column%20of%20numerical%20values%20and%20one%20column%20of%20categorical%20values.%3C%2FP%3E%3CP%3EAnd%20I%20want%20to%20create%20a%20chart%20like%20this%3A%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3EI%20want%20to%20left%20axis%20to%20be%20the%20count%20of%20different%20categories%2C%20right%20axis%20should%20be%20the%20avg%20temperature%20for%20each%20category.%26nbsp%3B%20What%20should%20I%20do%20to%20achieve%20this%20kind%20of%20functionality%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-873240%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-878233%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20a%20%22combination%20chart%22%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-878233%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F414480%22%20target%3D%22_blank%22%3E%40JP_Zhang%3C%2FA%3ETo%20create%20a%20combination%20chart%2C%20you%20need%20to%20select%20the%20ranges%20that%20your%20data%20is%20in%20and%20go%20to%20the%20insert%20tab.%20On%20the%20Insert%20tab%2C%20in%20the%20Charts%20group%2C%20there%20is%20a%20combo%20symbol%20that%20you%20must%20click.%20This%20symbol%20has%20a%20line%20graph%20above%20a%20bar%20graph%20(it%20is%20in%20the%20second%20row%20to%20the%20left%20of%20the%20PivotChart%20symbol).%20Once%20you%20click%20on%20the%20combo%20symbol%2C%20the%20chart%20dialogue%20box%20will%20appear.%20From%20here%20since%20you%20want%20a%20custom%20combination%20chart%2C%20you%20need%20to%20click%20the%20combo%20symbol%20with%20a%20blue%20pencil.%20This%20will%20allow%20you%20to%20customize%20where%20you%20want%20your%20data%20to%20be%20placed%20or%20what%20you%20wish%20to%20as%20a%20line%20or%20bar%20graph.%20Finally%2C%20to%20see%20your%20results%2C%20you%20will%20click%20to%20OK%2C%20which%20is%20located%20at%20the%20bottom%20of%20the%20dialog%20box.%20I%20hope%20this%20helps!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-878321%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20a%20%22combination%20chart%22%20%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-878321%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F414480%22%20target%3D%22_blank%22%3E%40JP_Zhang%3C%2FA%3E%26nbsp%3B%2C%20I'm%20having%20trouble%20visualising%20the%20chart%20you%20want%20to%20create.%20Can%20you%20mock%20it%20up%20with%20the%20drawing%20tools%20and%20post%20a%20picture%3F%20To%20post%20a%20picture%2C%20click%20on%20the%20camera%20icon%20in%20the%20toolbar.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20to%20plot%20the%20count%20and%20the%20average%2C%20you%20first%20need%20to%20calculate%20these%20numbers%20somewhere%20in%20the%20workbook.%20In%20the%20screenshot%2C%20I%20have%20manually%20entered%20the%20words%20in%20column%20E%20and%20then%20used%20Countif%20and%20AverageIF%20formula.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECount%26nbsp%3B%3DCOUNTIF(B2%3AB24%2CE2)%20in%20F2%20copied%20down%2C%3C%2FP%3E%0A%3CP%3EAverage%26nbsp%3B%3DAVERAGEIF(%24B%242%3A%24B%2424%2CE2%2C%24A%242%3A%24A%2424)%20in%20G2%20copied%20down.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%20I%20added%20a%20clustered%20column%20chart%20and%20changed%20the%20Average%20series%20to%20a%20line%20chart.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDoes%20this%20help%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20801px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133720i710DE8AE4FB150C1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-09-27_14-45-36.png%22%20title%3D%222019-09-27_14-45-36.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
JP_Zhang
Occasional Visitor

My data looks like this:

TemperatureBinned
66.6MEIDUM
70.3MEIDUM
69.2MEIDUM
68.1MEIDUM
67.2MEIDUM
72.6MEIDUM
73.2MEIDUM
70.3MEIDUM
57.6LOW
63.1LOW
70.8MEIDUM
78.7HIGH
67.7MEIDUM
53.4LOW
67.3MEIDUM
75.4MEIDUM
70.7MEIDUM
81.2HIGH
76.6HIGH
79.6HIGH
75.4MEIDUM
76.4HIGH
58.6LOW

 

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? 

2 Replies

@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!

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?

 

2019-09-27_14-45-36.png

 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies