data visualizationexcel
So I have data like:
Cost 20 30 10 5
Rating 5 3 2 5
I want to make a chart of rating vs. cost, so the points would be
[(5,20), (3,30), (2,10), (5,5)]
I can't seem to get excel to do anything other than put the two rows as independent series. Am I missing something, or I do have to pivot the data somehow to make it do that?
(Actually, I'm using an old-ish version of Numbers.app on OS X, but I'm hoping the concept will be the same. I have access to excel if need be.)
I suggest adding an example or two of what you are presently doing so we can better see what you are dealing with.
What you are concerned with is an important issue: how do you convey the "overall" pattern in the time series data while also not misleading viewers by showing just average values? One way I have dealt with this situation is plotting an average or median line along with surrounding quantile bands. For example,
Here, the time series data are from a bootstrap-based simulation so there are hundreds of values associated with each time point. The actual data are plotted in the black line with colored bands showing the variability of values from the simulation. This particular plot is maybe not the best example to show, but you can see that some points have much more variability than others, and you can also assess how the variability is skewed above/below the actual values depending on the position in the series.
UPDATE: Given your update here are some additional questions and thoughts... What decisions, if any, are made from this visualization? For example, are you looking for specific points in time where there is very slow response time, perhaps above a specific threshold? If so, it may be better to simply plot all of the points as a scatter plot, and then also plot a time series line showing the average value, as well as some lines delineating the bounds you are concerned about. This recommendation is not appropriate if you have numerous observations at some time points (too much clutter), or if your time measurement is not sufficiently coarse (in which case you can bin response data into minute-wide time of day intervals). But the visualization recommendation will certainly be affected by what decision(s) will be supported with it. In my example, I was looking at such plots side by side, one from one simulation and the other from another simulation (each simulation using different parameters) so I could assess the variability of the underlying model due to sampling error.
This web page ("Forest plots : Introduction and explanation") explains how to draw simple forest plots in MS-Excel.
Is your friend conducting a meta-analysis? In that case s/he might be interested in the following two pages:
Best Answer
Select the two rows and do a scatterplot which I think is called an XY plot in Excel (sorry, I run a Linux machine, so I do not have Excel installed).