[RESOLVED] Excel - combo chart scatter and column!

[VBAhack]

Anybody know how to make a combo scatter + column chart so the x-values line up?

The fundamental problem is that a column chart behaves like a "line" chart in that the x-axis corresponds to labels instead of values. If the x-values of the scatter plot are linear, everyting lines up nicely, but in that case there is no reason to have a scatter plot in the first place. If the x-values are not linear the plots don't line up (see attached example where I want each column to line up horizontally with the corresponding data point but clearly doesn't).

Is there some trick to make this work or have I encountered an Excel limitation w/o workaround?

My actual situation is more complex, but the attached illustrates the issue. My data is such that I can't use a line plot, must have a scatter plot.

[Ecniv]

Doing in code?
I added the X axis to each series (same range). Not sure if that will solve your problem.

I do have a different problem with graphs, but I'll post that in another thread.

srs is a series (object)
sht is the sheet with the data
rng is a range object

    Set rng = sht.Range(sht.cells(1, 2), sht.cells(1, 10)) ' b1 - J1
    strCell = "'" & sht.Name & "'!" & rng.addresslocal(True, True)
    srs.XValues = "=" & strCell 'needs a series to add the xvalues to...

[3com]

Just addng related info: http://peltiertech.com/Excel/Charts/ComboCharts.html

[VBAhack]

Thanks for your replies. Yep, already have the same x-axis values for each series. Also, already saw that link. Every example I've seen combining scatter with column has linear data for the scatter x-axis (meaning scatter isn't needed). I'm all but convinced it can't be done (fundamental incompatability between scatter and column). Works OK if I use scatter for both series but I really need columns for the 2nd series. Disappointing.....

[Ecniv]

Hi

I tried some duff data...




Was this what is was meant to do ?

[VBAhack]

Nope. The attached illustrates. I want to use the exact same data (x and y values) for both series. In the left plot, everything lines up but the x-values aren't scaled properly (because I used a line-chart instead of scatter). In the right one, the scatter x-values are scaled properly but the column data is not (treats the x-values as a linear label rather than actual values). What I'm really looking for is a column chart with properly scaled x-values. Doesn't seem to exist.

[vbfbryce]

Ok, so maybe we need to "hack" this, then...Can we "tie the values together" via code, then graph then, maybe? Does that make sense?

[VBAhack]

Found a hack elsewhere by making the 2nd series a scatter plot and adding vertical error bars (100% negative and 0% positive). Thus, the columns are really negative vertical error bars. That makes the legend goofy but I just pasted in a graphic rectangle to make it look right. Total hack but it seems to do the job. Still can't understand why there is no inherent ability in Excel to make such a column chart.........