+ Reply to Thread
Results 1 to 3 of 3

Range bars, not error bars

  1. #1
    Registered User
    Join Date
    02-06-2010
    Location
    Leicester, England
    MS-Off Ver
    Excel 2007
    Posts
    1

    Range bars, not error bars

    I'm trying to create individual range bars for each data point in a series on a line chart. For example, the first data point is 8.3, but I need the range to show 7.9 to 9. The second data point is 10.9 with a range of 10.4 to 11.1, and so on. I haven't found a way to use Excel error bars as range bars, because it only lets you format the bar for each data point so that the positive and negative values differ by the same amount (e.g. + or - .6, not + .8 and - .5). Is there a way to do this?

  2. #2
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,155

    Re: Range bars, not error bars

    Right click on your series. FormatDataSeries>Y error bars has a custom error bar option.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  3. #3
    Registered User
    Join Date
    11-14-2010
    Location
    Arkansas, United States
    MS-Off Ver
    Excel 2011
    Posts
    1

    Re: Range bars, not error bars

    @Anselm

    I had the same issue with creating real range bars, but I learned a way to do it. It has a few steps, but it's worth the effort.

    (Note that these directions were made using Excel for Mac 2008, but you should be able to make it work for Windows too. The buttons just might be in a slightly different place.)

    1. Make a column for the minimum and maximum values for each set of numbers. Label them "Real Minimum" and "Real Maximum."
    2. Make a column of the numbers you want to graph (this is usually an average).
    3. Make a fourth column and label it "Relative Minimum." Use the formula bar to subtract the value of the Real Minimum from the average. This will give you a number representing the "relative distance" of the minimum from the data point. Copy this formula for your other data points.
    4. Make a fifth column and label it "Relative Maximum." Again, use the formula bar, but this time subtract the average from the Real Maximum. This will give you a number representing the relative distance of the maximum from the data point.
    5. Right-click on the line of the data series in your graph and click on "Format Data Series."
    6. Click "Error Bars."
    7. Click "Both" in the "Display" section, "Cap" in the "End Style" section, and "Custom:" in the "Error amount" section.
    8. Click the "Specify Value" button.
    9. For the "Positive Error Value," click the little box to the right of the field and click and drag to select all the cells under the column you labeled "Relative Maximum."
    10. Click the little box again.
    11. For the "Negative Error Value," again click the little box to the right of the field, then click and drag to select all the cells under the column labeled "Relative Minimum."
    12. Click the little box again.
    13. Click "OK," and "OK" again to accept the changes made to the graph.

    You should now have range bars added to your graph! It does seem intense, but it's worth the effort. It can be done very quickly when you know what you need to do, but you still might want to create a macro for it.

    Hope this answers your question!
    Last edited by bscheuter; 11-14-2010 at 12:40 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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