Specific Graph

[Xenoun]

Hello, I need to re-create a graph that I just have a picture of, using different data. Its a kind of graph with a couple of unique small differences to what I usually use that makes it much better.

Basically, I have data that refers to Left/Right and a date for each point. The graph has two points plotted for each date (left/right) and a vertical line which joins them. Overall this forms a series of vertical lines with square boxes at the end. There is then another solid line graphed which gives the mean value for each point which looks more like a scatterplot without markers.

I'm looking at ways of first being able to plot the left/right data with a vertical line in an easy way. The mean line should be reasonably easy after that. It isn't plausible to plot each pair of left/right data in its own series as there is a lot of data. I'm also considering error bars, if I average each pair, then plot the average with a 50% up/down bar at each point. However I can't quite get my head around how to get excel to only average each pair when the data is listed (if I average a pair then drag down the formula it also calculates the average for non-related pairs)

Thanks for any help

 

[Xenoun]

Bump

 

[tony80]

You might be able to get the effect you're looking for using a stacked bar chart.

If you add an extra column with the movement between Left and Right like in this example:

<table border="0" cellpadding="0" cellspacing="0" width="342"><col style="width: 56pt;" width="75"> <col style="width: 48pt;" span="2" width="64"> <col style="width: 56pt;" width="75"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 56pt;" height="20" width="75">Date</td> <td style="width: 48pt;" width="64">Left</td> <td style="width: 48pt;" align="left" width="64">Right</td> <td style="width: 56pt;" width="75">Movement</td> <td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">01/01/2011</td> <td align="right">73</td> <td align="right">75</td> <td align="right">2</td> <td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">02/01/2011</td> <td align="right">69</td> <td align="right">82</td> <td align="right">13</td> <td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">03/01/2011</td> <td align="right">53</td> <td align="right">74</td> <td align="right">21</td> <td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">04/01/2011</td> <td align="right">64</td> <td align="right">75</td> <td align="right">11</td> <td valign="top">
</td> </tr> </tbody></table>
Create the stacked bar chart using the columns Date, Left and Movement only. Then select the Left series and choose the fill colour "no fill", making it invisible. The result should be a chart with vertical lines between the Left and Right values.

You can make the bars appear thinner by increasing the gap width of the Movement series.

 

[Xenoun]

That's a very good idea, and has come out looking close to what I need, but the bits that are missing are important.

I have the vertical lines that I said I needed, but it is also desirable to have a marker at each end, preferably a different shape to help distinguish between left/right. I also don't see how I can graph a line which represents the average (possibly moving average) between left/right as it's a column(I switched to column for my purposes) graph and you can only get series lines.

I think that I can get closer to what I need by graphing the average of the left/right sides and using error bars at 50%...but then I get the problem of having a rather jagged average line, whereas before I was looking at fitting a trendline to it with a smoother curve. Might still be possible if I play with colour settings. I'm just trying to figure out the best way to automate the average process as the data has come to me rather mixed up.

Originally it was in columns with left/right alternating and the date and other paramaters were also labelled in the column. The values I need respond to rows labelled as L, a and b. These values have a couple extra below them that i dont need, then the rows repeat for different angles of measurements. I've since managed to separate the date as a column on its own, but left/right data has moved into a column called 'side' and is still rather enmeshed. The L, a, b values etc are now in columns of their own.

I just need to figure out how to sort all this data easily to allow it to be averaged. The best layout would be what you have presented already for the bar chart, but that took me a bit of manual moving after a sort to get it into the right format. I really need to be able to automate this process for it to be successful. That's where i'm kind of stuck, i've never really needed to automate things in excel too much. I also know that the error bars will give me the same marker at each end...but the original graph has the same markers, different ones were just trying to improve on it.

 

[Xenoun]

I've just realised error bars won't work too well as the amount the values differ changes for each pair, hence the percentage changes....

back to square one then...i'll try post a picture of the graph if I can later...can't do it where I am right now



Edit: I noticed the custom setting in error bars, used the difference between left/right as the values for the error bars

 

[Xenoun]

Success! Got the graph out now, also discovered you can change the begin/end markers for error bars so that's done too.

If anyone has tips for how to automatically sort the data though that would be great