It’s 3:12 PM. You’re reviewing Q2 growth metrics for six regional distributors. Finance sent raw revenue numbers — $18,450, $92,700, $4.2M — and asked for "log-scaled growth rates" by tomorrow. You type =LOG(A2) and get #NUM! in row 4. Your cursor blinks. No error explanation. Just red.
The Setup
You’re working with Distributor Growth Data (Sheet1, A1:D10). This isn’t dummy data — it’s actual Q2 2024 figures pulled from SAP exports. Note the zeros, negatives, and one blank — all real-world landmines.
| Distributor | Q2 Revenue ($) | YoY Δ (%) | Region |
|---|---|---|---|
| AlphaTech Solutions | 18450 | 12.3 | APAC |
| Nexus Logistics | 92700 | -4.1 | EMEA |
| Veridian Dynamics | 4200000 | 31.7 | Americas |
| Orion MedSystems | 0 | 0.0 | APAC |
| StellarGrid Inc | -14200 | -22.6 | EMEA |
| Cobalt Edge Ltd | 295000 | 18.9 | Americas |
| TerraForm Energy | 7.2 | APAC | |
| Lumina Labs | 67800 | -1.3 | EMEA |
| AstraCore Group | 1250000 | 44.0 | Americas |
The Challenge
Your goal: compute base-10 logarithms of Q2 Revenue (column B) for visualization — but only where mathematically valid. That means no log of zero, no log of negative numbers, no log of blanks. And you need consistency: if someone changes the base later (say, to natural log), the formula must adapt cleanly.
Here’s what makes this tricky: Excel’s LOG() function doesn’t auto-skip invalid inputs. It errors hard. Also, =LOG(B2,10) works fine in B2, but if you drag it down and hit B5 (−14,200), you get #NUM!. Worse — if you forget to lock the base argument and copy =LOG(B2,B1) where B1 is empty, Excel treats the empty as 0 → #DIV/0!.
The beauty of this approach is that it turns error-handling into part of the logic — not an afterthought.
Walking Through It
We’ll build the solution in three layers. Start in cell E1: label it "Log₁₀ Revenue". Then go to E2.
Step 1: Trap the obvious failures
Enter this in E2:=IF(OR(B2<=0,ISBLANK(B2)),"N/A",LOG(B2,10))
Press Ctrl+Enter (not Enter — keeps focus in E2 for easy dragging). This checks for ≤0 or blank *before* calling LOG().
| B2 (Revenue) | E2 (Formula Output) |
|---|---|
| 18450 | 4.266 |
| 92700 | 4.967 |
| 4200000 | 6.623 |
| 0 | N/A |
| -14200 | N/A |
Step 2: Add precision & formatting
Select E2:E10 → Right-click → Format Cells → Number tab → Decimal places: 3. Or faster: Alt+H, 9, 3.
Step 3: Make it reusable across bases
Type "10" in cell G1. Now revise E2 to:=IF(OR(B2<=0,ISBLANK(B2)),"N/A",LOG(B2,$G$1))
That $G$1 absolute reference lets you change the base once — say to 2 or EXP(1) — and all rows update instantly. Try typing "2.71828" in G1. Watch E2 become 9.817 (natural log of 18450).
What makes this elegant is how it separates validation (is this number even log-able?) from calculation (what’s its log value?). Most people cram both into one nested IF — unreadable and fragile.
The Result
Here’s your final Log₁₀ Revenue column — clean, consistent, and ready for charts or further analysis:
| Distributor | Q2 Revenue ($) | Log₁₀ Revenue |
|---|---|---|
| AlphaTech Solutions | 18450 | 4.266 |
| Nexus Logistics | 92700 | 4.967 |
| Veridian Dynamics | 4200000 | 6.623 |
| Orion MedSystems | 0 | N/A |
| StellarGrid Inc | -14200 | N/A |
| Cobalt Edge Ltd | 295000 | 5.470 |
| TerraForm Energy | N/A | |
| Lumina Labs | 67800 | 4.831 |
| AstraCore Group | 1250000 | 6.097 |
What Could Go Wrong
Three mistakes I’ve debugged in live files — all causing silent corruption or misreporting:
- Mistake #1: Using =LOG(B2) without specifying base
This defaults to base 10 — only if you’re on English-language Excel. On German or French versions, =LOG() means natural log. Always specify the base explicitly: =LOG(B2,10) or =LN(B2). - Mistake #2: Forgetting that LOG() expects positive numbers — not just non-zero
Zero returns #NUM!, yes — but so does −0.0001. And Excel treats "−0" (typed manually) as negative. Test with =SIGN(B5) before logging. - Mistake #3: Copying =LOG(B2,C2) where C2 contains text like "base 10"
Excel converts "base 10" to 0 → #DIV/0!. Even worse: if C2 is "10", it works — but if someone edits it to "10.0", Excel sees it as text → #VALUE!. Always wrap the base argument in VALUE(): =LOG(B2,VALUE(C2)).
Here’s your quick-reference cheat sheet — print it or pin it:
| Task | Formula | Notes |
|---|---|---|
| Base-10 log (safe) | =IF(B2>0,LOG(B2,10),"N/A") | Excludes zero & negatives |
| Natural log (safe) | =IF(B2>0,LN(B2),"N/A") | LN() has no base arg — cleaner |
| Log base 2 | =IF(B2>0,LOG(B2,2),"N/A") | Useful for binary scaling |
| Dynamic base (cell G1) | =IF(B2>0,LOG(B2,$G$1),"N/A") | Change G1 → all recalc |
| Log difference (growth) | =IF(AND(B2>0,B1>0),LOG(B2,10)-LOG(B1,10),"N/A") | Useful for % change in log space |
