D5/(1+D6)D70.115(1+D9)-(D5/(1+D6)D7*0.115)

Or: How I nearly broke my brain building a preferment calculator

May 08, 2024

As I mentioned in my previous post, I set out to create a set of spreadsheets for preferments of any kind—sourdough or yeasted, white flour or whole grain—at any hydration, and—in the case of sourdoughs—using a starter of any hydration, that gives precise numbers for the preferment and the final dough. It’s useful generally, but what it is most useful for is when you have a known preferment-containing formula and want to alter the preferment—the time it takes to proof, its hydration, or the hydration of the starter you use—and end up in a more-or-less identical place.

I sorted out how to do this, but it nearly broke my brain in the process. I worked on it for days and days (and days), thought I had it worked out many times, only to find something was off and having to start over again. (I even had to rebuild it since I started writing this post.) I think it is structurally sound, but I’ve been wrong before, so if you try it out, keep that in mind. I know it’s close enough that any errors should be easy to fix.

I’ve worked with Excel for years, but I’d never spent much time creating formulas—the underlying calculations that take the numbers you input and spit out the numbers you want—from scratch. I’d looked at formula spreadsheets created by others, but it’s rarely easy to discern how the strings of numbers and letters in the formulas did their work. It was a royal pain, but in the end it was easier to create my own from the ground up, and I taught myself to use Excel in the process (at least a little).

The basics

Here’s how they work:

You start by choosing the appropriate spreadsheet out of four flavors: two sourdough and two yeasted ones, in either white or whole-wheat variations, since each flour ferments at a different rate.
You then input the following numbers (in the blue boxes): the final dough weight, the hydration of the final dough, the amount of prefermented flour, the hydration of the levain or preferment you are building, and the hydration of the starter you are building it with.
The sheet then spits out four separate amounts of flour, water, and starter (or yeast), each of which yields a preferment that will ferment within a specific timeframe: 3, 6, 12, and 18 hours. (I never use the last one, but it might be useful for someone. I could add others as well, or instead create a drop-down menu that lets you pick a time frame, yielding only the desired formula, though I don’t know how to make that work yet.)
Below the preferment formulas, the sheet tells you the weight of the preferment, along with exactly how much flour and water remain in the dough, so you can adjust the final formula appropriately. (It assumes you can sort out or know the amounts of everything else already, which you would if you were starting with a known recipe.)
At the bottom of the chart are the calculated weights of the preferment and the final dough, which are mainly for reference, to be sure that nothing is wonky in the formulas.

The nuts and bolts

Feel free to skip this part if it is boring, but if you are interested in how I created them, here you go (writing it all down was a useful exercise in making sure it all actually made logical sense):

The entered numbers for the weight of the final dough and its hydration determine the total amount of flour in the dough (including what is included in the preferment).
This element was actually one of the last things I unlocked, and the hardest to sort out, until I did. The answer actually came to me half-asleep in bed one morning, after agonizing over it for hours the day before. I needed a direct way to calculate the amount of flour in a specific weight of dough of a known hydration. The formula for this is:

dough weight ÷ (1 + dough hydration, expressed as a decimal)

For example, say you have 1000g dough that is 75% hydration:

1 + 0.75 = 1.75
1000 ÷ 1.75 = 571g flour
This works because the flour in a formula is always expressed as 100%, or 1; adding the hydration to 1 results in a ratio that accounts for both the flour and water together. This is actually an extremely useful formula, and it gets used many times in the sheets, since it can also determine how much flour and water are in a preferment as well. After having sorted it out, it seems dead obvious, but until then it was anything but.
One thing I learned in this process is that if you want to build complex formulas in Excel, the best way to do it (the only way I can do it, with my math-resistant brain) is to first build simpler intermediate ones that you combine later on. I’ve kept these intermediate calculations on the sheet for my own reference, though they are hidden down at the bottom (scroll down to find them). Here’s what they look like:

To know how much flour is going into the preferment (i.e., the “prefermented flour in levain”), I multiply the percentage of prefermented flour in the dough by the total flour in the formula. In the example above, there is 900g dough at 75% hydration and 15% prefermented flour:

900 ÷ 1.75% = 514.3g

514.3 x 0.15 = 77.1g

The amount of water in the preferment is just the prefermented flour x the hydration of the preferment. Since the levain in this case is 100% hydration, it is also 77.1g.
The prefermented flour in the seed (i.e. the starter you use to build it) is the one element that has a variable in it, and it is a number that determines how quickly the preferment will proof. Each column has a different variable, depending upon the length of the fermentation. I’m using numbers here that are based upon those I’ve used before and on those from other baker’s formulas; these numbers will likely get revised as I work with the sheets longer, but they are a solid starting point.
The seed weight is the prefermented flour in the seed x (1 + the seed hydration); multiplying a flour amount by (1 + hydration) yields the total weight of a dough.
The water in the seed is the seed weight, minus the weight of flour in the seed.

The flour amount in the levain is the prefermented flour in the levain, minus the flour in the seed.
The water amount in the levain is the prefermented flour in the levain x the levain hydration, minus the water in the seed.
Relative to the formula spreadsheets I normally use, this is the bit for me that these sheets fix—if you use a starter that is at a hydration other than 100% (as most formulas assume), it adjusts the amount of everything—the weight of the seed and the flour and water you add to it to get a levain of a precise hydration.
Here’s the same exact formula, but with a starter at 50% hydration:

Notice that the seed weight went down (since there is less water in it), and the amount of water went up to keep the hydration of the levain constant? It’s a simple shuffle, but one that required a lot of complicated calculations.

The next elements (the yellow boxes) are numbers for the final dough, which are essential to know when you are adjusting the makeup of the levain and/or the amount of it you use, since that will change the amount of flour and water going into it.
The final box is just the dough weight, as calculated from the weights of the levain, flour, and water; it’s not necessary to have it here, but it serves as a check on the calculations, since if it doesn’t equal the number I input into the first box, something is off. I may remove it from the sheets in the long run, but for now it’s reassuring that everything lines up.
The only other thing to mention about the sourdough versions is the difference between the white and whole-wheat flour versions: the amount of prefermented flour I use in the whole wheat one is reduced by 10%, to account for the increased fermentation rate for whole grain flours. I need to do more testing around this, so that difference may change in the future. If you use them and find the levains proof faster than suggested, let me know.

And that’s it! (lol). As I mentioned elsewhere, I have no idea if this is useful to anyone but me, but it is useful to me, and I learned a lot creating it.

The yeasted versions

Compared to the sourdough versions, the yeasted versions were dead simple to create, since there is no flour and water in the “seed”. They work identically to the sourdough versions, but give different weights of instant dry yeast, depending upon the desired fermentation rate of the poolish or biga (they don’t adjust the weights of flour and water from one to the next, since the difference is negligible). These numbers (again) are based upon my experience and formulas from others; I may revise them over time. (Also, the 6-hour column works two ways: as a 6-hour room temperature ferment, or one that you proof for 4 hours and then move to the fridge for up to 14 hours, making it convenient for an overnight process.)

The yeasted sheets do contain one more fancy trick, however:

See that last line, which is a set of numbers 100 times greater than the instant yeast amounts above it? They represent a simple way to get around a problem that arises when making a yeasted preferment on a small (i.e., home-baker) scale: the teensy, tiny amounts of yeast that such preferments call for, which are well below the accuracy of even a good gram scale.

Meet serial dilution

This is something I learned back in my days as an organic chemist: If you need to scale out something that is below the accuracy of your measuring device, you add a precise amount of it to a larger amount of some neutral carrier, and then measure that instead. This is what is known as serial dilution—you dilute the thing you need in something else, and measure the mixture instead on a larger scale.

Here’s how to use serial dilution with instant yeast: place 49.5g flour and 0.5g (1/8 teaspoon if you don’t have an accurate gram scale) yeast—or any amounts, in a 99:1 ratio by weight—in a jar, seal it up, shake well, and store in the fridge. Whenever you need a teensy amount of yeast, use 100 times as much as the recipe calls for (or the 100x weights given by my preferment sheet). You’ll add some extra flour to the formula in the process, but the difference will be negligible.

Incidentally, you can do this with sourdough too, and it is even easier. If your recipe calls for a tiny amount, thin a larger amount of it out in water, and then measure that. For example: Say you need 0.5g of starter to build a levain. Combine 5g of starter with 45g water (a 10x dilution, or 9:1), stir well, and weigh out 5g of that.

Phew, that was a lot!

I’ve shared all the intel behind the sheets (enough that someone might even be able to build their own), but as I mentioned in the previous post, the sheets themselves are for paid subscribers’ eyes only. They will be sent out in yet another email, since I’d like to keep comments on this post open to everyone here.

—Andrew

Wordloaf

Discussion about this post