CODEWITHSUNDEEP
excel
user1723583
user1723583

Reputation: 573

Drawing interval charts in excel

Is it possible to draw such charts in excel? How? enter image description here

Upvotes: 4

Views: 383

Answers (2)

Tie Cheng
Tie Cheng

Reputation: 58

JavaScript has powerful libraries such as Highcharts and D3 to draw graphs like this, and Funfun enables to code JavaScript in Excel. Try this solution with Highcharts on the website, and tell me if it's what you want:

https://www.funfun.io/1/edit/59f9d122ff5aa66c5ca8b9ee

Once you are satisfied with a solution, you can directly load it into your Excel by the same link, thanks to the Funfun Excel add-in. Here are screenshots:

enter image description here

enter image description here

Of course, you can edit the data and the code in Excel as you want:

enter image description here

Upvotes: 0

Mikegrann
Mikegrann

Reputation: 1081

This was a fun one. I don't know if there's a good way to extend the functionality of the existing box-and-whiskers charts in Excel, but luckily I was able to come up with a quick-and-dirty way to fake it with a scatterplot. Basically, we're just going to iterate along the intervals and pick enough points to display on a scatterplot that it looks indistinguishable from a box-and-whiskers. Because it makes every interval a different series, you can also format things like coloring to more easily distinguish them.

Here's the end result for some of my data (you can modify the markers used by underlying scatterplot to change things like the line thickness):

Interval Chart Example

The code is looking for a formatted table with names for each series and separate columns for the min/max of each dimension. Here's how the input table is formatted:

Interval Sheet

And lastly, here's the macro I run to generate it:

' Build and display an "Interval Chart"
Public Sub MakeIntervalChart()
    Dim inRow As Long, outRow As Long, lastRow As Long, startRow As Long
    Dim interX As Double, interY As Double, intervalAmt As Double
    intervalAmt = 0.01 ' CHANGE ME

    ' Source / Destination Worksheet Parameters (CHANGE ME)
    Dim wsSource As Worksheet, wsDest As Worksheet
    Set wsSource = Worksheets("Data")
    Set wsDest = Worksheets.Add()
    wsDest.Name = "IntervalChart"

    ' Create output chart
    Dim boxChart As Chart
    Set boxChart = wsDest.Shapes.AddChart2(240, xlXYScatter).Chart
    boxChart.HasLegend = True

    outRow = 1
    lastRow = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row

    For inRow = 2 To lastRow
        ' Retrieve current interval (CHANGE ME)
        Dim seriesName As String
        Dim minX As Double, maxX As Double, minY As Double, maxY As Double
        seriesName = wsSource.Cells(inRow, 1)
        minX = wsSource.Cells(inRow, 2)
        maxX = wsSource.Cells(inRow, 3)
        minY = wsSource.Cells(inRow, 4)
        maxY = wsSource.Cells(inRow, 5)

        startRow = outRow

        'intervalAmt = (maxX - minX) / 50.0

        ' Top and Bottom of box
        For interX = minX To maxX Step intervalAmt
            wsDest.Cells(outRow, 1) = seriesName
            wsDest.Cells(outRow, 2) = interX
            wsDest.Cells(outRow, 3) = minY
            outRow = outRow + 1
            wsDest.Cells(outRow, 1) = seriesName
            wsDest.Cells(outRow, 2) = interX
            wsDest.Cells(outRow, 3) = maxY
            outRow = outRow + 1
        Next

        'intervalAmt = (maxY - minY) / 50.0

        ' Left and Right of box
        For interY = minY To maxY Step intervalAmt
            wsDest.Cells(outRow, 1) = seriesName
            wsDest.Cells(outRow, 2) = minX
            wsDest.Cells(outRow, 3) = interY
            outRow = outRow + 1
            wsDest.Cells(outRow, 1) = seriesName
            wsDest.Cells(outRow, 2) = maxX
            wsDest.Cells(outRow, 3) = interY
            outRow = outRow + 1
        Next

        ' Add new series (box)
        With boxChart.SeriesCollection.newSeries()
            .Name = seriesName
            .XValues = wsDest.Range("B" & startRow & ":B" & outRow - 1)
            .Values = wsDest.Range("C" & startRow & ":C" & outRow - 1)
        End With
    Next
End Sub

You'll want to change things like the input/output sheet names and the expected format of the input table (assuming you don't want to be stuck with my format). You'll also want to tune the 0.01 parameter for your data. I left it with a very small interval you'll be able to use with the sort of sample data you. You can either tweak this increment value to something other than 0.01 depending on your needs, or you can use the code in the commented lines intervalAmt = (maxX - minX) / 50.0 and tweak the 50.0 value (which will be the number of markers to draw for each segment of the boxes). Be careful with tweaking these values - you could easily end up wasting tons of time generating way more markers than you need in order to achieve a straight line.

Upvotes: 1

Related Questions