PLeeeeease help. How to do combo line-column chart

[rdionne]

I need to display vertical lines in my column chart to mark various limits. I know how to do the column chart programmatically, but how do I add the line chart and make vertical lines with it? Is it possible?

 

[rdionne]

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.

 

[Mark W.]

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?

 

[rdionne]

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!

 

[Mark W.]

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

 

[rdionne]

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!

 

[Mark W.]

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

 

[rdionne]

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?

 

[rdionne]

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