Cross-entropy in Excel: BCE and CE step by step

What is in the workbook

The workbook teaches one chain that stands behind the output of any classifier: the network produces a logit, any number from $-\infty$ to $+\infty$; a “translator” (sigmoid for two classes, softmax for $K$ classes) turns it into a probability; and only the probability goes into the loss formula. Each sheet walks this chain from left to right and shows what happens if the middle step is skipped.

The cell colours are the same everywhere:

• yellow: inputs, the labels $y$ and the logits $z$; you can change them
• light blue: the sigmoid or softmax “translator”, a separate step that is not part of the loss formula
• red: the loss formula itself; the lighter, the deeper inside the brackets
• green and red zones in blocks 3: where the logarithms exist and where they do not

The BCE sheet: the computation step by step

Block 1 of the sheet holds five objects of a binary task: the label $y=1$ (“yes”) or $y=0$ (“no”) and the logit $z$ produced by the network. The computation has three steps, like the columns of the table: input → sigmoid → the BCE formula.

$y_i$

the correct answer for object $i$: 1 or 0

$z_i$

the logit, the raw output of the network, any number

$\hat y_i=\sigma(z_i)$

the predicted probability of “yes”

$n$

the number of objects, here $n=5$

The sum over all rows is $\Sigma = -1.496$ and there are $n=5$ objects, so $\mathrm{BCE}=-\Sigma/n = -(-1.496)/5 = 0.299$. The last grey row of the sheet computes the same thing another way, with SUMPRODUCT over both branches, and also gives $0.299$.

Try it (as the sheet suggests)

• Logit of row 1: $2.2 \to 6$. Then $\hat y_1 = 0.998$, the row penalty is $0.002$, almost zero, and BCE falls from $0.299$ to $0.279$.
• Logit of row 1: $2.2 \to -3$ with $y=1$. Then $\hat y_1 = 0.047$ and the row penalty is $3.049$: more than the other four rows together ($1.391$). BCE grows to $0.888$: one confident mistake outweighs everything else.

Logits without sigmoid

Block 2 takes the same labels and logits and feeds the logits into the BCE formula directly, as if $z$ were already a probability. BCE has two branches, $\ln z$ and $\ln(1-z)$, and both must exist in the computation, even the one that is later multiplied by zero.

What came out, row by row:

• Row 1, $z=2.2$: $\ln 2.2 \approx 0.788 > 0$ is a “penalty” with the opposite sign, and $\ln(1-2.2)=\ln(-1.2)$ does not exist.
• Rows 2 and 4, $z<0$: $\ln z$ does not exist; row 5, $z=1.4$: $\ln(1-z)$ does not exist.
• Only row 3 ($z=0.4$) landed in $(0, 1)$ “by chance” and gave a number, $\ln 0.4\approx-0.916$. Even that is meaningless: $0.4$ is a logit, not a probability; the real probability here is $\sigma(0.4)=0.599$.

One such row is enough: a mean with a NaN is NaN, the gradients are NaN, and after the optimizer step the network weights are NaN too. Training is not “bad”, it is dead. The result of the block: BCE without sigmoid is NaN, BCE with sigmoid is 0.299.

Sigmoid solves both problems at once: $\hat y\in(0, 1)$ and $1-\hat y\in(0, 1)$, so both logarithms always exist and both are $\le 0$, which makes every penalty $\ge 0$.

The workbook in the browser

The same computation as in the sheet: change the yellow cells and the rest recalculates.

Two branches and two walls

Block 3 explains why: the table plugs different $x$ into both BCE branches, both probabilities and “logit” values outside $(0, 1)$. The yellow $x$ in the sheet can be changed, and the chart under the table (the sheet draws it for $\hat y$ from $0.02$ to $0.98$) shows both branches in full.

BCE has two “walls”. The $y=1$ branch, the penalty $-\ln x$, goes to infinity near zero: a confident “no” when the right answer is “yes”. The $y=0$ branch, the penalty $-\ln(1-x)$, goes to infinity near one. Between the walls, at $x=0.5$, both branches give the same penalty $\ln 2 \approx 0.693$: the price of complete uncertainty.

Outside $(0, 1)$ everything breaks: for $x\le0$ there is no $\ln x$, for $x\ge1$ there is no $\ln(1-x)$, for $x>1$ the $y=1$ branch gives a negative penalty (at $x=10$ it is $-\ln 10\approx-2.303$ and it keeps falling with no floor), and for $x<0$ the $y=0$ branch breaks the same way: at $x=-1$ its penalty is $-\ln 2\approx-0.693$. A network that is allowed a negative loss will simply inflate the logit.

How sigmoid squeezes a logit

Block 4 shows the “translator” itself. The columns of the sheet are the intermediate steps of $\hat y = 1/(1+e^{-z})$: first $e^{-z}$, then $\hat y$, then $1-\hat y$.

Why the result is always in $(0, 1)$: $e^{-z}>0$ for any $z$, so the denominator $1+e^{-z}>1$ and $0<\hat y<1$. At $z=0$, exactly $\hat y=0.5$: complete uncertainty; large positive $z$ give a confident “yes”, large negative ones a confident “no”. The table is symmetric: $\sigma(-z)=1-\sigma(z)$, so the rows $z$ and $-z$ swap the columns $\hat y$ and $1-\hat y$.

Summary of the BCE sheet

• BCE is cross-entropy for two classes written through a single number $\hat y$: the probability of “yes” is $\hat y$, the probability of “no” is $1-\hat y$.
• The formula knows only about $\hat y\in(0, 1)$. Logits live on the whole line: fed in directly, they give NaN or a penalty with the opposite sign.
• Sigmoid squeezes any logit into $(0, 1)$. Softmax for two classes gives exactly a sigmoid: $\mathrm{softmax}(z_1,z_2)_1=\sigma(z_1-z_2)$; it is one and the same idea.
• Dividing by $n$ is only there so that the loss does not depend on the batch size; the shape of the penalty comes from $-\ln$.
• In code: nn.BCEWithLogitsLoss expects logits (applies sigmoid itself, and more stably), nn.BCELoss expects probabilities.

Pythonimport numpy as np

# labels and logits from the BCE sheet
y = np.array([1, 0, 1, 0, 1])
z = np.array([2.2, -1.4, 0.4, -0.6, 1.4])

# step 2: sigmoid; step 3: the BCE formula
p = 1 / (1 + np.exp(-z))
bce = -np.mean(y * np.log(p) + (1 - y) * np.log(1 - p))
print(round(bce, 3))       # 0.299

# the same straight from logits, stably (as nn.BCEWithLogitsLoss does)
s = np.maximum(z, 0) - z * y + np.log1p(np.exp(-np.abs(z)))
print(round(s.mean(), 3))  # 0.299

# PyTorch:
# nn.BCEWithLogitsLoss() expects logits
# nn.BCELoss() expects probabilities

The Categorical CE sheet: the computation step by step

The second sheet is the same chain for $K=3$ classes. The input is a one-hot label vector ($y_k=1$ only for the correct class, here $k=2$) and three logits. The translator is softmax: it turns logits into probabilities that sum to 1.

$z_k$

the logit of class $k$

$\hat y_k=\dfrac{e^{z_k}}{\sum_j e^{z_j}}$

softmax: the probability of class $k$

$y_k$

1 for the correct class, 0 for the others

$K$

the number of classes, here $K=3$

How the numbers of the light blue column and the $\ln\hat y$ column come about:

1. The exponentials of the logits and their sum:

2. Divide each exponential by the sum; that is softmax. The sheet’s check $\Sigma\hat y = 1$ always holds, whatever the logits:

3. The logarithms of the probabilities. Note that $\ln\hat y_k = z_k - \ln\sum_j e^{z_j}$ and $\ln 11.756 = 2.464$. So all three logarithms are the logits minus one and the same number:

4. In the $y\ln\hat y$ column only one term survives: the one with $y=1$. So CE is $-\ln$ of the probability of the correct class (which is what the grey row of the sheet checks):

Try it (as the sheet suggests)

• Logit of class 2: $2 \to 5$. Then $\hat y_2=0.971$ and $\mathrm{CE}=0.029$, almost zero.
• Logit of class 2: $2 \to -2$. Then $\hat y_2=0.030$ and $\mathrm{CE}=3.505$, a large penalty. In both cases the sum of $\hat y$ stays equal to 1.

Logits without softmax

Block 2 again feeds logits in directly, now into $\mathrm{CE}=-\sum_k y_k\ln z_k$. Here all three logits happen to be positive, so the logarithms exist, but the result is worse than an error: CE without softmax $= -\ln 2 \approx -0.693$, a negative loss, whereas with softmax CE $= 0.464$.

• Logit of the correct class $\to 100$: without softmax the loss is $-\ln 100 \approx -4.605$, at $1000$ it is $-6.908$, and so on with no floor. The network will learn only to inflate this logit. With softmax the loss is bounded below by 0.
• Logit of the correct class $\to -1$: without softmax, an error, since a negative number has no logarithm. With softmax everything is computed; the loss is just large.

The workbook in the browser

The same computation as in the sheet: change the yellow cells and the rest recalculates.

Why only $(0, 1]$

Block 3 of the second sheet is the same “map” as in BCE, but with a single branch $-\ln x$: CE penalizes only the probability of the correct class. The green zone is $(0, 1]$: from a huge penalty near zero to zero at $x=1$. The red zones are logits: for $x\le 0$ there is no logarithm, for $x>1$ the penalty is negative and falls with no floor.

Summary of the Categorical CE sheet

• The CE formula knows only about probabilities: numbers from 0 to 1 that sum to 1.
• Logits are any numbers from $-\infty$ to $+\infty$. Fed into CE directly, they give either an error or a negative loss with no floor.
• Softmax is a translator: it squeezes any logits into $(0, 1)$ with a sum of 1. That is why CE and softmax always come as a pair.
• In code, check who applies softmax: nn.CrossEntropyLoss expects logits (softmax inside), nn.NLLLoss expects log-probabilities (log_softmax).

Pythonimport numpy as np

# logits from the Categorical CE sheet; correct class k = 2 (index 1)
z = np.array([1.0, 2.0, 0.5])
k = 1

lse = np.log(np.sum(np.exp(z)))   # ln Σ e^z
p = np.exp(z - lse)               # step 2: softmax
print(np.round(p, 3), round(p.sum(), 3))
# [0.231 0.629 0.14 ] 1.0
print(round(-np.log(p[k]), 3))    # 0.464
print(round(lse - z[k], 3))       # 0.464: CE = ln Σ e^z − z_k

# PyTorch:
# nn.CrossEntropyLoss() expects logits (softmax inside)
# nn.NLLLoss() expects log-probabilities (log_softmax)

Self-checking practice

The Practice sheet has three calculator tasks: enter the answer in the yellow cell and a ✓ or ✗ appears next to it. The tolerance is $\pm 0.005$; the logarithm is natural. Below are the same tasks right on the page; numbers can be written with a comma or a dot.

To stay within the tolerance, do not round intermediate values to fewer than three decimals. The answer for a term is negative (the logarithm of a probability), while BCE and CE are positive: there is a minus at the end.

Common mistakes

• Logits in a formula that expects probabilities. nn.BCELoss or nn.NLLLoss on top of the raw network output: this is blocks 2 of both sheets, NaN or a negative loss.
• A double translator. Sigmoid before nn.BCEWithLogitsLoss or softmax before nn.CrossEntropyLoss: the function applies it a second time. There is no error, but the probabilities get squeezed towards the middle, the loss almost stops changing, and training stalls.
• The wrong logarithm. The formulas use the natural $\ln$. On a calculator the log key often means $\log_{10}$: $\log_{10}0.8\approx-0.097$ instead of $\ln 0.8\approx-0.223$.
• A lost minus. The logarithm of a probability is negative, so the “sum of the two” column is negative, while BCE and CE are positive: $\mathrm{BCE}=-\Sigma/n$.
• The wrong branch. For $y=0$ the penalty is $-\ln(1-\hat y)$, not $-\ln\hat y$: it is the probability of “no” that is penalized.
• Rounding too early. If you round $e^{z}$ or $\hat y$ to one or two decimals, the answer falls outside the $\pm0.005$ tolerance.

Summary

• Logit → translator → formula: the loss formula understands only probabilities.
• The penalty of an object is always $-\ln$ of the probability of the right answer: $0$ for a confident right answer, $\ln 2\approx0.693$ for complete uncertainty, $+\infty$ for a confident mistake.
• More on the functions themselves in the cheat sheet: BCE, cross-entropy, sigmoid, softmax.

Check yourself

← Materials