GAUGE

Excel Usage

=GAUGE(value, title, min_val, max_val, color_ranges)

• value (float, required): Current reading.
• title (str, optional, default: null): Gauge title.
• min_val (float, optional, default: 0): Minimum value.
• max_val (float, optional, default: 100): Maximum value.
• color_ranges (list[list], optional, default: null): Range color zones.

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

Examples

Example 1: Gauge showing mid-range value

Inputs:

value
50

Excel formula:

=GAUGE(50)

Expected output:

"chart"

Example 2: Gauge with custom title

Inputs:

value	title
75	Speed (mph)

Excel formula:

=GAUGE(75, "Speed (mph)")

Expected output:

"chart"

Example 3: Custom min/max range

Inputs:

value	min_val	max_val
150	0	200

Excel formula:

=GAUGE(150, 0, 200)

Expected output:

"chart"

Example 4: Gauge with color zones

Inputs:

value	color_ranges
85	0	50	green
	50	75	yellow
	75	100	red

Excel formula:

=GAUGE(85, {0,50,"green";50,75,"yellow";75,100,"red"})

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
import matplotlib.patches as mpatches

def gauge(value, title=None, min_val=0, max_val=100, color_ranges=None):
    """
    Create a speedometer/gauge style chart.

    See: https://matplotlib.org/stable/gallery/pie_and_polar_charts/pie_and_donut_labels.html

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

    Args:
        value (float): Current reading.
        title (str, optional): Gauge title. Default is None.
        min_val (float, optional): Minimum value. Default is 0.
        max_val (float, optional): Maximum value. Default is 100.
        color_ranges (list[list], optional): Range color zones. Default is None.

    Returns:
        object: Matplotlib Figure object (standard Python) or base64 encoded PNG string (Pyodide).
    """
    try:
        # Validate value
        try:
            val = float(value)
        except (TypeError, ValueError):
            return "Error: Value must be a number"

        # Validate min/max
        if min_val >= max_val:
            return "Error: min_val must be less than max_val"

        # Clamp value to range
        val = max(min_val, min(max_val, val))

        # Create the figure
        fig, ax = plt.subplots(figsize=(8, 5), subplot_kw={'projection': 'polar'})

        # Set up the gauge (semicircle from pi to 2*pi, or 180 to 360 degrees)
        ax.set_theta_zero_location('S')
        ax.set_theta_direction(1)
        ax.set_thetamin(0)
        ax.set_thetamax(180)

        # Remove radial labels
        ax.set_yticks([])
        ax.set_xticks([])

        # Parse color ranges if provided
        if color_ranges:
            def to2d(x):
                return [[x]] if not isinstance(x, list) else x

            ranges = to2d(color_ranges)

            # Draw colored zones
            for range_def in ranges:
                if not isinstance(range_def, list) or len(range_def) < 3:
                    continue

                try:
                    r_min = float(range_def[0])
                    r_max = float(range_def[1])
                    r_color = str(range_def[2])

                    # Convert value range to angle (0 to 180 degrees = 0 to pi radians)
                    theta_min = np.pi * (r_min - min_val) / (max_val - min_val)
                    theta_max = np.pi * (r_max - min_val) / (max_val - min_val)

                    theta = np.linspace(theta_min, theta_max, 100)
                    r = np.ones_like(theta)

                    ax.fill_between(theta, 0, r, color=r_color, alpha=0.3)
                except (TypeError, ValueError):
                    continue
        else:
            # Default gradient from green to yellow to red
            n_segments = 100
            for i in range(n_segments):
                theta_start = np.pi * i / n_segments
                theta_end = np.pi * (i + 1) / n_segments

                # Color gradient: green -> yellow -> red
                ratio = i / n_segments
                if ratio < 0.5:
                    color = (ratio * 2, 1, 0)  # green to yellow
                else:
                    color = (1, 2 * (1 - ratio), 0)  # yellow to red

                theta = np.linspace(theta_start, theta_end, 10)
                r = np.ones_like(theta)
                ax.fill_between(theta, 0, r, color=color, alpha=0.3)

        # Draw the needle
        needle_angle = np.pi * (val - min_val) / (max_val - min_val)
        ax.plot([needle_angle, needle_angle], [0, 0.9], color='black', linewidth=3)

        # Add value text
        ax.text(np.pi / 2, -0.2, f'{val:.1f}', 
                ha='center', va='center', fontsize=20, weight='bold',
                transform=ax.transData)

        # Add min/max labels
        ax.text(0, 1.1, f'{min_val:.0f}', ha='right', va='center', fontsize=10)
        ax.text(np.pi, 1.1, f'{max_val:.0f}', ha='left', va='center', fontsize=10)

        if title:
            plt.title(str(title), pad=20, fontsize=14, weight='bold')

        plt.tight_layout()

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

Online Calculator