Pilot Hole and Screw Length Calculator (#6, #8, #10 Wood Screws)

Choose what to find, then enter the screw size and the material (for the pilot hole), or the thickness of the board you are attaching (for the screw length). If the ratios are left blank, common rules of thumb are used (softwood 0.65, hardwood 0.75, plywood/MDF 0.7, 2.5 times the board thickness, 0.67 of the base piece).

Units

What to find

Screw type

Common sizes

Screw diameter d (mm)

Material

Ratio (pilot hole ÷ screw diameter, editable)

Step between your drill bit sizes

If you know them (optional)

Screw length (optional)

Flat-head diameter (for a countersink, optional)

The ratios and results are rules of thumb. If the screw maker recommends a pilot hole or length, use that first, and follow the designer or the product specifications for structural screws and connectors. Near the end of a board, in end grain, or in hard wood that splits easily, a slightly larger pilot hole or a test screw in scrap wood is a safe check.

Result and figure

Enter the screw size and the material (or the board thickness) on the left and press "Calculate". The pilot hole size and nearest drill bit (or the right screw length, the nearest standard length and whether it goes through) will appear here, with a cross-section of the screw in the boards.

[ CLOSE ]

What you can do on this page
What is this calculation used for?
How to Use
Formulas and figures
Symbols and terms
- Symbols
- Terms
Good to know before you start
How to calculate it in Excel
How to calculate it in Google Sheets
How to calculate it in Python
How to write it in LaTeX and other math languages (copy and paste)
How to have ChatGPT do the calculation
DataChef Features
Related Features
NumberChef Calculators List

What you can do on this page

Choose the screw size (#6, #8, #10 and so on) and the material (softwood, hardwood, or plywood/MDF), and you get the pilot hole size and the nearest drill bits in your set (such as 1/64 in steps)
Enter the thickness of the board you are attaching, and you get the right screw length from a rule of thumb (2 to 2.5 times the board thickness, or about 2/3 of the thickness of the base piece) and the nearest standard length (1, 1-1/4, 1-5/8, 2, 2-1/2 in and so on)
Enter the thickness of the base piece, and the page checks whether the screw pokes out the back (and how much wood is left)
For flat-head screws you also get the countersink size, and if you enter the screw length, the depth of the pilot hole
The ratios, the drill bit steps and the list of standard lengths can all be changed, so you can match the maker's recommendations and the tools you have

The ratios (the pilot hole as a fraction of the screw diameter, the screw length as a multiple of the board thickness) are common rules of thumb in woodworking and DIY. If the screw maker lists a recommended pilot hole or length, use that first. For materials other than wood (plastic, aluminum and so on) and for structural parts of a building (structural screws, connectors), follow the product specifications or the designer's instructions.

What is this calculation used for?

Building shelves or a workbench from 1×4s and 2×4s (a DIY classic)

A 1×4 is actually 3/4 in thick and a 2×4 is 1-1/2 in. To fasten a 3/4 in board to a 2×4, a screw 2 to 2.5 times the board thickness is 1-1/2 to 1-7/8 in, so 1-5/8 in or 2 in screws are the choices. The penetration is 7/8 to 1-1/4 in, which leaves at least 1/4 in of the 2×4, so the screw does not go through.
Pine is softwood, so the pilot hole for a #8 screw is about 0.164 × 0.65 ≈ 0.107 in, which is 7/64 in. In the middle of a board you can often skip the pilot hole, but within about 1 in of the end, or in end grain, a pilot hole prevents splitting.

Keeping hardwood furniture such as oak or walnut from splitting (woodworking)

Hardwood is dense and takes a lot of force to drive a screw into, so without a pilot hole it can split or the screw can snap. For a #10 screw (0.190 in), the pilot hole is 0.190 × 0.75 ≈ 0.143 in, which falls between 9/64 in and 5/32 in. Near the end of a board or in a thin part, the larger 5/32 in is the safer choice.
In hardwood a flat head will not sink in by itself, so countersink the hole to match the head (about 0.39 in for a #10) before driving the screw. On furniture where the screws show, this extra step keeps the heads flush and the wood around them from cracking.

Hanging shelf brackets or a grab bar on a drywall wall (home projects)

Screws do not hold in drywall itself, so they must be long enough to reach the stud behind it. To go through 1/2 in drywall and at least 1 in into the stud, the screw must be \(0.5 + 1 = 1.5\) in or longer, so 1-1/2 in or 1-5/8 in screws are the choices.
A 2×4 stud is 3-1/2 in deep, so enter 3.5 as the thickness of the base piece to confirm that a long screw will not come out the other side. Find the studs with a stud finder, and for anything that carries weight, such as a grab bar, use the screws named in the product's installation instructions.

Fastening plywood subfloor or deck boards to joists (floors and decks)

To fasten 3/4 in plywood to the joists, 2.5 times the board thickness is 1.875 in, so 2 in screws are the guide, and a penetration of 1-1/4 in leaves plenty of joist. The pilot hole in plywood is about 0.7 times the screw diameter: \(0.164 \times 0.7 \approx 0.115\) in for a #8, which is 7/64 in with a 1/64 in drill set. For 5/4 deck boards (actually 1 in thick), 2.5 times gives 2.5 in, which is why 2-1/2 in deck screws are the usual choice.
For a large floor or deck, the total number of screws is the screws per board times the number of boards. You can find the number of flooring planks with the "Flooring Calculator".

Fastening thin boards face to face or building a box (small projects and storage)

To fasten a 1/2 in board face to face onto a 3/4 in board, 2.5 times the board thickness (1-1/4 in) gives a penetration of 3/4 in, which leaves no wood at all behind the tip, so the back splits or bulges. In this case, set the penetration to 2/3 of the base piece (0.5 in): \(0.5 + 0.5 = 1\) in, so a 1 in screw (1/2 in penetration, 1/4 in left) does not go through.
Thin boards and end grain split easily, so use a thinner #6 screw with a pilot hole (0.138 × 0.65 ≈ 0.090 in, which is 3/32 in), and keep away from the edges.

Using self-tapping screws in plastic, aluminum or sheet metal (electronics, cars and bikes)

For materials other than wood, screw makers publish charts of the recommended pilot hole for self-tapping screws by material and thickness. For plastic it is often about 80 to 90% of the screw diameter, and for aluminum or sheet metal it also depends on the sheet thickness. Enter "recommended pilot hole ÷ screw diameter" from the chart in "Other (enter the ratio)" on this calculator, and you can pick the nearest drill bit the same way.
In metal, a pilot hole that is too small snaps the screw, and one that is too large leaves the threads with nothing to grip, so following the chart matters even more than in wood.

Everyday uses of multiplying by decimals and fractions (elementary and middle school math)

"0.65 times the diameter", "2.5 times the board thickness" and "2/3 of the base piece" are all "amount × multiplier", as taught in elementary school. Rounding a pilot hole to 1/64 in steps is rounding down and up to a multiple of a fraction, and checking whether a screw goes through is comparing a difference with 0. It is a good example of arithmetic that prevents mistakes in real projects.

Formulas and figures

Pilot hole size (screw diameter × material ratio)

Figure

Standard notation (the usual math form)

\(D\) \(=\) \(d\) \(\times\) \(r\)

In words (symbols replaced with words)

③ \(D\): pilot hole \(=\) ① \(d\): screw diameter \(\times\) ② \(r\): material ratio (softwood 0.65, hardwood 0.75, plywood 0.7)

The formula in words

① Take the \(d\): screw diameter

② multiply it by the \(r\): material ratio (0.65 for softwood, 0.75 for hardwood, 0.7 for plywood/MDF)

③ and you get the \(D\): pilot hole size. Then pick the drill bit in your set (such as 1/64 in steps) that is closest to it

Quick example

The pilot hole for a #8 screw (0.164 in) driven into pine (softwood, ratio 0.65) is

\(D\): pilot hole \(=\) diameter (0.164 in) \(\times\) softwood ratio (0.65)

\(D = 0.164 \times 0.65 \approx 0.107\ (\mathrm{in})\)

\(0.1066 \div \tfrac{1}{64} \approx 6.82 \rightarrow 7 \times \tfrac{1}{64} = \tfrac{7}{64}\ (\mathrm{in})\)

Key idea

The basic rule for a pilot hole is "narrower than the threads, about the same as the shank (the bottom of the threads)". A screw holds because its threads bite into the wood around the hole. If the hole is as wide as the threads or wider, the threads cannot grip and the screw just spins. If the hole is too small (or there is none), driving the screw pushes the wood apart with great force, and a board end or a hard wood splits. A ratio of "60 to 80% of the screw diameter" is the common rule between these two failures: smaller for soft wood (60 to 70%), larger for hard wood (70 to 80%), and in between for plywood and MDF (about 70%). On a coarse-thread screw, the shank is thin, so in softwood a pilot hole "the same as the shank or a little smaller" (65 to 70% of the diameter) works, and in hardwood that splits easily, "a little larger than the shank" (about 75%). The material ratio covers both. Wood screws and self-tapping screws are also sized by the thread diameter, so you multiply the diameter by the ratio in the same way. For a #8 screw, this gives 7/64 in in softwood and 1/8 in in hardwood, in line with the pilot hole charts from screw makers. A thin screw in soft wood often goes in without a pilot hole, but within about 1 in of a board end, in end grain, in hardwood or in thin boards, a pilot hole is the safe choice. Drill the hole as deep as the screw is long or a little deeper, and wrap tape around the bit as a depth mark so you do not drill through the base piece. If the maker lists a recommended pilot hole (for plastic, aluminum, drywall anchors and so on), use that first.

Screw length (a multiple of the board thickness)

Figure

Standard notation (the usual math form)

\(L\) \(=\) \(k\) \(\times\) \(t_1\)

\(p\) \(=\) \(L\) \(-\) \(t_1\)

In words (symbols replaced with words)

③ \(L\): screw length \(=\) ② \(k\): multiple (2 to 2.5) \(\times\) ① \(t_1\): thickness of the board you attach

⑤ \(p\): penetration into the base piece \(=\) ④ \(L\): screw length \(-\) \(t_1\): thickness of the board you attach

The formula in words

① Take the \(t_1\): thickness of the board you attach

② multiply it by the \(k\): multiple (usually 2 to 2.5)

③ and you get the \(L\): screw length

④ Subtract the board thickness \(t_1\) from the \(L\): screw length

⑤ and what is left is the \(p\): penetration into the base piece . The threads along this length grip the base piece

Quick example

When you fasten a 1×4 (3/4 in thick) to a 2×4 using 2.5 times the board thickness, and pick the nearest standard length, 2 in (the penetration is for 2 in)

\(L\): screw length \(=\) multiple (2.5) \(\times\) board thickness (0.75 in)

\(p\): penetration \(=\) screw length (2 in) \(-\) board thickness (0.75 in)

\(L = 2.5 \times 0.75 = 1.875\ (\mathrm{in}) \rightarrow 2\ (\mathrm{in})\)

\(p = 2 - 0.75 = 1.25\ (\mathrm{in})\)

Key idea

What holds is the part of the threads that has gone into the base piece. How far the screw goes past the board you attach decides how well it holds, so the rule "the screw length is 2 to 2.5 times the thickness of the board you attach" really means "put a length equal to the board thickness, or a little more, into the base piece". For a 3/4 in board that is 1-1/2 to 1-7/8 in, and for a 1/2 in board 1 to 1-1/4 in. Then choose the nearest standard length (1-5/8 or 2 in, 1 or 1-1/4 in, and so on). This rule assumes a thin board on a thick base piece. When you fasten two 2×4s face to face (1-1/2 in each), 2.5 times gives 3.75 in, which goes right through the second 2×4. When the base piece is thin, set the penetration with the next formula (a fraction of the base piece) and always check that the screw does not go through. Common standard lengths of wood screws and deck screws are 1, 1-1/4, 1-1/2, 1-5/8, 2, 2-1/2, 3 and 3-1/2 in (they vary a little by maker). If the estimate falls between two lengths, the longer one holds better, as long as it does not go through.

Penetration from the base piece (a fraction of its thickness)

Figure

Standard notation (the usual math form)

\(p\) \(=\) \(q\) \(\times\) \(t_2\)

\(L\) \(=\) \(t_1\) \(+\) \(p\)

In words (symbols replaced with words)

③ \(p\): penetration into the base piece \(=\) ② \(q\): fraction (about 2/3) \(\times\) ① \(t_2\): thickness of the base piece

⑤ \(L\): screw length \(=\) ④ \(t_1\): thickness of the board you attach \(+\) \(p\): penetration into the base piece

The formula in words

① Take the \(t_2\): thickness of the base piece

② multiply it by the \(q\): fraction (about 2/3)

③ and you get the \(p\): penetration into the base piece

④ Take the \(t_1\): thickness of the board you attach

⑤ add that penetration, and you get the \(L\): screw length

Quick example

When you fasten a 1/2 in board to a 3/4 in base piece with a penetration of 2/3 (0.67) of the base piece (the nearest standard length is 1 in)

\(p\): penetration \(=\) fraction (0.67) \(\times\) base piece (0.75 in)

\(L\): screw length \(=\) board thickness (0.5 in) \(+\) penetration (0.5025 in)

\(p = 0.67 \times 0.75 = 0.5025\ (\mathrm{in})\)

\(L = 0.5 + 0.5025 = 1.0025\ (\mathrm{in}) \rightarrow 1\ (\mathrm{in})\)

Key idea

When the base piece is thin (two boards fastened face to face, a thin furring strip, the edge of a shelf and so on), setting the length as a multiple of the board thickness often makes the screw go through. The other method is to start from the base piece and "put the screw about 2/3 of the way into the base piece". With 2/3, one third of the base piece is left behind the tip, so the tip does not push out the back and make it bulge or split. Both methods decide how far the screw goes into the base piece, and the screw length is "the thickness of the board you attach + the penetration". If the two methods give different answers, the shorter one (the one that does not go through) is on the safe side. With either method, if you enter the thickness of the base piece, this calculator checks with the next formula whether the screw goes through.

Does it go through? (wood left in the base piece)

Figure

Standard notation (the usual math form)

\(m\) \(=\) \(t_2\) \(-\) \(p\)

In words (symbols replaced with words)

③ \(m\): wood left in the base piece \(=\) ① \(t_2\): thickness of the base piece \(-\) ② \(p\): penetration into the base piece

The formula in words

① Take the \(t_2\): thickness of the base piece

② subtract the \(p\): penetration into the base piece (the screw length \(L\) − the board thickness \(t_1\))

③ If the \(m\): wood left in the base piece is more than 0, the screw does not go through (at 0 or less, the tip comes out the back)

Quick example

When you fasten a 3/4 in board to a 2×4 (1-1/2 in thick) with a 2 in screw (penetration \(2 - 0.75 = 1.25\) in), the wood left in the base piece is

\(m\): wood left \(=\) base piece (1.5 in) \(-\) penetration (1.25 in)

\(m = 1.5 - 1.25 = 0.25\ (\mathrm{in}) > 0\)

Key idea

A screw tip that comes out the back of the base piece looks bad, can cause injuries, and splits and tears the wood on the back. Even when only a little wood is left, the tip can push out the back and make a bulge, so leave some margin. This calculator finds the wood left for the recommended standard length. If the standard length nearest the estimate goes through, it steps down to a shorter length that does not. If less than about 1/8 in (3 mm) is still left after that, it shows a warning, so also consider the next shorter length, or setting the penetration as a fraction of the base piece. If you do not know the thickness of the base piece (such as a stud behind a wall), treat it as thick enough, or look up the framing size first (a 2×4 stud is 3-1/2 in deep, and 1-1/2 in across its face).

The pilot hole is "screw diameter \(\times\) material ratio" (0.65 for softwood, 0.75 for hardwood and 0.7 for plywood/MDF are the usual rules), and you pick the nearest drill bit in your set. The screw length is "the thickness of the board you attach + the penetration into the base piece". Set it with "2 to 2.5 times the board thickness" or "about 2/3 of the base piece", choose the nearest standard length, and check that the wood left, \(m = t_2 - p\), is more than 0.

Symbols and terms

Symbols

\(d\)	dee	The screw diameter across the threads (in), from the first letter of "diameter". For a US wood screw it comes from the gauge: #8 is 0.164 in.
\(r\)	ar	The material ratio (the pilot hole as a fraction of the screw diameter), from the first letter of "ratio". 0.65 for softwood, 0.75 for hardwood and 0.7 for plywood/MDF are common rules of thumb, and you can change it in this calculator.
\(D\)	capital dee	The pilot hole size (in). It is found with \(D = d \times r\), and you pick the nearest drill bit in your set (such as 1/64 in steps).
\(t_1\)	tee one	The thickness of the board you attach (in), from the first letter of "thickness". The subscript 1 is for the first board, on the screw head side.
\(t_2\)	tee two	The thickness of the base piece (in). The subscript 2 is for the second piece, where the screw tip goes. It is used to check whether the screw goes through.
\(k\)	kay	The multiple (the screw length as a multiple of the board thickness). 2 to 2.5 is the usual rule, and this calculator uses 2.5 by default.
\(q\)	cue	The fraction (the penetration as a fraction of the base piece). This calculator uses \(q\) to keep it apart from the pilot hole ratio \(r\) (the letter has no special origin). About 2/3 (0.67) is the usual rule.
\(L\)	el	The screw length (in), from the first letter of "length". It is measured from under the head to the tip; for flat-head screws, the length usually includes the head, from the top of the head to the tip.
\(p\)	pee	The penetration into the base piece (in), from the first letter of "penetration". \(p = L - t_1\), and the threads along this length grip the base piece.
\(m\)	em	The wood left in the base piece (in), from the first letter of "margin". \(m = t_2 - p\), and if it is more than 0, the screw does not go through.
\(\approx\)	approximately equal to	The sign for "approximately equal". It is used when a value that does not come out evenly is rounded to a decimal.

Terms

pilot hole	A small hole drilled before driving a screw. It keeps the wood from splitting, helps the screw go in straight, and makes it easier to drive. It is narrower than the threads and as deep as the screw is long or a little deeper.
screw gauge	The number for the thickness of a US wood screw, such as #6, #8 or #10. The diameter across the threads is 0.060 + 0.013 × N in (#8 is 0.164 in). "#8 × 1-5/8" on a package means gauge 8, 1-5/8 in long. Metric screws are sized by the diameter in mm instead ("4 × 40" is 4 mm across and 40 mm long).
thread diameter	The diameter measured across the tips of the threads, also called the major diameter. This is the size of a wood screw or coarse-thread screw. The pilot hole must be narrower than this, or the threads cannot grip the wood.
shank diameter	The diameter of the body of the screw at the bottom of the threads, also called the root diameter. A coarse-thread screw has a thin shank, about 65 to 70% of the thread diameter (about 0.11 in for a #8). In softwood, the pilot hole is the same as the shank or a little smaller; in hardwood, a little larger.
wood screw	The traditional screw for wood. It is sized by gauge, has threads only near the tip, and has a smooth shank under the head. Brass screws and other soft screws break easily, so a pilot hole is especially important.
coarse-thread screw	A wood screw with widely spaced (coarse) threads, a thin shank and a flat head, such as a deck screw or construction screw. It is the most common screw for DIY, and with a power drill it often goes into softwood without a pilot hole. It is sized by gauge and length (#8 × 1-5/8 in, #10 × 3 in and so on).
self-tapping screw	A screw that cuts its own threads as it goes into the pilot hole, such as a sheet metal screw. It is used in thin steel, plastic and aluminum, and makers publish charts of the recommended pilot hole for each material. For materials other than wood, use those charts instead of the ratios on this page.
flat head	A screw head with a cone-shaped underside that sinks into the surface, also called a countersunk head. Most coarse-thread screws have one. In hardwood or thin boards the head may not sink in, or the board may split, so countersink the hole first.
countersink	Widening the top of the pilot hole into a cone so that a flat-head screw sits flush with the surface. The size is about the same as the head diameter, and you cut it lightly with a countersink bit or the tip of a larger drill bit.
softwood	Light, soft wood, mostly from conifers, such as pine, spruce, fir and cedar. Screws hold well and it rarely splits, so a small pilot hole (60 to 70% of the screw diameter) is enough, and thin screws often go in with no pilot hole at all.
hardwood	Dense, hard wood, mostly from broadleaf trees, such as oak, maple, cherry, walnut and teak. It takes a lot of force to drive a screw and splits easily, so use a larger pilot hole (70 to 80% of the screw diameter) and always drill one.
plywood	A panel made of thin layers of wood glued together with the grain of each layer at right angles to the next. It rarely splits between layers, but screws driven into its edge can pull the layers apart.
MDF	Medium-density fiberboard, made of wood fibers pressed together with glue. It is even and easy to work, but screws hold less well than in solid wood, and screws in its edge split it easily, so drill a pilot hole (about 70% of the screw diameter).
penetration	How far the tip end of the screw goes into the base piece. It is the screw length minus the thickness of the board you attach (also called the bite), and the threads here grip the base piece and give the holding power. With a screw 2 to 2.5 times the board thickness, the penetration is 1 to 1.5 times the board thickness.
base piece	The piece the screw tip goes into, such as a stud, a joist or another board. It is the piece that holds the board you attach. If it is thin, the screw may go through, so enter its thickness if you know it.
go through	The screw tip coming out the back of the base piece. Besides looking bad and causing injuries, it splits and tears the wood on the back. If the wood left is 0 or less, the screw goes through, and even with a little wood left the back may bulge.
holding power	How strongly a driven screw grips the wood without pulling out. It depends on the penetration, the thread area and the hardness of the wood. A screw in end grain cannot grip the fibers well and is said to hold only about half as well as one in the side of a board.
end grain	The cut end of a board, across the grain. Screws driven here only slip in between the fibers, so they hold poorly and split the wood easily. When you must screw into end grain, use a long, thin screw, drill a pilot hole, and keep away from the edges.
standard length	The lengths screws are sold in. For wood screws and deck screws, 1, 1-1/4, 1-1/2, 1-5/8, 2, 2-1/2, 3 and 3-1/2 in are common (they vary a little by maker). Choose the one nearest the estimate.
splitting	Wood cracking along the grain from the force of a screw going in. It happens easily near a board end, in end grain, in hardwood, in dry wood and with thick screws. A pilot hole, moving away from the end, or a thinner screw prevents it.
drywall	The panels on interior walls and ceilings, made of gypsum between sheets of paper. Screws hardly hold in drywall itself, so use a screw long enough to reach the framing (studs) behind it, or a drywall anchor.

Good to know before you start

Here is what helps you use the calculation on this page with real understanding, not just by pressing the button.
If you get stuck, going back over these topics is the fastest way forward.

Measuring length in inches and fractions of an inch (Grades 3–4)	Reading inches and fractions of an inch (1/2, 1/4, 1/8, 1/16, 1/64) and handling board thickness, screw length and drill bit size in inches
Multiplying decimals (Grade 5)	Multiplying a decimal by a decimal, such as 0.164 × 0.65 Multiplying by a decimal, such as 0.75 × 2.5
Multiples and fractions of an amount (Grades 5–6)	Knowing that "0.65 times the diameter" and "2.5 times the board thickness" are found as amount × multiplier Finding "2/3 of the base piece" as thickness × 2/3 (multiplying by a fraction)
Rounding down and rounding up (Grade 4)	When fitting 0.1066 in to 1/64 in steps (6/64, 7/64, 8/64 and so on), finding the step below (rounded down) and the step above (rounded up)
Subtracting and comparing (Grades 1–3)	Subtracting, such as 1.5 − 1.25 = 0.25, and telling whether the answer is more than 0 (whether the screw goes through)

How to calculate it in Excel

Copy the whole table below and paste it into cell A1 in Excel. It works as is.

Table to find the pilot hole size

Screw diameter d (in, #8)	0.164
Material ratio r (softwood 0.65, hardwood 0.75, plywood 0.7)	0.65
Pilot hole D (in)	=B1*B2

Table to find the nearest drill bits (smaller and larger)

Pilot hole D (in)	0.1066
Drill bit step (in, 1/64)	0.015625
Nearest drill bit, smaller (in)	=FLOOR(B1,B2)
Nearest drill bit, larger (in)	=CEILING(B1,B2)

Table to find the screw length (a multiple of the board thickness)

Thickness of the board you attach t1 (in)	0.75
Multiple k (2 to 2.5)	2.5
Screw length L (in)	=B1*B2
Penetration into the base piece p (in)	=B3-B1

Table to find the penetration from the base piece and check whether it goes through

Thickness of the board you attach t1 (in)	0.75
Thickness of the base piece t2 (in)	1.5
Fraction q (of the base piece)	0.67
Penetration p (in)	=B2*B3
Screw length L (in)	=B1+B4
Standard length you chose (in)	2
Wood left m (in, more than 0 = does not go through)	=B2-(B6-B1)

After pasting, the upper rows of column B are your inputs and the last rows are calculated automatically.
The first table is a #8 screw (0.164 in) in softwood (ratio 0.65), and B3 shows 0.1066. The second table rounds that 0.1066 to 1/64 in steps: B3 shows 0.09375 (3/32 in) and B4 shows 0.109375 (7/64 in). "FLOOR" rounds down to a multiple of the step and "CEILING" rounds up. The third table is a 0.75 in board × 2.5, so B3 shows 1.875 and B4 shows 1.125. The fourth table is a 0.75 in board, a 1.5 in base piece and a fraction of 0.67: B4 shows 1.005, B5 shows 1.755, and with the 2 in standard length you chose, the wood left in B7 is 0.25 (more than 0, so it does not go through).
"*" is multiplication and "-" is subtraction. For a different material or thickness, change the ratio, multiple and thickness cells.

How to calculate it in Google Sheets

Copy the whole table below and paste it into cell A1 in Google Sheets. It works as is.

Table to find the pilot hole size

Screw diameter d (in, #8)	0.164
Material ratio r (softwood 0.65, hardwood 0.75, plywood 0.7)	0.65
Pilot hole D (in)	=B1*B2

Table to find the nearest drill bits (smaller and larger)

Pilot hole D (in)	0.1066
Drill bit step (in, 1/64)	0.015625
Nearest drill bit, smaller (in)	=FLOOR(B1,B2)
Nearest drill bit, larger (in)	=CEILING(B1,B2)

Table to find the screw length (a multiple of the board thickness)

Thickness of the board you attach t1 (in)	0.75
Multiple k (2 to 2.5)	2.5
Screw length L (in)	=B1*B2
Penetration into the base piece p (in)	=B3-B1

Table to find the penetration from the base piece and check whether it goes through

Thickness of the board you attach t1 (in)	0.75
Thickness of the base piece t2 (in)	1.5
Fraction q (of the base piece)	0.67
Penetration p (in)	=B2*B3
Screw length L (in)	=B1+B4
Standard length you chose (in)	2
Wood left m (in, more than 0 = does not go through)	=B2-(B6-B1)

The same formulas as in Excel work as is (FLOOR and CEILING have the same names). Copy the whole table, paste it into cell A1, and replace the numbers in column B with your own.

How to calculate it in Python

import math
from fractions import Fraction

# ---- Pilot hole size ----
screw_dia = 0.164        # screw diameter d (in). #8 = 0.060 + 0.013 x 8
ratio = 0.65             # material ratio r (softwood 0.65, hardwood 0.75, plywood 0.7)
drill_step = 1 / 64      # step between your drill bit sizes (in)

pilot_dia = round(screw_dia * ratio, 4)
drill_lower = math.floor(pilot_dia / drill_step) * drill_step
drill_upper = math.ceil(pilot_dia / drill_step) * drill_step
print(f"Pilot hole: {pilot_dia} in -> drill bit {Fraction(drill_lower).limit_denominator(64)} in or {Fraction(drill_upper).limit_denominator(64)} in")

# ---- Screw length ----
board_thickness = 0.75   # thickness of the board you attach t1 (in)
mate_thickness = 1.5     # thickness of the base piece t2 (in)
length_ratio = 2.5       # multiple k (of the board thickness)
std_lengths = [0.5, 0.625, 0.75, 1, 1.25, 1.5, 1.625, 2, 2.5, 3, 3.5, 4]

target_length = board_thickness * length_ratio
penetration = target_length - board_thickness
print(f"Screw length: {target_length} in (penetration {penetration} in)")

# Standard lengths that reach the base piece without going through, and the one nearest the estimate
usable = [L for L in std_lengths if 0 < L - board_thickness < mate_thickness]
best = min(usable, key=lambda L: abs(L - target_length))
margin = mate_thickness - (best - board_thickness)
print(f"Recommended standard length: {best} in (wood left {margin} in -> {'does not go through' if margin > 0 else 'goes through'})")

Runs with the standard library only. math.floor() rounds down and math.ceil() rounds up, which rounds the pilot hole to a multiple of the drill bit step, and Fraction shows the result as a fraction of an inch. For the screw length, the code keeps only the standard lengths that reach the base piece without going through, and picks the one nearest the estimate. Replace the diameter, ratio and thicknesses at the top with your own numbers and run it.

How to write it in LaTeX and other math languages (copy and paste)

Pilot hole size (screw diameter × material ratio)

D = d × r

D = d \, r

<math xmlns="http://www.w3.org/1998/Math/MathML" display="block">
  <mrow>
    <mi>D</mi><mo>=</mo><mi>d</mi><mo>&#x2062;</mo><mi>r</mi>
  </mrow>
</math>

D = d r

d*r

pilot := d*r;  # In Maple, D is the differential operator (a reserved name), so the pilot hole is named pilot

D = d*r;  % d is the screw diameter, r is the material ratio (softwood 0.65, hardwood 0.75, plywood 0.7)

D = dr

Screw length (a multiple of the board thickness)

L = k × t₁,  p = L − t₁

L = k\,t_{1},\quad p = L - t_{1}

<math xmlns="http://www.w3.org/1998/Math/MathML" display="block">
  <mrow>
    <mi>L</mi><mo>=</mo><mi>k</mi><mo>&#x2062;</mo><msub><mi>t</mi><mn>1</mn></msub>
    <mo>,</mo>
    <mi>p</mi><mo>=</mo><mi>L</mi><mo>-</mo><msub><mi>t</mi><mn>1</mn></msub>
  </mrow>
</math>

L = k t_1,  p = L - t_1

{k*t1, k*t1 - t1}

L := k*t1;  p := L - t1;

L = k*t1; p = L - t1;  % t1 is the thickness of the board you attach, k is the multiple (2 to 2.5)

L = kt_1, p = L − t_1

Penetration from the base piece (a fraction of its thickness)

p = q × t₂,  L = t₁ + p

p = q\,t_{2},\quad L = t_{1} + p

<math xmlns="http://www.w3.org/1998/Math/MathML" display="block">
  <mrow>
    <mi>p</mi><mo>=</mo><mi>q</mi><mo>&#x2062;</mo><msub><mi>t</mi><mn>2</mn></msub>
    <mo>,</mo>
    <mi>L</mi><mo>=</mo><msub><mi>t</mi><mn>1</mn></msub><mo>+</mo><mi>p</mi>
  </mrow>
</math>

p = q t_2,  L = t_1 + p

{q*t2, t1 + q*t2}

p := q*t2;  L := t1 + p;

p = q*t2; L = t1 + p;  % t2 is the thickness of the base piece, q is the fraction (about 2/3)

p = qt_2, L = t_1 + p

Does it go through? (wood left in the base piece)

m = t₂ − p = t₂ − (L − t₁)

m = t_{2} - p = t_{2} - (L - t_{1})

<math xmlns="http://www.w3.org/1998/Math/MathML" display="block">
  <mrow>
    <mi>m</mi><mo>=</mo><msub><mi>t</mi><mn>2</mn></msub><mo>-</mo><mi>p</mi>
    <mo>=</mo><msub><mi>t</mi><mn>2</mn></msub><mo>-</mo>
    <mo>(</mo><mi>L</mi><mo>-</mo><msub><mi>t</mi><mn>1</mn></msub><mo>)</mo>
  </mrow>
</math>

m = t_2 - p = t_2 - (L - t_1)

t2 - (L - t1)

m := t2 - (L - t1);

m = t2 - (L - t1);  % m > 0 means it does not go through

m = t_2 − p = t_2 − (L − t_1)

How to have ChatGPT do the calculation

You are a calculation assistant for choosing screws in woodworking and DIY. Do the following calculation by actually running Python code, and base your answer only on the numbers from the execution result (do not answer by mental math or guessing).

I am driving a #8 wood screw (thread diameter 0.164 in) through a 3/4 in pine board (softwood) into a 1-1/2 in pine 2×4.
Find each of the following:
1. The pilot hole size (diameter × 0.65) and the nearest drill bits in 1/64 in steps (rounded down and rounded up), shown as fractions of an inch
2. The screw length (board thickness × 2.5) and the penetration into the base piece (length − board thickness)
3. From the standard lengths 1, 1-1/4, 1-1/2, 1-5/8, 2, 2-1/2, 3 and 3-1/2 in, the length nearest the estimate that does not go through the 1-1/2 in base piece
4. The wood left in the base piece with that length (base piece thickness − penetration)

Show the formulas you used and the numbers from the execution result.

How to Use

1

Enter your numbers

Type the numbers you want to calculate with into the input fields
2

Calculate

Press the "Calculate" button
3

Check the result

The result appears on the spot. The same page also explains the idea behind the calculation and the formula