Two axis charts

[TG2812]

Hello,

I'm trying to get my head around doing a simple chart at first but it turns out to be a nightmare.

I want to create the chart in the first image i uploaded in Excel. My dataset is attached in the second picture.

Any help would be greatly appreciated!

PS: if you could provide your response using an excel sheet, this would be fantastic. Thank you!

 

[myall_blues]

It's a shame you chopped off the axes in your first picture. Can you repost? Also:

• What value do the red crosses represent in your data? I can't seem to match them up with anything.
• Why does your desired output only have 9 bars?

 

[TG2812]

It's a shame you chopped off the axes in your first picture. Can you repost? Also:

• What value do the red crosses represent in your data? I can't seem to match them up with anything.
• Why does your desired output only have 9 bars?

Thank you for your reply.
Regarding the first image and the axes:
- We can see the x axis (primary) at the bottom. It represents the MIN and MAX weight (columns B & C) in my dataset. Should start around 15 and end around 45.
- We can see the x axis (secondary) at the top: It represents the weight (column D) in my dataset. Should start around 1100 and end around 2000.
- The Y axis (which is purposely chopped) only lists the models

The red crosses should represent the weight (column D) of each model.
The desired output should have as many bars as models, so in my case 10 bars (i've been doing so many reworks that I haven't kept tracked of all changes )

 

[myall_blues]

Link to spreadsheet

Probably about the best that can be achieved given the disparity in values between weight attachment and min and max weights. The foundation for this can be found at @Jon Peltier's page here, modified to suit. Note that there is an error on Jon's page at this instruction, as you must also check the box for 'Categories (X Labels) in First Column'.

tg2812.xlsx
	A	B	C	D	E	F	G
1		Weight (min)	Helper	Weight (max)	Weight attachment	Weight attachment (x100)	Height
2	Model1	18	22	40	1450	14.5	0.5
3	Model2	15	15	30	1482	14.82	1.5
4	Model3	16	8	24	1530	15.3	2.5
5	Model4	20	8	28	1390	13.9	3.5
6	Model5	23	9	32	1430	14.3	4.5
7	Model6	18	16	34	1870	18.7	5.5
8	Model7	25	8	33	1700	17	6.5
9	Model8	25	15	40	1280	12.8	7.5
10	Model9	25	15	40	1741	17.41	8.5
11	Model10	20	10	30	1205	12.05	9.5
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=D2-B2
F2:F11	F2	=E2/100

 

[TG2812]

Thank

Link to spreadsheet

View attachment 115180


Probably about the best that can be achieved given the disparity in values between weight attachment and min and max weights. The foundation for this can be found at @Jon Peltier's page here, modified to suit. Note that there is an error on Jon's page at this instruction, as you must also check the box for 'Categories (X Labels) in First Column'.
View attachment 115181

tg2812.xlsx
	A	B	C	D	E	F	G
1		Weight (min)	Helper	Weight (max)	Weight attachment	Weight attachment (x100)	Height
2	Model1	18	22	40	1450	14.5	0.5
3	Model2	15	15	30	1482	14.82	1.5
4	Model3	16	8	24	1530	15.3	2.5
5	Model4	20	8	28	1390	13.9	3.5
6	Model5	23	9	32	1430	14.3	4.5
7	Model6	18	16	34	1870	18.7	5.5
8	Model7	25	8	33	1700	17	6.5
9	Model8	25	15	40	1280	12.8	7.5
10	Model9	25	15	40	1741	17.41	8.5
11	Model10	20	10	30	1205	12.05	9.5
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=D2-B2
F2:F11	F2	=E2/100

Thank you for your reply. This is exactly what I needed. However, after reading the article you included as a reference, I do not understand how height is calculated and what is it for honestly. Could you please explain more simply for dummies ?

 

[myall_blues]

When you add the series that becomes the crosses (the weight) it needs something to use as X values (which is vertical on a bar chart) because it is going to be converted to a scatter plot.
The height is just an X value for each point. Ideally you want it to plot aligned to the middle of each bar that it applies to, so in this case with 10 bars it is set to 0.5, 1.5, …, 9.5.

Does that help?

I also provided a downloadable excel worksheet - checking that you got that?

 

[TG2812]

When you add the series that becomes the crosses (the weight) it needs something to use as X values (which is vertical on a bar chart) because it is going to be converted to a scatter plot.
The height is just an X value for each point. Ideally you want it to plot aligned to the middle of each bar that it applies to, so in this case with 10 bars it is set to 0.5, 1.5, …, 9.5.

Does that help?

I also provided a downloadable excel worksheet - checking that you got that?

Yes, I have downloaded the excel file and it helps. Thank you.

However, I do not understand 1) which data (in my example) is the height derived from? 2) How to generate it, what are the steps to follow in Excel? I tried to follow the article but it is quite complicated ..

 

[TG2812]

As I want to add new models in my list, I do not know how to recalculate the height. Following the same pattern (0.5, 1.5...9.5..10.5), does not work anymore.

 

[myall_blues]

Here's an example with 20 models so you can understand what's going on. Here are our starting data- shaded cells contain formulas.

• As you already know you need a helper column to set the max weight for the bars.
• The weight attachment is divided by 100 just to get it into a usable range of values.
• The height I've calculated as =COUNTA(A$2:A2)-0.5, which is really just the model number or data point minus 0.5. You'll see why shortly.

tg2812.xlsx
	A	B	C	D	E	F	G
1		Weight (min)	Helper	Weight (max)	Weight attachment	Weight attachment (x100)	Height
2	Model1	18	22	40	1450	14.5	0.5
3	Model2	15	15	30	1482	14.82	1.5
4	Model3	16	8	24	1530	15.3	2.5
5	Model4	20	8	28	1390	13.9	3.5
6	Model5	23	9	32	1430	14.3	4.5
7	Model6	18	16	34	1870	18.7	5.5
8	Model7	25	8	33	1700	17	6.5
9	Model8	25	15	40	1280	12.8	7.5
10	Model9	25	15	40	1741	17.41	8.5
11	Model10	20	10	30	1205	12.05	9.5
12	Model11	18	22	40	1450	14.5	10.5
13	Model12	15	15	30	1482	14.82	11.5
14	Model13	16	8	24	1530	15.3	12.5
15	Model14	20	8	28	1390	13.9	13.5
16	Model15	23	9	32	1430	14.3	14.5
17	Model16	18	16	34	1870	18.7	15.5
18	Model17	25	8	33	1700	17	16.5
19	Model18	25	15	40	1280	12.8	17.5
20	Model19	25	15	40	1741	17.41	18.5
21	Model20	20	10	30	1205	12.05	19.5
Sheet1

Cell Formulas
Range	Formula
F2:F21	F2	=E2/100
G2:G21	G2	=COUNTA(A$2:A2)-0.5
C2:C21	C2	=D2-B2

The first thing to do is plot the weight bars. Select A1:C21 and insert a 2D stacked bar, which will give this:

Now select F1:G21, copy it and paste special into the graph using the following settings

Now the graph looks like this:

Right-click on the new bars (the green ones), select 'Change series chart type' and change it to an XY scatter (points only). It will automatically get put on the secondary axis. Here's what it looks like now, but the marker points have run amok. This is where the height value we calculated earlier comes in. The graph currently looks like this:

Right click on the secondary vertical axis, select format axis, and set the axis bounds to 0 and 20 (or whatever your highest model number is). And voila!

Now you can tidy it up - set the markers how you want them, hide the secondary vertical axis, remove the fill from the min weight, add a title etc.

 

[TG2812]

Thank you very much for your reply explanations. It is clearer.

However, I'm following exactly the same steps as you did but for some reasons when I change the axis bounds like in the last steps, the points are still running havoc. I do not know why but they are moving down the graph (see attached picture).

I do not know if one of my excel setting is wrong or not...