Add dots on secondary axis in grouped column chart

sajibo

New Member
Joined
Mar 19, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Good morning!

I'm trying to visualise number of tests performed by 2 types of providers in 5 regions over 8 years, and the positivity ratios. So a lot of info in one graph.
The grouped columns for the number of tests look nice enough, but I can't get the positivity ratios right on the secondary axis. See Images.
When I use scatter or lines in the secondary axis, they dont show the progress from 2011-2018 like the bars do. I'd like to have just a dot at each bar for the positivity in that year, region and provider.

What's the trick?? I've managed the attached 2 graphs, but not what I want yet.

Thanks a lot!

fig 1.png
fig 2.png
fig 3.png
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
if you install xl2bb and paste your tabke with that others can work with your data rather than replicating cell by cell
 

sajibo

New Member
Joined
Mar 19, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Thanks for the suggestion mole999!
Here's my data, if anyone's willing to give it a try :)
I've now also tried Jon Peltiers' method described here, but it doesn't work for me since the positivity ratio scale is so much smaller than the number of tests' scale. In Peltiers method, you place everything on the primary axis, which obviously doesn't work in this case.

TtestRegion 1Region 2Region 3Region 4Region 5
SHCGPSHCGPSHCGPSHCGPSHCGP
2011314.741521207.468185128.887572121.60833839.994351823.436001744.969061827.4354328147.451848115.551208
2012332.335631190.05411132.699691119.76001432.295578122.978198750.742528424.3384862107.787451122.775439
2013360.657149162.96602137.166077117.54870334.868390722.390428164.794606522.7189247124.157577118.668797
2014435.273307137.625543125.413475108.32220736.912354119.320439160.988143717.3171422112.22987899.7906056
2015351.426287144.940478106.014596109.04742320.292653138.570550314.654875768.191543795.5923968
2016392.936014153.482446112.086585108.74355121.671224528.814215143.862772516.090073623.458514257.9927492
2017407.584139144.489636123.525813108.20273925.237514430.348386550.680625919.82405431.6444325725.0090786
2018430.058788158.136916109.622821114.89099421.122137632.733819955.421466220.3040459
PtestRegion 1Region 2Region 3Region 4Region 5
SHCGPSHCGPSHCGPSHCGPSHCGP
20110.5%0.8%0.6%0.6%0.2%0.5%1.2%0.9%0.2%0.2%
20120.4%0.7%0.5%0.5%0.2%0.3%0.7%0.8%0.2%0.1%
20130.3%0.7%0.5%0.5%0.2%0.2%0.5%0.6%0.1%0.1%
20140.3%0.7%0.3%0.5%0.4%0.7%1.0%0.8%0.2%0.1%
20150.3%0.7%0.8%0.6%0.4%2.0%1.8%0.4%0.1%
20160.3%0.6%0.7%0.5%0.0%0.4%1.1%2.0%0.6%0.1%
20170.3%0.5%0.4%0.5%0.4%0.2%1.3%1.2%0.0%0.3%
20180.3%0.4%0.4%0.5%1.0%0.3%1.1%1.1%
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
is the second axis configured as a stacked graph
 

sajibo

New Member
Joined
Mar 19, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
In Peltiers method, it's configured as an XY scatter, which is also what i did in my first image in the original post (but without using an offset like Peltier does). In the second image it's 2 sets of grouped columns on a primary and secondary axis. Is that what you meant?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,400
Messages
5,641,924
Members
417,247
Latest member
Chitaah

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top