Text
Build in-cell histograms and 10-slot progress bars with REPT
Scale large counts down by a divisor for compact REPT("|", ...) histograms, and clamp [====------] progress bars safely with MEDIAN.
Scale frequency counts and clamp fixed-width progress bars
Repeating "|" 350 times overflows column width; dividing by ScalePerTick (e.g., 1 tick per 5 responses -> ROUND(35 / 5, 0) = 7 ticks) keeps in-cell histograms compact across any magnitude.
For fixed 10-character progress bars like [=======---], always clamp filled slots n = MEDIAN(0, 10, ROUND(Pct * 10, 0)) so completion > 100% never passes a negative count (10 - n < 0) to the trailing REPT("-", 10 - n) (#VALUE!).
| Responses (A) | ScalePerTick (B) | CompletionPct (C) | Progress Bar |
|---|---|---|---|
| 35 | 5 | 0.7 | [=======---] |
| 35 | 5 | 1.4 | [==========] (clamped) |
| 35 | 5 | 0 | [----------] |
Clamped 10-slot progress bar and scaled histogram formulas
Use a monospace font (Consolas, Menlo, or Courier) so "=" and "-" occupy identical pixel widths.
=LET(n, MEDIAN(0, 10, ROUND(C2 * 10, 0)), "[" & REPT("=", n) & REPT("-", 10 - n) & "]")Scaled tick histogram formula
35 responses at 5 responses per tick renders "|||||||" (7 ticks).
=IF(B2<=0, "Invalid scale", REPT("|", MIN(100, MAX(0, ROUND(A2 / B2, 0)))))Inputs and interpretation
The histogram caps output at 100 ticks to keep large inputs from creating huge strings. Values above the cap cannot be compared visually by bar length; show the numeric count alongside. Use a positive scale and numeric inputs. The progress bar is a ten-slot rounded display, not an exact percentage.
Try the example
Use the original values or change them to test your understanding.