PLeeeeease help. How to do combo line-column chart

rdionne

New Member
Joined
Feb 21, 2002
Messages
47
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?
 
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.
 
Upvote 0

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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?
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
I figured it out finally. Thanks for your help amigo. I couldn't have done this without your vast excel knowledge.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top