DUMBBELL

Excel Usage

=DUMBBELL(data, title, xlabel, ylabel, color_start, color_end, grid, legend)

• data (list[list], required): Input data (Labels, Start, End).
• title (str, optional, default: null): Chart title.
• xlabel (str, optional, default: null): Label for X-axis.
• ylabel (str, optional, default: null): Label for Y-axis.
• color_start (str, optional, default: “blue”): Start color.
• color_end (str, optional, default: “red”): End color.
• grid (str, optional, default: “true”): Show grid lines.
• legend (str, optional, default: “false”): Show legend.

Returns (object): Matplotlib Figure object (standard Python) or base64 encoded PNG string (Pyodide).

Examples

Example 1: Simple dumbbell plot

Inputs:

data
Product A	20	35
Product B	15	40
Product C	25	30

Excel formula:

=DUMBBELL({"Product A",20,35;"Product B",15,40;"Product C",25,30})

Expected output:

"chart"

Example 2: Dumbbell plot with labels and grid

Inputs:

data			title	xlabel	ylabel	grid
Q1	100	150	Quarterly Performance	Sales	Quarter	true
Q2	120	180
Q3	110	160

Excel formula:

=DUMBBELL({"Q1",100,150;"Q2",120,180;"Q3",110,160}, "Quarterly Performance", "Sales", "Quarter", "true")

Expected output:

"chart"

Example 3: Dumbbell with custom colors

Inputs:

data			color_start	color_end
Before	10	25	black	orange
After	15	30

Excel formula:

=DUMBBELL({"Before",10,25;"After",15,30}, "black", "orange")

Expected output:

"chart"

Example 4: Dumbbell plot with legend

Inputs:

data			legend	grid
Item 1	5	15	true	true
Item 2	8	20
Item 3	12	22

Excel formula:

=DUMBBELL({"Item 1",5,15;"Item 2",8,20;"Item 3",12,22}, "true", "true")

Expected output:

"chart"

Python Code

import sys
import matplotlib
IS_PYODIDE = sys.platform == "emscripten"
if IS_PYODIDE:
    matplotlib.use('Agg')
import matplotlib.pyplot as plt
import io
import base64
import numpy as np

def dumbbell(data, title=None, xlabel=None, ylabel=None, color_start='blue', color_end='red', grid='true', legend='false'):
    """
    Create a dumbbell plot (range comparison) from data.

    See: https://matplotlib.org/stable/gallery/lines_bars_and_markers/timeline.html

    This example function is provided as-is without any representation of accuracy.

    Args:
        data (list[list]): Input data (Labels, Start, End).
        title (str, optional): Chart title. Default is None.
        xlabel (str, optional): Label for X-axis. Default is None.
        ylabel (str, optional): Label for Y-axis. Default is None.
        color_start (str, optional): Start color. Valid options: Blue, Black, Green. Default is 'blue'.
        color_end (str, optional): End color. Valid options: Red, Orange, Cyan. Default is 'red'.
        grid (str, optional): Show grid lines. Valid options: True, False. Default is 'true'.
        legend (str, optional): Show legend. Valid options: True, False. Default is 'false'.

    Returns:
        object: Matplotlib Figure object (standard Python) or base64 encoded PNG string (Pyodide).
    """
    def to2d(x):
        return [[x]] if not isinstance(x, list) else x

    try:
        data = to2d(data)

        if not isinstance(data, list) or len(data) < 1:
            return "Error: Data must be a non-empty list"

        # Extract labels, start and end values
        labels = []
        start_vals = []
        end_vals = []
        for row in data:
            if not isinstance(row, list) or len(row) < 3:
                continue
            try:
                labels.append(str(row[0]))
                start_vals.append(float(row[1]))
                end_vals.append(float(row[2]))
            except (ValueError, TypeError):
                continue

        if len(labels) == 0 or len(start_vals) == 0 or len(end_vals) == 0:
            return "Error: No valid data rows found (need 3 columns: Label, Start, End)"

        # Create figure
        fig, ax = plt.subplots(figsize=(8, 6))

        # Create dumbbell plot
        y_pos = np.arange(len(labels))

        # Draw lines connecting start and end
        for i in range(len(labels)):
            ax.plot([start_vals[i], end_vals[i]], [i, i], 'k-', linewidth=2, alpha=0.5)

        # Draw start and end points
        ax.scatter(start_vals, y_pos, color=color_start, s=100, zorder=3, label='Start')
        ax.scatter(end_vals, y_pos, color=color_end, s=100, zorder=3, label='End')

        # Set labels
        ax.set_yticks(y_pos)
        ax.set_yticklabels(labels)

        if title:
            ax.set_title(title)
        if xlabel:
            ax.set_xlabel(xlabel)
        if ylabel:
            ax.set_ylabel(ylabel)

        # Handle grid
        if grid == "true":
            ax.grid(axis='x', alpha=0.3)

        # Handle legend
        if legend == "true":
            ax.legend(loc="best")

        plt.tight_layout()

        if IS_PYODIDE:
            buf = io.BytesIO()
            plt.savefig(buf, format='png', bbox_inches='tight')
            plt.close(fig)
            buf.seek(0)
            img_base64 = base64.b64encode(buf.read()).decode('utf-8')
            return f"data:image/png;base64,{img_base64}"
        else:
            return fig
    except Exception as e:
        return f"Error: {str(e)}"

Online Calculator