chartsmicrosoft excel
I'm trying to create a line chart in Excel with the following values from my sheet:
(0,100)
(1,90)
(2,80)
(4,70)
(8,60)
(16,50)
The problem I can't work around is that I want the 0, 1, 2, 4, 8, 16 to be both labels on the x-axis of my chart as well as the values (i.e., the 4 should be twice the distance from 0 as the 2, the 8 twice the 4, etc.).
When I create the chart in Excel all x-axis values are equally spread.
Is this even possible in Excel?
I don't understand quite. What kind of graph do you want ? This ?
To get this, choose your chart as a linear type (xy scatter group). After that go to select data, and select x and y values by hand from series 1. After that, fix up a little your x axis properties, so the year shows every year, and not every two or so ... Might want to fix up the default look of the graph too.
You've got a couple different things making this difficult for you.
The most obvious, is how Excel handles times. Since you're talking about hours and have your time formatted as time, Excel is reading this as a fraction of a day. In Excel, dates are handled as serial numbers, with a single day having the value 1. So an hour has a value of .0416... This means any time value is going to be interpreted/graphed as a small decimal. So, you'll either need to accomodate their small values, or convert the hours/minutes to a integer/decimal value yourself (e.g. 00:54:05 (hh:mm:ss) = 0.0376 (Excel serial) = 0.9014 (hours)).
The other problem is what your question centers around, and it's Excel behaving properly (believe it or not). Since the values of your categories Distance, Duration, Energy and Heart Rate all have different units, graphing them on a single axis will lead to the distortions you noted. Adding a second axis (as pnuts accurately described) doesn't really help your situation, other than allowing a second "scale" to be superimposed on your first. However, a second (or third or fourth) scale doesn't solve the problem that your putting disparate units on a chart in the same "space" (vertical area of the plot area).
So, what to do? You have three options to get what you're looking for:
Adopt a scale that allows all your units to be plotted in their native scale. Similar to the chart you already have, all you need to do is convert your vertical axis to a log scale. This will allow all of your values to be charted in a much more observable size, but the down side is that vertical differences will be muted at the upper end of each base.
Use small multiples. This allows you to create a number of charts that are identical in all aspects, except Y axis unit/scale. This will allow you to see each category in an environment accurate to it, and relatable to the others. The downside is you'll be making several (four or five) charts for the same data.
Normalize the data into the same unit. In your example, use the starting values (2013-06-03) as a 100% value, and convert all of your other values to a percantage of the starting value, then plot the percentages all on the same scale. The downside is you lose the original unit values.
Sample Excel file: Multiscale Sample
Best Answer
If you use the series (0,1,2,4,8,16) as Horizontal Category (Axis) Labels, Excel will always equally spread the values unless you select one of the Scatter chart types.
After selecting a Scatter chart for your data, you will see that your x-axis labels will spread according to their values.