Scatter chart assistance

[decli]

I have the following columns of data which are pulled from a form:

A Left (number)
B Right (number)
C DNA Gender (text M F)
D Age group (number)
E Height (number)​

I would like to create a scatter chart showing the following:

Gender on the Vertical (aggregated, so that are only two values)
Left AND Right values horizontally (two colors)

Here's a hand drawn example:

https://www.dropbox.com/s/xc6wc1g6kjp6zsq/gender-weights.jpg?dl=0

The goal is to ultimately demonstrate that Male values for Column A & B are greater than Female.

Can someone guide me in how to make this happen please?
I've tried and tried and just can't get everything to connect correctly.

Thank you, Decli

 

[thisoldman]

We need a small example of the data in order to help you.

Is it set up like this,

[TABLE="class: grid, width: 192"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]Left
[/TD]
[TD]Right[/TD]
[TD]Gender[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]78[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]23[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]42[/TD]
[TD]F
[/TD]
[/TR]
</tbody>[/TABLE]

or like this,

[TABLE="class: grid, width: 192"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]Left
[/TD]
[TD]Right[/TD]
[TD]Gender[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]78[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"][/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"][/TD]
[TD]F
[/TD]
[/TR]
</tbody>[/TABLE]

or something completely different?

 

[decli]

Here is a data sample. It's like the top option. Thank you for your assistance

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 100px"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD="align: center"]RIGHT[/TD]
[TD="align: center"]LEFT[/TD]
[TD="align: center"]DNA GENDER[/TD]
[/TR]
[TR]
[TD="align: center"]27.22[/TD]
[TD="align: center"]27.22[/TD]
[TD="align: center"]M[/TD]
[/TR]
[TR]
[TD="align: center"]27.22[/TD]
[TD="align: center"]27.22[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]25.4[/TD]
[TD="align: center"]27.22[/TD]
[TD="align: center"]M[/TD]
[/TR]
[TR]
[TD="align: center"]22.68[/TD]
[TD="align: center"]31.90[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]27.82[/TD]
[TD="align: center"]28.73[/TD]
[TD="align: center"]F[/TD]
[/TR]
</tbody>[/TABLE]

 

[thisoldman]

I have a version as a scatter chart.

The left and right values are the x values in the chart. Copy those two columns, to a blank area of the worksheet. Separate the new columns with two blank columns. We need the blank columns for formulas. Looks like this:

Book1
	A	B	C	D	E	F	G	H	I
1	Original Data				Series 1			Series 2
2	Right	Left	Gender			Right			Left
3	51.77	35.69	M		51.77	3		35.69	3
4	60.88	47.32	M		60.88	3		47.32	3
5	29.56	66.76	M		29.56	3		66.76	3
6	22.74	43.34	F		22.74	1		43.34	1
7	44.34	75.74	F		44.34	1		75.74	1
Sheet1

Cell Formulas
Range	Formula
F3	=IF($C3 = "M", 3, 1)
I3	=IF($C3 = "M", 3, 1)

I used 3 for the y values for M and 1 for the F's y values. The formulas are shown above.

Select the data in columns E and F, from E2:F2 and down in the example. Insert a scatter chart with markers and no line. I formatted this series markers to 7 pts, no border, solid fill, darkish blue with 60% transparency. Don't fuss with other chart object formatting, just yet.

Now select the second series data and Ctrl+c copy it. Next, select the chart. On the ribbon, select the Home tab. Under the big clipboard Paste icon is a small downward pointing triangle—click that triangle. Choose Paste Special. Make sure the buttons and checkboxes look like this, below, and then click OK.

This second series is formatted just like the first but I used a medium-deep red as the base.

Now we set up a series for the divider:

Book1
	K	L
1	Midline
2	My_X	Divider
3	0	2
4	100	2
Sheet1

Select K2 through L4, copy it, then select the chart, and paste the cells into the chart, using the Paste Special from the ribbon, exactly as before.

Format this new series to have a line, 0.75 pts width, almost-black color and no markers.

Format the x-axis to fixed 0–90 scale. Adjust to taste.

Add the legend. Select the legend, then click again directly on top of the Divider legend entry. Press delete. This gets rid of that entry and only the Right and Left series remain in the legend box.

Select the vertical axis. Format it to a fixed scale of 0 to 4. Then we give that axis a custom Number scale. In Excel 2013 and later, its down at the bottom on the same pane as the axis scale options, under "Number".

Select the Category "Custom". In the box Labeled "Format Code", next to the Add button, type in:

[=1]"F";[=3]"M";

The semicolons are important. Then click the Add button. The M and F appear as labels and the numbers disappear

You may have to tweak the plot area size inside the chart. After this step, my axis labels got shifted too far to the left and I had to do a correction.

Remove any gridlines you don't want. Tweak the formatting and it should look something like this:

My workbook may be downloaded from: https://www.dropbox.com/s/4zbi65sgwctke07/declis_chart.xlsx?dl=0

 

[decli]

Oh dear, dear, dear Old Man.... This is exactly what I had hoped for! It's beautiful! The data is being populated from an online jotform into a google sheet - which is loved for it's convenience, but loathed because it's "not" excel. Is there a way to duplicate your magic in google sheets? If not, I can try to find a way to connect share date between the two. Thank you for any further wisdom you can impart. Kindly, Decli

 

[thisoldman]

Thank you for the kind words. I'm glad it fills your expectations.

Unfortunately, I have no experience with Google Docs, other than once visiting it. If you run into difficulties, you can ask for help in a new thread in [on?] these forums, under "General Excel Discussion & Other Questions", and link to this thread.