KyleUp
KyleUp

Reputation: 1703

Chart Numbers in Excel with Strings mixed in

I have some data that I'm putting into a chart and formatting. There are some business rules where some of the data is "Protected" like in the example. The issue when graphing with the word "Protected" (or any other word) excel is graphs that point at the bottom of the X-Axis. I'd like the graph to look like the 2nd chart, but I'd like to avoid graphing off a hidden column because I think it would confuse my power users.

Chart Examples

Any thoughts or insights are appreciated!

Upvotes: 13

Views: 1270

Answers (3)

AranDG
AranDG

Reputation: 406

I'm kind of gutted that I couldn't figure the majority of this out, it seems like such a simple premise. I've made somewhat of a workaround but it could definitely do with some refinement;

I'm using a VBA function called Eval:

Public Function Eval(varRange As Range)
Dim varArray() As Variant
varArray = varRange
Dim R As Long
Dim C As Long
For R = 1 To UBound(varArray, 1)
    For C = 1 To UBound(varArray, 2)
        If varArray(R, C) = "Protected" Then
            varArray(R, C) = CVErr(xlErrNA)
        End If
    Next C
Next R
Eval = varArray
End Function

And a defined range called 'Chart' which has the following formula

=EVAL(INDIRECT("Sheet1!$B$2:$B$"&1+COUNTA(Sheet1!$B$2:$B$31)))

This makes the range dynamic, but you could just use =EVAL(B2:B6) if need be.

On your chart, edit the series values range to Sheet1.xlsm!Chart (or whatever your sheet is called), and you get the data range with any "Protected" values converted to blanks. The only thing is, the chart will plot the line between the two points regardless, I cannot make the chart plot the gap. Maybe someone with better google-fu than myself could come up with a solution?

Upvotes: 0

OldUgly
OldUgly

Reputation: 2119

When manually building the chart:

  1. Select the data point
  2. On the Format ribbon, pick Format Selection
  3. On "Format Data Point", choose Line, and select No Line.
  4. Pick the next data point (corresponding to 2013).
  5. On "Format Data Point", choose Line, and select No Line.

Here are my results ...

enter image description here

A small bit of VBA that will generate the chart ...

Sub MakeChart()
Dim cell As Range, mySerRng As Range, mySrcRng As Range
Dim mySht As Worksheet, myChrt As Chart
Dim lastRow As Long

Set mySht = Worksheets("Sheet1")
lastRow = mySht.Range("A" & mySht.Rows.Count).End(xlUp).Row

Set mySerRng = mySht.Range(mySht.Cells(1, 2), mySht.Cells(lastRow, 2))
Set mySrcRng = mySht.Range(mySht.Cells(1, 1), mySht.Cells(lastRow, 2))

Set myChrt = mySht.Shapes.AddChart2(-1, xlLine, mySht.Range("C1").Left, mySht.Range("C1").Top).Chart
With myChrt
    .SeriesCollection.Add Source:=mySrcRng, RowCol:=xlColumns, serieslabels:=True, categorylabels:=True, Replace:=True
    For Each cell In mySerRng
        If cell.Value = "Protected" Then
            .SeriesCollection(1).Points(cell.Row - 1).Format.Line.Visible = False
            .SeriesCollection(1).Points(cell.Row).Format.Line.Visible = False
        End If
    Next cell

End With
End Sub

Alternate approach

Build a scatter chart with multiple series, separated by the "offending" rows, and formatted so they appear to be one series ...

enter image description here

enter image description here

This has disadvantages:

  • Likely more confusing to an end user
  • Requires a lot of series if you have a lot of data with "Protected" scattered throughout

Upvotes: 6

Weasemunk
Weasemunk

Reputation: 455

While I agree with @OldUgly's answer, you could also just add another column next to the data, if you think it would be understandable.

=IF(ISNUMBER($B2),$B2,"")

That should do the trick.

Upvotes: 0

Related Questions