+ Reply to Thread
Results 1 to 10 of 10

How to make a one dimensional chart, like a ruler or timeline, with labels?

  1. #1
    Registered User
    Join Date
    11-16-2014
    Location
    SF Bay Area, CA
    MS-Off Ver
    2003
    Posts
    5

    How to make a one dimensional chart, like a ruler or timeline, with labels?

    I used to think I was good with MSExcel, but I've not done much graphing in many years, and this one has me stumped. I want to make a simple one-dimensional graph which shows various fractional inch values (1/64, 1/32, 3/64, 1/16, etc.) as well as millimeter values (1,2,3, etc.) positioned properly along a horizontal line that is ruled in inches, with each point labeled with it's inch or mm value. This 'ruler' seems very much like a simple timeline having a proportional scale, but I can't find any tips on how to do this, other than as an X,Y scatter plot where each point is a separate series, with all Y-values the same, X-values as the decimal inch conversion, and using the fractional and mm values as series labels.

    Although formatting such a chart to look like the ruler I have in mind is more than a bit awkward, it seems doable. However, I don't know of a way to make the data entry efficient. I can only figure out how to add one series at a time, individually specifying the Label, X-value, and Y-value for point after point. That's horrendous. Is there a better way to do this, or to approach this type of chart?

    This is Excel 2003.
    Last edited by spoolin01; 11-17-2014 at 02:15 AM.

  2. #2
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,071

    Re: How to make a one dimensional chart, like a ruler or timeline, with labels

    Hi spoolin01,

    Is the attached what you're after?

    Cheers
    Attached Files Attached Files
    Remember you are unique, like everyone else

  3. #3
    Registered User
    Join Date
    11-16-2014
    Location
    SF Bay Area, CA
    MS-Off Ver
    2003
    Posts
    5

    Re: How to make a one dimensional chart, like a ruler or timeline, with labels

    I'm not sure the converter got it all. What I see is a blank plot with x-axis from 0 to 13 x1, y-axis from 0 to 14 x2, and the fractions 1/4 and 2/4 printed just above the x-axis, slightly to the right of 7 and 13 respectively.

    I'm looking to plot the decimal equivalents of fractions of an inch, and of mm-converted-to-decimal inches along a one-dimensional axis representing the continuous variable of decimal inches.

    Here's an example with the first four fractions in the series. It took 10 minutes of series defining and chart formatting just to get these 4 points up. I'm trying to figure out how to do this w/o having to define each plotted point as a separate series - creating one series at a time with several clicks per series - and then having to format multiple aspects of each series, one series at a time. As well, an XY plot is really not the objective, and requires a lot of fudging to obscure the Y-dimension. I need a one-dimensional plot, where labeling and formatting for each point can be done globally.

    Surely, there's a one-dimensional timeline-style graph option somewhere, I just can't find it.
    ruler.xls

  4. #4
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,071

    Re: How to make a one dimensional chart, like a ruler or timeline, with labels?

    What I did was similar to yours except I made the Series markers invisible.

    So what you saw was correct, I just made markers on the X axis in millimetres & added another range for the Imperial inches to resemble a ruler but able to be setup quickly.

    As I don't have any other data to work with, could you give me another piece of data or advise what the chart will do/look like?

  5. #5
    Registered User
    Join Date
    11-16-2014
    Location
    SF Bay Area, CA
    MS-Off Ver
    2003
    Posts
    5

    Re: How to make a one dimensional chart, like a ruler or timeline, with labels?

    It's to be a reference for my workshop, which I can inspect having micrometer or caliper measurements made in thousandths of an inch, to determine the nearest categorical match among the various tool, fastener, and material stock sizes. The sizes are the labels (1/64,1/32, etc and 1mm, 2mm, etc.), and the actual decimal inch measurements, in thousandths, lie along the one-dimensional ruler. I could just create a table of correspondences, as textbooks do, but having done that, I thought it might be more visual to represent it as a one-dimensional graph, which is where I've become stuck.

    Unless I'm missing something, your table was a combination of two ordinal series - mm in one column, fractions in another - whereas I need those to be the labels for actual decimal equivalents plotted as a continuous variable along the x-axis. It would resemble those rulers with fractional inches along one edge, mm along the other, but this one would have a third - tenths/hundredths/thousandths of an inch, and all marks would be along one edge.

    I keep going back to the timeline analogy. If I wanted to plot a list of historical events as labels for their dates along a time axis, using a one-dimensional continuous and proportional representation of time, can that be done in Excel? I've tried googling that up, without luck.
    Last edited by spoolin01; 11-17-2014 at 03:23 AM.

  6. #6
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,071

    Re: How to make a one dimensional chart, like a ruler or timeline, with labels?

    Ok, so you have 3 dimensions now.

    Do you mean that you need 3 dimensions on the x - axis?

    I understood your question to be that you wanted a 'Ruler' type parameter on your x-axis.

    I don't think I'm getting where you say you want a timeline for historical events along a time axis along "using a one-dimensional continuous and proportional representation of time"

    Can you explain further?

    Thanks

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,485

    Re: How to make a one dimensional chart, like a ruler or timeline, with labels?

    use a xy-scatter chart.
    number format for X values as fractions
    Vary format of each point
    format horizontal axis to number with 3 digits
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  8. #8
    Registered User
    Join Date
    11-16-2014
    Location
    SF Bay Area, CA
    MS-Off Ver
    2003
    Posts
    5

    Re: How to make a one dimensional chart, like a ruler or timeline, with labels?

    Sorry, didn't mean to make it more confusing. The simplest explanation would be a ruler with one edge, marked in decimal inches (10ths, 100ths, maybe 1000ths). The same edge would also be marked in 64ths of an inch, and also in mm.

    Maybe with the advent of better software and graphics, no one uses simple one-dimensional timelines, but they are analagous to a ruler, where time is the scale, with additional hash marks for the events. A simple horizontal line, marked with dates, and the dates of events.
    Last edited by spoolin01; 11-17-2014 at 11:51 AM.

  9. #9
    Registered User
    Join Date
    11-16-2014
    Location
    SF Bay Area, CA
    MS-Off Ver
    2003
    Posts
    5

    Re: How to make a one dimensional chart, like a ruler or timeline, with labels?

    Quote Originally Posted by Andy Pope View Post
    use a xy-scatter chart.
    number format for X values as fractions
    Vary format of each point
    format horizontal axis to number with 3 digits
    Brilliant!! Well, for as kludgy as Excel is at this task, anyway... I'm able to incorporate the mm series as well, by converting them to proper fractions for the x-series, and using custom labels to get back to the proper mm labeling. There aren't so many of those as to make the whole process too laborious.

    The really slick trick was the "vary colors by point" under Format Data Series/Options, which created individual series out of each data point w/o the series-by-series specification required when doing that during the Data Source specification step.

    I see it may be possible to incorporate custom data markers as well, rather than the default geometric shapes which don't suit the objective here.

    Many thanks! I'm still amazed there's no simple "timeline" style of graph as a default.

  10. #10
    Registered User
    Join Date
    08-09-2016
    Location
    Hamburg
    MS-Off Ver
    10
    Posts
    1

    Re: How to make a one dimensional chart, like a ruler or timeline, with labels?

    Hey everyone! I have a similar problem and could not manage to adapt your tables.

    I'm also trying to scatter plots on a score line (0-100), but instead of only numbers, I have numbers and labels to include.
    Say I have a straight line 0 to 100, and I have 5 points I want to mark on that line (person A, 16 points; person B 43 points; C 46 points; D 49 points; and E 79 points)

    Is there any way to plot it on a straight line and include the name of the person close (up or above) each point?
    Appreciate any help!!! =)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA code to make x-axis labels of an Excel chart be vertical
    By wintermelon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-27-2013, 10:42 PM
  2. Excel 2007 : how to make a timeline scedule?!
    By birdie in forum Excel General
    Replies: 1
    Last Post: 04-11-2012, 12:04 AM
  3. today's date, and how to make a timeline chart
    By e30m20b27 in forum Excel General
    Replies: 1
    Last Post: 02-27-2009, 02:10 PM
  4. How do I make chart labels or legends wordwrap?
    By LC673 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-23-2006, 04:45 PM
  5. Can I link timeline labels to a line chart?
    By hizzle in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-06-2005, 05:40 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1