You can also create a simple confusion table:
To evaluate how well the network performed, calculate the Squared Error for the row: Formula for Error: =0.5 * ($C2 - a_o)^2
). For the rows where the target is 1 , your prediction should now read something like 0.98 or 0.99 . For rows where the target is 0 , your prediction should read close to 0.01 or 0.02 . build neural network with ms excel full
Run the macro. You will watch the total loss value in cell W6 systematically drop toward zero as the network successfully figures out the mathematical solution to the XOR gate logic entirely inside Excel.
Now calculate how changing the hidden layer nodes affects the final outcome. Create columns Y through AA : δ1delta sub 1 (Error Signal for H1cap H sub 1 ): =(V2 * I$2) * (L2 * (1 - L2)) δ2delta sub 2 (Error Signal for H2cap H sub 2 ): =(V2 * I$3) * (N2 * (1 - N2)) δ3delta sub 3 (Error Signal for H3cap H sub 3 ): =(V2 * I$4) * (P2 * (1 - P2)) 5. Training the Network You can also create a simple confusion table:
W3(2)cap W sub 3 raised to the open paren 2 close paren power in I4 , and Bias B(2)cap B raised to the open paren 2 close paren power in I5 . Fill cells I2:I5 with random decimal values. Hyperparameters Learning Rate (
𝜕Ŷ𝜕Zoutthe fraction with numerator partial cap Y hat and denominator partial cap Z sub o u t end-sub end-fraction ): =R2 * (1 - R2) =T2 * U2 (or simply = (R2 - C2) * R2 * (1 - R2) ) Step B: Hidden Layer Gradients Run the macro
Congratulations – you have successfully trained a neural network inside Excel!
Once you've verified the formulas work for the first data sample, copy them down for all the samples in your training set.
Wait, let's make that dynamic. Better formula for H1 (cell C2 ): = SUMPRODUCT($A2:$B2, B5:B6) + B7 (Copy this formula across to D2, E2, F2 for H2, H3, H4. Excel will automatically shift the weight columns because of the relative/absolute mix.)