A /29 that cannot hold eight devices, and a register that loses its part codes
A fabrication unit is networking its shop floor. The existing LAN is numbered 192.168.10.0/29. Eight devices must be connected: five CNC machines with built-in Ethernet ports (already set to 192.168.10.1 to 192.168.10.5 by the OEM), one file-and-print server, one router interface acting as the gateway, and one supervisory HMI terminal. The contractor says the remaining three can be 'fitted anywhere in the same range'. Meanwhile the store keeper keeps a stock register in a spreadsheet. Part codes such as 0074, 0321 and 1180 are typed into column A, and the description is fetched from a rate list on a second sheet using VLOOKUP. Since last month parts 0074 and 0321 return #N/A, and after the register was sorted by quantity several descriptions shifted by one row. The store keeper blames the spreadsheet and wants to go back to a paper register.
1. Show how many devices the /29 can actually address, explain why the contractor's proposal fails, and recommend a subnet that works with the least disturbance.
Read the worked answer
/29 leaves 3 host bits: 2³ = 8 addresses. The all-zeros address (192.168.10.0) is the network and the all-ones (192.168.10.7) is the broadcast, so only six are assignable: .1 to .6.
Eight devices need eight addresses, so the plan fails by two. The contractor's suggestion of fitting the extras 'anywhere in the same range' would put them on .8, .9 and so on - outside the /29, on a different subnet, unreachable without a router.
Use /28: 4 host bits, 16 addresses, 14 usable, covering 192.168.10.0 to 192.168.10.15 with mask 255.255.255.240. The five OEM addresses .1 to .5 remain valid, so no machine has to be renumbered - the decisive point, because re-addressing OEM equipment usually means a service visit. Put the server on .6, the HMI on .7, the gateway on .14, and leave .0 and .15 alone. Every device, including the existing five, must be re-masked to 255.255.255.240, and all must sit inside the same /28.
2. Explain why part codes 0074 and 0321 fail while 1180 works, and give the fixes with their trade-offs.
Read the worked answer
0074 is a code, but the spreadsheet stores it as the number 74, because a leading zero has no meaning in a number. A VLOOKUP searching for the text '0074' never matches 74, so it returns #N/A. 1180 has no leading zero, so its numeric value and its visual form agree and the lookup succeeds.
Fixes, best first:
- Format the key column on both sheets as Text before entering data, or import the register as text. This is the durable fix.
- Make both sides numeric: VLOOKUP(VALUE(A2), rate!$A$2:$B$500, 2, FALSE). Works only if the rate list is genuinely numeric.
- Prefix entries with an apostrophe as a manual workaround; visible only in the formula bar, but easy to lose when rows are copied out.
A trap to flag: changing the column format to Text after the values exist does not convert them. The old entries remain numbers; they must be re-entered or converted with Text-to-Columns. Part codes, PINs and phone numbers are labels, not quantities, and must be handled as text throughout.
3. Diagnose the one-row shift after sorting and set out the minimum changes that would make the register reliable.
Read the worked answer
Two independent faults produce that symptom.
Approximate matching. If VLOOKUP's fourth argument is omitted or set to TRUE, it performs an approximate match and silently assumes the first column of the table is sorted ascending. Sorting the register re-orders rows underneath the formula, so the approximate search lands on a neighbouring row and returns the wrong description - always 'one row out', never random. Use FALSE or 0 for an exact match.
Relative table references. If the table_array was written without dollar signs, copying or inserting rows drags the window down by one, so it no longer spans the whole list. Use $A$2:$B$500.
Minimum reliable setup: keep the rate list on its own locked sheet, reference it absolutely, use exact match only, and if the list has to be re-sortable, convert it to a table and use INDEX/MATCH or XLOOKUP. Sorting a register must never change what a part number resolves to. The paper register would restore the symptom, not the cure.