PLeeeeease help. How to do combo line-column chart - Page 4
Thanks Thanks:  0
Likes Likes:  0
Page 4 of 4 FirstFirst ... 234
Results 31 to 38 of 38

Thread: PLeeeeease help. How to do combo line-column chart

  1. #31
    New Member
    Join Date
    Feb 2002
    Location
    Texas Instruments
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Yes, the line is vertical. The Y axis points are in the correct places. So it just looks like the line is pushed to the left.

  2. #32
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Okay, I've been playing. I'm beginning to understand what's happening... The fact that we're "mixing" a Column Chart type with an XY Chart type appears to be changing the behavior of the SERIES function to which we've assigned an XY Chart type. Keep in mind that ordinarily the x-axis values of a Column Chart type are categorical... in this case "bins" of histogram values. Conversely, the x-axis values of an XY Chart type are continuous.

    So, try this... instead of entering an x-axis value in C1:C2 enter the Nth bin where you want the vertical line to appear. For example, if your x-axis category values were {1,1.5,2,2.5,3) enter a 3 to make the line appear in the 3rd bin (2) rather than using 2 as it's x-axis value. Follow?

  3. #33
    New Member
    Join Date
    Feb 2002
    Location
    Texas Instruments
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That makes alot of sense. I was hoping that I could get more accuracy than that. As long as I have enough bins (the user selects the number), the line placement shouldn't be that far off. How would you figure out which bin the line should be placed in?
    Thanks for the info. I appreciate it!

  4. #34
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-26 16:14, rdionne wrote:
    That makes alot of sense. I was hoping that I could get more accuracy than that. As long as I have enough bins (the user selects the number), the line placement shouldn't be that far off. How would you figure out which bin the line should be placed in?
    Thanks for the info. I appreciate it!
    > I was hoping that I could get more accuracy than that.

    Actually, this provides a great deal of accuracy (precision) because you can specify a fractional part of a bin (e.g., 2.33). So if there's a specific value that you want the vertical line to represent (i.e., the 3rd quartile or the median) you could use a formula to interpolate where that value would fall between 2 adjacent bins. MATCH could be used to find the bin that's the lower of the 2 bins.

    Hey, I think we're going for a world record on duration of this thread!!

    [ This Message was edited by: Mark W. on 2002-02-27 06:29 ]

  5. #35
    New Member
    Join Date
    Feb 2002
    Location
    Texas Instruments
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes, this has been quite an adventure! Can you describe to me how to place the line precisely within a bin once I determine which bin the value would be in?

    Thank You so much for your help!

  6. #36
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Let's say that you want a vertical line to mark the average of the following distribution (in cells A1:B11)...

    {"X","Y"
    ;1,14
    ;2,18
    ;3,28
    ;4,38
    ;5,48
    ;6,55
    ;7,65
    ;8,54
    ;9,34
    ;10,20}

    Since this data is already tallied you can use a weighted average...

    =SUMPRODUCT($A$2:$A$11,$B$2:$B$11)/SUM($B$2:$B$11)

    ...as the x-axis value of your vertical line and use {0,65} as your y-axis values.

    Since this is a unimodal distribution you can use this formula...

    =INDEX(A:A,MATCH(MAX(B:B),B:B))

    ...to determine the placement of a vertical line identifying the distribution's mode.


    [ This Message was edited by: Mark W. on 2002-02-27 10:24 ]

  7. #37
    New Member
    Join Date
    Feb 2002
    Location
    Texas Instruments
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Man, you lost me on that one. Can you simplify that for a dummy like me? My X axis values go from -.2 to +2.3 in 10 bins. I want to draw a vertical line at say 1.6 . How do I do it?

  8. #38
    New Member
    Join Date
    Feb 2002
    Location
    Texas Instruments
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    I figured it out finally. Thanks for your help amigo. I couldn't have done this without your vast excel knowledge.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com