Dolf I need your help

It all Excel mastering, in microsoft excel (or Open office Calc) you are able to put mathematic functions in the "squares".

say that you have square B1 and put the argument B1 = B3 * B4. if you input 5 to B3 and 6 to B4 then B1 will display 30. This ofcourse works for more difficult operations too like sinusodial curves and similar.

There is a number of things that went into creating that spreadsheet. One part facts (food lookups etc.), one part understanding of (baker's) math, and one part programming.

It all centers around the understanding of the use of baker's percentage in formulas and how you would express things in multi-stage formulas. I gathered my knowledge from books and the Internet. Next came the desire to express ingredients not only in weights, but also provide volume equivalents for those of us that cannot, or do now want to, use a scale.

The latter requires that you know how much volume a particular weight of ingredient takes up. The answer depends not only on the ingredient matter, but also on its form (fine powder, cubes, coarse grated, etc.). You can capture all this by assigning each ingredient a "specific gravity" value. For many substances you can find the necessary information in the USDA's food and nutrition database, although some conversion math is generally required. For some of the more commonly used items that are not in that database, you can scour the Internet (but take what you find with a grain of salt), or perform your own measurements.

To perform your own measurements, start out with a large measuring cup and fill it with 250g of water. Mark the water level. Dry your measuring cup thoroughly and fill to the marker line with the ingredient you want to measure. Note the weight. The ratio of the weight and 250g gives you the specific gravity. You have to make sure that you fill the cup in the same way you would do it when pouring ingredients into a mixer or mixing bowl. For example, if you want to "pack" flour in a cup, you must also "pack" it when you do this measurement. Repeat a few times and average for better accuracy. The combined USDA/Internet/Measurement values are in my "lookup" worksheet.

Another component that I determined to be useful for me are some common conversions. Examples are: conversion between fresh yeast, instant dry yeast, and active dry yeast, conversion between milk and milk powder + water, etc. The required ratios and formulas can be found in books or the Internet.

I often find myself encountering recipes that I'd like to use, but that do not come in baker's percentages. Yet that is how I prefer to work. This led to the creation of the "Reverse" worksheet, where I can type in amounts in any kind of units and it gets converted to baker's percentages. This is basically for one-time use because after I am done, I keep the formula around in baker's percentages.

With all that done, you encounter the problem that it is not always easy to see the overall makeup (hydration etc.) of your overall formula, so that's where I decided to create the Analysis worksheet. By adding prices per unit of weight to the lookup database, it can then also easily provide a cost analysis.

Finally, you have to take all that and design a set of worksheets that accomplish the tasks you want. Ultimately that is really up to the individual designer. I have a computer science/programming background and I tend to make designs that not only meet my needs, but (or so I hope) are easy to understand. Spreadsheets have their limits though on creating easy to use applications. It is one of the reasons why I am going to (ultimately) move all this into a Web application.

An additional handicap I encountered is that in presenting non-metric amounts it is customary to represent them using fractions, rather than decimal notation. This involves not only conversion math, but an algorithm to get to fractions that people typically use, combined with rounding. You do not want to see 31/64 pounds, but rather 1/2 so simplification and rounding is required, but 1/64 of a pound cannot be rounded to 0, and rounding to 1/32 would get you twice the amount you need. You'll need to go to 1/4 oz, which involves dynamic determination of appropriate units. For even smaller amounts you get into pinches, dashes, etc. Spreadsheets don't/won't/can't handle that for you.

Also, dealing with multi-stage formulas involves expressing amounts of ingredients in one stage relative to its used weight in a later stage. That is hard to do in a plain spreadsheet, as it requires a "hierarchical" data approach. I used Excel and it supports the Visual Basic for Applications programming language. I used that extensively to expand basic Excel functionality to handle such tasks, conversions from metric to imperial, etc.

Put all that together with sweat equity, and you have what I created. I hope this answers your question, or puts you on the path to finding what you need.

Also note that TFL now has message options, allowing you to send messages to just a particular user. That might be more appropriate in some cases.

P.S. I included links to various things on the Internet, but TFL insisted that turned my message into SPAM, and would not give me the option to enter a CAPTCHA, so I had to remove the links.
--dolf

See my My Bread Adventures in pictures