How can I graph this in Excel?
Author
Discussion

TonyRPH

Original Poster:

13,550 posts

199 months

Monday 3rd December 2018
quotequote all
I have some data that I want to graph.

I want the 'Y' axis to be the 1st column, and then the rest of the data represented on the 'X' axis.

I realise I need a bar graph, however I just cannot seem to work out how to do this in Excel.

How can I do this?

The data looks like this:

I/P vRMS Noise Dyn. Range THD (%) IMD+N (%) Crosstalk IMD+N (swept freqs %)
0.400 -97.9 97.9 0.0014 0.0043 -90.3 0.0051
0.500 -99.40 99.60 0.0014 0.0038 -90.30 0.0046
0.600 -100.70 100.70 0.0014 0.0036 -90.00 0.0044
0.650 -101.30 101.30 0.0014 0.0035 -89.40 0.0043


Thanks in advance.

J8 SVG

1,470 posts

161 months

Monday 3rd December 2018
quotequote all
Like this?



Or this?


louiebaby

11,022 posts

222 months

Monday 3rd December 2018
quotequote all
TonyRPH said:
I have some data that I want to graph.

I want the 'Y' axis to be the 1st column, and then the rest of the data represented on the 'X' axis.

I realise I need a bar graph, however I just cannot seem to work out how to do this in Excel.

How can I do this?
It looks like you have two types of data, the "whole" numbers and percentages. If they "whole" numbers are on the same kind of scale, then you can do this by selecting a Combo chart type, which will start off looking like this:



Let me have a bit more of a play...

louiebaby

11,022 posts

222 months

Monday 3rd December 2018
quotequote all
Can you sketch out how you expect it to look and upload a picture of it?

TonyRPH

Original Poster:

13,550 posts

199 months

Monday 3rd December 2018
quotequote all
The bottom one is probably the closest.

I just attempted to draw a mock up, and I realised I probably won't get what I'm after because of the negative numbers I have.

I suspect it's possible with a single graph for every row (or column?) I'm not sure!


deltaevo16

755 posts

202 months

Monday 3rd December 2018
quotequote all
What like this


J8 SVG

1,470 posts

161 months

Monday 3rd December 2018
quotequote all
TonyRPH said:
The bottom one is probably the closest.

I just attempted to draw a mock up, and I realised I probably won't get what I'm after because of the negative numbers I have.

I suspect it's possible with a single graph for every row (or column?) I'm not sure!

That... doesn't correlate with the data set you have

TonyRPH

Original Poster:

13,550 posts

199 months

Monday 3rd December 2018
quotequote all
To keep it simple (and also to avoid typing out the table formatting here on PH!), I posted a small subset of the data I have.

This is a comma delimted export of the full dataset.

I/P vRMS,Noise,Dyn. Range,THD (%),IMD+N (%),Crosstalk,IMD+N (swept freqs %)
0.400,-97.90,97.90,0.0014,0.0043,-90.30,0.0051
0.500,-99.40,99.60,0.0014,0.0038,-90.30,0.0046
0.600,-100.70,100.70,0.0014,0.0036,-90.00,0.0044
0.650,-101.30,101.30,0.0014,0.0035,-89.40,0.0043
0.700,-101.90,101.70,0.0014,0.0034,-88.70,0.0043
0.725,-102.00,101.90,0.0015,0.0034,-88.50,0.0043
0.750,-102.10,102.10,0.0015,0.0034,-88.80,0.0043
0.775,-102.40,102.30,0.0015,0.0034,-88.40,0.0043
0.800,-102.50,102.60,0.0015,0.0034,-88.30,0.0043
0.900,-103.30,103.20,0.0016,0.0033,-87.60,0.0043
1.000,-103.80,103.70,0.0017,0.0033,-87.20,0.0043
1.300,-104.90,104.90,0.0020,0.0034,-86.90,0.0046
1.500,-105.50,105.50,0.0021,0.0035,-86.90,0.0049
1.600,-105.50,105.50,0.0023,0.0035,-87.40,0.0051
1.941,-106.20,106.20,0.0026,0.0039,-88.10,0.0057
2.027,-106.40,106.30,0.0027,0.0040,-88.50,0.0058
2.154,-106.50,106.40,0.0028,0.0043,-88.80,0.0061
2.270,-106.60,106.70,0.0033,0.0048,-89.60,0.0066
3.145,-107.10,107.10,0.0054,0.0076,-102.50,0.0089

And this is what it looks like on the actual spreadsheet.



EDIT: My formatting got screwed moving the data between sheets - so this is a repost.

Edited by TonyRPH on Monday 3rd December 11:20

Halmyre

12,695 posts

170 months

Monday 3rd December 2018
quotequote all
You'd need to plot the percentages on a secondary axis, and that needs a horrible workaround otherwise the secondary axis plot piles on top of the primary axis and obscures it. And even after doing that the result is an unreadable dog's breakfast.

louiebaby

11,022 posts

222 months

Monday 3rd December 2018
quotequote all


Like this?

TonyRPH

Original Poster:

13,550 posts

199 months

Monday 3rd December 2018
quotequote all
louiebaby said:
Like this?
That's pretty close actually.

I would have preferred something close to my earlier illustration, however this is not bad - all I'm really trying to visualise are trends, more than absolute numbers.

Can you share the spreadsheet please?


Herbs

5,132 posts

260 months

Monday 3rd December 2018
quotequote all
I imagine separate graphs will be more suitable because of the disparity in values for the Axis - if you set up as a Pivot graph you can have multiple smaller ones on the same spreadsheet.

louiebaby

11,022 posts

222 months

Tuesday 4th December 2018
quotequote all
TonyRPH said:
That's pretty close actually.

I would have preferred something close to my earlier illustration, however this is not bad - all I'm really trying to visualise are trends, more than absolute numbers.

Can you share the spreadsheet please?
I hadn't saved it, so will have to re-create. Do you know a free, no-sign-up file sharing service? Or can I email you?

TonyRPH

Original Poster:

13,550 posts

199 months

Tuesday 4th December 2018
quotequote all
louiebaby said:
I hadn't saved it, so will have to re-create. Do you know a free, no-sign-up file sharing service? Or can I email you?
I'll send you a PM which will give you my email address.

Thanks.

Vaud

60,030 posts

186 months

Tuesday 4th December 2018
quotequote all
TonyRPH said:
That's pretty close actually.

I would have preferred something close to my earlier illustration, however this is not bad - all I'm really trying to visualise are trends, more than absolute numbers.

Can you share the spreadsheet please?
You could add smoothing as well, and maybe a trend line.

louiebaby

11,022 posts

222 months

Tuesday 4th December 2018
quotequote all
TonyRPH said:
louiebaby said:
I hadn't saved it, so will have to re-create. Do you know a free, no-sign-up file sharing service? Or can I email you?
I'll send you a PM which will give you my email address.
Seen and replied, please let me know when you've got it.

Please note I've set the X Axis as a date, so that the gaps between the data points are proportional to the actual gap. If you look at the first go I had at it, the gap between 0.7 and 0.725 is the same as the gap between 2.270 and 3.145, which could be misleading.

Agree with the smooth line as mentioned by a previous poster, although more sample points would help on this.

TonyRPH

Original Poster:

13,550 posts

199 months

Tuesday 4th December 2018
quotequote all
louiebaby said:
Seen and replied, please let me know when you've got it.

Please note I've set the X Axis as a date, so that the gaps between the data points are proportional to the actual gap. If you look at the first go I had at it, the gap between 0.7 and 0.725 is the same as the gap between 2.270 and 3.145, which could be misleading.

Agree with the smooth line as mentioned by a previous poster, although more sample points would help on this.
Got it and replied thanks.

Much appreciated.

louiebaby

11,022 posts

222 months

Tuesday 4th December 2018
quotequote all
TonyRPH said:
Got it and replied thanks.

Much appreciated.
thumbup