Nice—this is already solid. I’ll extend it with the two upgrades you hinted at so it’s more production-ready for your workflow.

---

## ✅ Version 3: Handles missing `#` automatically

This safely normalizes input like `7b7b00` or `#7b7b00`.

```excel

=LET(

hexRaw, A1,

hex, IF(LEFT(hexRaw,1)="#", hexRaw, "#"&hexRaw),

r, HEX2DEC(MID(hex,2,2))/255,

g, HEX2DEC(MID(hex,4,2))/255,

b, HEX2DEC(MID(hex,6,2))/255,

max, MAX(r,g,b),

min, MIN(r,g,b),

d, max-min,

l, (max+min)/2,

h, IF(d=0,0,

IF(max=r, MOD((g-b)/d,6),

IF(max=g, (b-r)/d+2,

(r-g)/d+4)))*60,

s, IF(d=0,0, d/(1-ABS(2*l-1))),

"hsl(" & ROUND(h,1) & ", " & ROUND(s*100,1) & "%, " & ROUND(l*100,1) & "%)"

)

```

---

## ✅ Version 4: Auto-spills H, S, L into 3 columns

This is ideal for palette building or downstream logic (sorting, filtering, thresholds).

```excel

=LET(

hexRaw, A1,

hex, IF(LEFT(hexRaw,1)="#", hexRaw, "#"&hexRaw),

r, HEX2DEC(MID(hex,2,2))/255,

g, HEX2DEC(MID(hex,4,2))/255,

b, HEX2DEC(MID(hex,6,2))/255,

max, MAX(r,g,b),

min, MIN(r,g,b),

d, max-min,

l, (max+min)/2,

h, IF(d=0,0,

IF(max=r, MOD((g-b)/d,6),

IF(max=g, (b-r)/d+2,

(r-g)/d+4)))*60,

s, IF(d=0,0, d/(1-ABS(2*l-1))),

{

ROUND(h,1),

ROUND(s*100,1),

ROUND(l*100,1)

}

)

```

➡️ Output:

- Column 1 → Hue

- Column 2 → Saturation (%)

- Column 3 → Lightness (%)

---

## ✅ Version 5 (Advanced): Works on a whole column (ARRAYFORMULA-style)

Drop this in row 1 (or next to your data), and it processes a full list of hex codes in column A:

```excel

=MAP(A1:A,

LAMBDA(hexRaw,

IF(hexRaw="","",

LET(

hex, IF(LEFT(hexRaw,1)="#", hexRaw, "#"&hexRaw),

r, HEX2DEC(MID(hex,2,2))/255,

g, HEX2DEC(MID(hex,4,2))/255,

b, HEX2DEC(MID(hex,6,2))/255,

max, MAX(r,g,b),

min, MIN(r,g,b),

d, max-min,

l, (max+min)/2,

h, IF(d=0,0,

IF(max=r, MOD((g-b)/d,6),

IF(max=g, (b-r)/d+2,

(r-g)/d+4)))*60,

s, IF(d=0,0, d/(1-ABS(2*l-1))),

"hsl(" & ROUND(h,1) & ", " & ROUND(s*100,1) & "%, " & ROUND(l*100,1) & "%)"

)

)

)

)

```

---

## Small but important edge-case note

- Pure grayscale (like `#777777`) → Saturation correctly returns `0`

- Hue is mathematically undefined in that case → we set it to `0` (standard practice)

---

If you want, I can take this one step further for your color-coded storytelling system:

- map HSL → emotional categories

- normalize palettes across contributors

- or build a “genkiness gradient” scoring system tied to lightness + saturation shifts

Just say the direction.

### ✅ Result

hsl(60, 100%, 24.1%) → `#7A7A00`

---

### 🧠 Quick breakdown

- Hue 60° = yellow

- 100% saturation = fully vivid

- 24.1% lightness = fairly dark → pushes toward an olive/mustard tone

Rounded to nearest integer RGB:

- R ≈ 122

- G ≈ 122

- B = 0

→ Hex: #7A7A00

---

If you want, I can give you the exact reverse formula for Google Sheets too (HSL → HEX) so your system stays fully round-trip consistent.

### ✅ Result

hsl(60, 100%, 24%) → `#7A7A00`

---

### 🔍 Why it’s the same as 24.1%

That tiny 0.1% drop doesn’t change the rounded RGB output:

- R = 122

- G = 122

- B = 0

→ Hex stays `#7A7A00`

You’d only see a hex change once the lightness shift crosses a rounding boundary (≈ ±0.5 in RGB space).

---

If you want ultra-precise (non-rounded) mapping for your storytelling system, I can give you a version that preserves fractional RGB before hex conversion.

Great catch — this is a real rounding mismatch, not you doing anything wrong.

### ✅ What happened

You started with:

- Hex: `#7B7B00`

→ RGB: (123, 123, 0)

When converted to HSL, that becomes approximately:

- HSL: `hsl(60, 100%, 24.1176%)`

But I gave you:

- `hsl(60, 100%, 24.1%)` (rounded)

---

### ⚠️ Where the mismatch comes from

When you went back:

- You used 24% (or 24.1%)

- But the true value needed to reconstruct `#7B7B00` is:

```

24.1176% (not 24% or even exactly 24.1%)

```

That tiny difference matters because:

- HSL → RGB involves multiplication + rounding to integers (0–255)

- Small decimal shifts can flip:

- 123 → 122 (what happened)

‍ ‍

So you got:

- (122,122,0) → `#7A7A00` instead of `#7B7B00`

---

### 🔬 The key insight

This conversion is not perfectly reversible because:

- HEX = integer RGB (0–255)

- HSL = floating point math

- Rounding happens twice:

1. When converting to HSL (you rounded 24.1176 → 24.1 or 24)

2. When converting back to RGB (floats → integers)

---

### ✅ Exact round-trip values

If you want perfect reversibility, use:

```

#7B7B00 ↔ hsl(60, 100%, 24.1176%)

```

NOT:

- 24%

- 24.1%

---

### 🧠 Rule of thumb for your system

For your color-coded storytelling (this actually matters a lot for consistency):

- ✅ Store HEX as the source of truth

- ✅ Treat HSL as a derived / display format

- ⚠️ Avoid rounding Lightness if you need reversibility

- ✅ Keep at least 4 decimal places for L (and S if needed)

---

### 🧩 Why this matters for your use case

Since you're using hex codes as semantic identifiers:

- `#7B7B00` and `#7A7A00` are different "stories"

- But visually almost identical → dangerous drift

So rounding = identity corruption in your system

---

If you want, I can build you a Google Sheets-safe reversible pipeline where:

- HEX → HSL (high precision)

- HSL → HEX (guaranteed same output)

That would lock your narrative encoding system so nothing silently mutates.