For all you chart lovers out there...

[AOB]

Hi all,

I have an XY Scatter chart in XL2007, plotting account balances as a time-series graph.

The problem I'm having is that Excel will always plot straight lines between each data point. This misrepresents the account balance between the data points

(For example, if the account opens at 9am with €100, and receives €50 at 12pm, the balance appears to climb steadily from €100 to €150 between 9am and 12pm, when in fact it should remain at €100 until 12pm and then jump 'vertically' to €150)

As a workaround, I have added some code to automatically insert 'dummy' values immediately prior to each data point, adopting the value of the previous data point, which forces the chart to show a more representative shape.

However, when the number of transactions on a given day is large, this code obviously takes quite a long time to run. If I want to graph over a number of days, the run-time increases exponentially again.

Just wondering if anybody knows of a chart type in Excel which can produce this kind of shape automatically (i.e. no sloping lines, only horizontal or vertical movements) while maintaining the linear time-series nature of the x-axis?

It's been driving me nuts for days as I would have thought this kind of shape would be common for lots of data types where the y value needs to remain constant until the next data point, rather than moving gradually to its next value.

Any suggestions would be very gratefully received!

Thanks in advance

AOB

 

[Asala42]

How about using a bar chart instead. It sounds like you could get something close to what you're shooting for if you reduce the gap width to 0 and remove the borders. It would look like a surface chart with distinct non-sloping values.

 

[buxtongt]

Would an x-y scatter work for what you need?

 

[shg]

I think you want a step chart. See http://www.andypope.info/charts/stepchart.htm

 

[AOB]

Thanks guys

Asala42 - yeah, I did think of that alright. There are several problems with using a bar chart though I'm afraid. The data points won't be uniformly spaced, and each individual bar will be centered on the data point, again giving a slight misrepresentation of the time at which the balance has changed. (I'd need the data point to be at the top left corner of each bar for it to be fully accurate) I also need to the ability to plot several accounts on the one chart and with a common time axis - a standard bar won't allow that... In any case, when I try to plot it as a standard bar, I just get one giant block of colour...

Buxtongt - I'm already using an XY scatter, but have to 'manually' (okay, via macro, but it's very slow...) interpolate dummy points so that the lines between each data point don't slope upward/downward. Ideally, I would like to keep it as an XY scatter, or something similar, as I want my x-axis to be a linear time-series, which XY scatter is quite handy at producing.

Basically, what I need is an XY scatter with the shape / attributes of a bar chart!

 

[AOB]

shg - that looks just about EXACTLY what I'm looking for!!

Thanks so much for that link!

Getting on for 8pm here so haven't the brain function to dig into it now but will attack that workbook first thing in the morning.

Thanks again, that looks extremely promising!

Very much obliged!

 

[shg]

You're welcome. Jon Peltier also has material on his site: http://peltiertech.com/Excel/ChartsHowTo/StepChart.html

 

[AOB]

Thanks again shg

Gave this a go this morning (with a fresh head) and, while a bit finnicky at the start, it worked pretty much exactly as I'd hoped. It's considerably quicker to add extra columns for the X and Y error values and apply formulas than inserting multiple 'dummy' data points, especially for larger datasets, so this looks like being the perfect answer to my problem!

Here's the catch though...

I can produce the XY chart with error bars manually with no issues. It looks perfect. However, when I try to reproduce the chart using VBA, I'm having difficulty defining the custom ranges for the X and Y error bar data.

I tried recording a macro to see how it was done - but the definition of the custom ranges for the X and Y error bars did not appear anywhere on my recorded code :

Sub adderrorbars()
 
    ActiveChart.SeriesCollection(1).ErrorBars.Select
    ActiveChart.SeriesCollection(1).HasErrorBars = True
    ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlX, Include:= _
        xlPlusValues, Type:=xlFixedValue, Amount:=1
    ActiveChart.SeriesCollection(1).ErrorBars.EndStyle = xlNoCap
    ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlX, Include:= _
        xlPlusValues, Type:=xlCustom, Amount:=0
 
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).ErrorBars.Select
    ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:= _
        xlMinusValues, Type:=xlFixedValue, Amount:=1
    ActiveChart.SeriesCollection(1).ErrorBars.EndStyle = xlNoCap
    ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:= _
        xlMinusValues, Type:=xlCustom, Amount:=0
 
End Sub

I get "Type:=xlCustom" but nowhere does it mention what that custom range was defined as (allowing me to switch in my own 'Range.Address' dynamic variable)

How do I apply defined custom ranges to the error bars using VBA??

 

[AOB]

Sorted!

If anybody else is encountering the same problem, the below is seriously helpful...

http://www.andypope.info/tips/tip014.htm

 

[Lori_M]

AOB - I find the error bar technique for step charts too fiddly for my liking. I recently discovered a quick and direct way to do this which works on any chart type (line, area or xy) and automatically updates if data changes.

Just select the chart with the original data and run the code below to change to a step chart. The only assumption is your data is set up with ranges in columns. (This was tested in Excel 2007+ with several hundred rows.)

Sub ConvertToStepChart()

    Dim s As Series, str As String, i As Integer, j As Integer, n As Integer
    
    Set s = ActiveChart.SeriesCollection(1)
    n = UBound(s.Values) - 2
    str = s.Formula
    
    For i = 1 To 2
        Range(Split(str, ",")(i)).Resize(3 - i).Select
        For j = 0 To n
            ExecuteExcel4Macro "select((selection(),offset(active.cell()," & _
                j - i + 2 & ",," & (j = n) * (i = 1) + 2 & ")))"
        Next j
        If i = 1 Then s.XValues = Selection Else s.Values = Selection
    Next i
    
End Sub