Custom x-axis intervals

**svisj** · 05-18-2013, 07:18 AM

Hi,

I have a set of corresponding values where I want to graph one as a function of the other. However, on the x-axis I don't want to display the values that I have, as they are not very "user friendly".

Let's say I have the following values where the first column should represent the x-axis and the second column should represent the y-axis:

0 1024
294,0527664 1083,179809
588,1055327 1154,841288
882,1582991 1150,825888
1176,211065 1131,385909
1470,263832 1135,210046
1764,316598 1122,591419
2058,369365 1138,473892
2352,422131 1147,951293
2646,474897 1127,330905
2940,527664 1127,39093
3234,580432 1129,34369

However, I don't want to any of the x-values to show. I'd rather want specific values, lets say 0-3000 with 500 intervals. How can I do this?

Thanks

**popipipo** · 05-18-2013, 07:39 AM

Use to round the x-values the formula FLOOR or MROUND.
Use these numbers for X-value

**MrShorty** · 05-18-2013, 09:48 AM

What type of plot are you currently using? Have you got a good idea of what kind of graph you are trying to create? What is it supposed to communicate or show?

My first thought (because it is all I ever really use) is to use a scatter plot. A scatter plot treats the x-values like real numbers (not categories like in other chart types). You can control the tick mark and label spacing to appropriate values. Scatter plots are good for showing the relationship between a dependent variable (y) and an independent variable (x). Scatter plots are the "Cartesian" plots that you learned about in algebra, whenever that was. Gridlines could easily be used to section off the graph every 500 units.

My second thought from your description is that you want more of a histogram. You want to "count" or "sum" the results of certain categories or "bins" Then use that value to show the totals for each "bin". One would usually use a function like =SUMIF() or =COUNTIF() or =FREQUENCY() or similar to "tally up" the desired value for each bin or category, then plot that on a line or column chart.

Or maybe there is an option that isn't coming to me off the top of my head. I would suggest a few minutes away from Excel, decide what you want the graph to look like (maybe sketch it on paper), then we can talk specifically about creating exactly what you want.

**svisj** · 05-18-2013, 12:00 PM

Originally Posted by popipipo

Use to round the x-values the formula FLOOR or MROUND.
Use these numbers for X-value

I'm not sure I understand what you mean. Could you please elaborate?

Originally Posted by MrShorty

What type of plot are you currently using? Have you got a good idea of what kind of graph you are trying to create? What is it supposed to communicate or show?

My first thought (because it is all I ever really use) is to use a scatter plot. A scatter plot treats the x-values like real numbers (not categories like in other chart types). You can control the tick mark and label spacing to appropriate values. Scatter plots are good for showing the relationship between a dependent variable (y) and an independent variable (x). Scatter plots are the "Cartesian" plots that you learned about in algebra, whenever that was. Gridlines could easily be used to section off the graph every 500 units.

My second thought from your description is that you want more of a histogram. You want to "count" or "sum" the results of certain categories or "bins" Then use that value to show the totals for each "bin". One would usually use a function like =SUMIF() or =COUNTIF() or =FREQUENCY() or similar to "tally up" the desired value for each bin or category, then plot that on a line or column chart.

Or maybe there is an option that isn't coming to me off the top of my head. I would suggest a few minutes away from Excel, decide what you want the graph to look like (maybe sketch it on paper), then we can talk specifically about creating exactly what you want.

I'm using the line plot, as this is the only viable option. I'm trying to show an elevation profile along a line - in essence height as a function of distance.

**shg** · 05-18-2013, 12:25 PM

You want a scatter plot, not a line chart.

**popipipo** · 05-18-2013, 12:42 PM

This is what I meant.

**svisj** · 05-18-2013, 01:15 PM

Originally Posted by shg

You want a scatter plot, not a line chart.

Maybe. However, it seems to me that it is very hard to combine several chart types when using the scatter plot? To make things worse, I want to colorize "sections" under the graph, like this. excel.jpg Until now I have used 3 plots to do this; 1 line that shows the edge, and two (or more) area plots to colorize. I can't get this to work if I use the scatter plot.

Thanks everybody

**shg** · 05-18-2013, 01:21 PM

If you use a line chart, the x values are taken as categories and spaced at equal intervals. In a scatter chart, the x values must be numbers, and you can set the axis parameters (min, max, units) as you like.

I don't use shaded area charts and have no suggestion for the added wrinkle.