+ Reply to Thread
Results 1 to 10 of 10

Controlling Error Bar Heights

  1. #1
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,052

    Question Controlling Error Bar Heights

    Hi,

    I'm using the Error Bar Chart to create schedules & having issues with:

    * Controlling bar heights between 10 to 25 Tasks - Main issue

    -- Main issues is that the task descriptions is almost right on top of each other & I would like to have some visible space between
    -- Also, some task is going below the X or 0 Axis & preferably, I would like to keep all task above the X or 0 Axis
    -- Depending on space, I may consider having a handful of tasks below the X axis

    How can I control bar heights through my example file in the Bar Heights or in Column Q & have my tasks evenly spaced with clear visibility between task descriptions?


    Nice to have options:

    * Have task blocks to left of Y axis. May also want phantom line going across

    Thanks
    Attached Files Attached Files
    MyCon
    -- Using Latest Version of Excel

  2. #2
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,052

    Re: Controlling Error Bar Heights

    Hi,

    Does anyone have a solution on how to control bar heights?

    Thanks

  3. #3
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,052

    Question Re: Controlling Error Bar Heights

    Hi,

    Does anyone have a solution on how to control bar heights?

    Thanks

  4. #4
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,052

    Question Re: Controlling Error Bar Heights

    Hi,

    Does anyone have a solution on how to control bar heights?

    Thanks

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Controlling Error Bar Heights

    It appears that your chart, as it currently exists, is exclusively an XY scatter chart. Changing the "spacing" and/or location for any of the data points should be as simple as changing the x and/or y values for that point. Moving the green diamond points over to the "vertical axis" should be as easy as changing the dates in G44:G68. If you want to move the "data labels" only for the green diamond series over to the "vertical axis", you may need to add an additional data series (a dummy series) that will plot along the vertical axis and assign the data labels to that series instead of the green diamond series. If I understand the changes you want to make, most of those changes will involve changing the x,y values for the existing data series and/or adding additional x,y data series to create the effects you want.

    If you are interested in starting over from scratch, what I see in your chart is something very much like a Gantt chart. Almost all Gantt charts I have seen in Excel have started from a stacked bar chart instead of an XY scatter chart. I know starting over is irritating, but I think you might find it easier to create and maintain the chart if you start with a stacked bar "Gantt" type chart (the "task labels" will automatically be placed along the vertical axis of a stacked bar chart for example), then add the additional effects to that chart.
    Basic Gantt chart: https://support.office.com/en-us/art...rs=en-US&ad=US
    https://www.smartsheet.com/blog/gantt-chart-excel
    More advanced Gantt chart with lines: https://www.experts-exchange.com/que...Date-Line.html
    http://peltiertech.com/Excel/Charts/GanttChart.html
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  6. #6
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,052

    Re: Controlling Error Bar Heights

    Hi MrShorty & Others,

    Yes - My chart is representative of a Gantt Chart except I needed more flexibility.

    With my chart, I have virtually everything I need except "clean spacing" between my tasks or bars. That's all I'm trying to fix.

    For whatever reasons - No matter how "tall" I make the Y Axis, the spacing is off or some tasks goes below the X or 0 axis

    Is there a formula or something that I can control the spacing between 0 to 1000 (more or less if neeced) on the Y axis & will "evenly space" the tasks depending on how many tasks I have?

    Thanks

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Controlling Error Bar Heights

    If I follow your sheet correctly, the cell that currently controls the Y axis spacing is the merged cell Q35, which holds a constant value of 550 and feeds into Q44 and on. I could see entering a formula in that cell that would maybe better reflect the desired spacing. The current value of 550 could be the result of (P35-P36)/(#tasks+1). I could see putting a similar function like this into Q35 instead of the constant value, then adjusting the values in P35, P36, and whatever cell you put the #tasks value to get the spacing you want.

    Is that what you are looking for, or do you need something more complex (perhaps a series of constants/formulas adjacent to Q44+ that will allow for different spacing between each data point)?

  8. #8
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,052

    Re: Controlling Error Bar Heights

    Hi MrShorty,

    I'm not following our formula. I plugged into Q35 & still have major space issues

    Can you send the Excel completed example

    Thanks

  9. #9
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,052

    Re: Controlling Error Bar Heights

    Hi MrShorty & Others,

    I managed to get all my tasks above the X or 0 axis. However, in order to do this, I have to my Y axis to be 15,000 which is also where my first task begins. By using a spacing of 800.

    This at at least gets all my tasks in but my task descriptions are still right on top of each other.

    It doesn't seem to matter if I increase the Y axis or create bigger spacing

    Is there something that will work to give me more space between tasks?

    Thanks

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Controlling Error Bar Heights

    I am still not sure I understand what you are trying to do here.

    In order to get the absolute maximum vertical spacing (and fit within a given chart area/plot area height) for the green diamond series looks like this:

    1) Enter the Y axis maximum in P35 (10000 in your original sample file, 15000 in your new variation)
    2) Enter the Y axis minimum in P36 (0 in your original sample file, I assume it is still 0)
    3) Enter the formula (max-min)/(count-1) which is =(P35-P36)/(S40-1) into Q35

    That will place the Task 1 marker at the top of vertical axis, the Task 17 marker will correspond to the bottom of the vertical axis, and the other markers will be evenly spaced between. In order to get more vertical spacing than that, one would need to increase the chart area and/or plot area heights.

+ 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. Excel - Controlling word - Object Error
    By dntel123 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-28-2009, 11:20 AM
  2. Autofit anf row heights
    By drgogo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-15-2009, 07:25 AM
  3. Run-time error 438 - when controlling Excel remotely
    By longbow007 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-13-2009, 08:33 PM
  4. Different row heights in same row
    By grievousangel in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-09-2009, 06:43 AM
  5. Replies: 1
    Last Post: 06-23-2006, 10:06 PM
  6. Heights
    By Dig in forum Excel General
    Replies: 2
    Last Post: 04-24-2006, 04:00 PM
  7. Error while controlling Word from Excel
    By JFamilo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-14-2005, 09:05 PM
  8. Replies: 3
    Last Post: 01-28-2005, 09:06 PM

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