The Fresh Loaf

A Community of Amateur Bakers and Artisan Bread Enthusiasts.

Conversion Calculator Example

RonRay's picture
RonRay

Conversion Calculator Example


The following information and resulting calculator is considered original work by me. I hereby declare it in the public domain and free to copy and pass on to anyone you should chose. I really do not wish to become the suppler of software programs, free or otherwise. However, because it is intended as an aid to the members of TFL, I will temporarily try to provide a free copy of both versions to anyone who e-mails a request to me in the following way. Go to this URL
http://www.RonRay.us
and send me an e-mail from there with "TFL-Per Cup" in the subject line - just to be sure you do not get lost in the Spam filter. I will send you a copy of both versions in Excel format.

I really do not enjoy Excel, perhaps that is because I use the Open Office Spreadsheet, which is free. However, I will attempt to show how anyone can make there own Excel program for ingredient measure conversions.

Below is an example with four example ingredients entered (as examples only), along with the one thing you need to find out - grams per cup [g/cup ] weight of that item. This you can obtain from many places online, or directly from the label on the ingredient.

Conversion Calculator Example

Once you make a few setup entries, the only thing you need do after that is copy the 7 active cells on the first information line [line 2] and paste that line down as many lines as you wish for additional entries - and, of course, save your new calculator.

For me, at least, this would be simpler in Open Office's Spreadsheet. so please bare with me on these steps.

First, you will need to have a copy of the Excel spreadsheet, or the Open Office Spreadsheet installed on your PC and then we can start.

1/ Open Excel, and click on [File], located at the top left of the Excel screen. Then on the [Save As...] option. Navigate to any location where you want to store this file, and then fill in a name for what will become your ingredient calculator. Finally, save the file.

2/ Now format the 7 columns that we will be using - A through G.

    Column A/ Click the [A] at the top of the 1st column - the entire column should become highlighted. Next find the [Format] button on the top line of Excel, and click it. In the drop down menu, pick [Column] then [Width...] and enter 25, then OK. You can always return and set it for a different width. Now, name that column. Click in the 1st cell of the A Column - that is location cell A1 - type "Ingredient".

    Columns B through G/ Click the [B] at the top of the 2nd column, and holding down on the left mouse button, drag the mouse across to the right until you reach the G (top of the 7th column) and then release the mouse button. All the 6 columns from B to G should be highlighted. Now, as before, go [Format]=>[Column]=>[Width]=>Column width: [10 ] and type in the "10" followed by OK.
Go to cell B1 and type in "g/cup". Go to cell C1, and type "Cup", then D1 enter "Tbs". Type "tsp" in E1, "grams" in F1, and finally "Oz." in G1.

3/ Click on [File], located at the top left of the Excel screen, and then on the [Save] option. I know you would not wish to loose the work you have already finished. To finish the formatting, press [Ctrl]-A, that means hold down the control key [Ctrl], and then press the [A] key as well, then release both. The entire screen was selected and all cells are highlighted (except the cell you were in when you did the Control-A action). Now click the bold "B" in the text format area of the toolbars to set All text to BOLD. Then just to the right of that press the centered group of lines indicating the Centered Justification. All of the text you have typed, should be centered within the cell they occupy. Click in cell A1 and color the background to match the example at the head of the posting. To do so, click the small down-pointing triangle to the right of the tilting paint can - extreme right bottom of the lowest toolbar. Then color the other 6 cells that have text typed in them to also match the example given. 


4/ Click on [File], located at the top left of the Excel screen, and then on the [Save] option. Most of the work is done. You have to equations to copy and insert. Then we can check to see if you got everything functioning before copying additional lines for future data - should you wish them.

In the F2 cell - the cell breath [grams] - you will need to place a copy of the expression given on the next line, but before that you must prepare Excel to accept it for the equation it is, rather than as just a text entry. First, click in the F2 cell, and then on the blank line just above the A, B, C, D, E, F, G, etc. you will see an equals sign at the left end "=". Click that [=] and you should see an " = " appear at the extreme left of the blank white line. That indicates that Excel expects a statement telling it how to figure the content to show in cell F2. Here is what you must type, or better yet, copy from the next line and paste in following the "=" sign:

           ((C2 * 48) + (D2 * 3) + (E2 )) * (B2/48)

Nothing special here, but so there is no mystery, here is what ((C2 * 48) + (D2 * 3) + (E2 )) * (B2/48) says:
1/ Take the number in cell C2 and multiply that by 48. That determines the number of teaspoons resulting from the "Cup" entry.
2/ Take the number in cell D2 and multiply that by 3. That determines the number of teaspoons resulting from the "Tbs" entry.
3/ Take the number in cell E2 and add that to the other two quantities. That determines the total number of teaspoons resulting from the 3 data entries.
4/ Take the number in cell B2 and divide it by 48. That determines the grams per teaspoon for this ingredient.
5/ Multiply the g/tsp by the total tsp indicated. That gives the answer in grams for the entries in the Cup, Tbs, and tsp columns.

When you clicked the "=", an "X" and a check mark came on to the left of the "=". If you successfully placed the expression into Excel line, you now must tell Excel that you are either "Done", or "Forget it". Clicking the red X is saying "Forget it", clicking the check mark is saying "Done". So, if you are done, click the check mark.  SAVE YOUR WORK.

Let's see if it is working. There should be a zero "0" in cell F2. Enter a "120" in cell B2 and the zero should stay unchanged. Enter a "1" in cell E2 - meaning 1 teaspoon - hit [Enter] and you should have a "2.5" in the grams column (cell F2). If so, that is saying that for an ingredient the weighs 120 grams per cup, 1 tsp would equal 2.5g. Leave the 1 in E2 and add a tablespoon - place a 1 in cell D2 and press [Enter]. The result should be a total of 10g. add 2 Cups (enter 2 in cell C2) and [Enter]. You should have 250 grams.One more equation and the hard work is finished. Let us add the conversion for ounces. Click in cell G2 to make it active. Then the "=" at the left of the blank toolbar area - to say "equation coming" to Excel. Then copy and paste, or type in the next line:

( F2 / 28.35 )

Then press the check mark. You should see " 8.818342152 " in the Oz. column's cell G2. Well, I doubt you need that much precision. So, let us change it. If cell G2 is still active, change to select the whole column G. Click the G and the column should become highlighted. Go [Format]=>[Cells]=>[Number] and type in 2 in the "Decimal places" [ 2 ], then click OK. While we are at it, click the "F" to select the F column and do the same thing, except enter "Decimal places" [ 1 ], , rather than 2, followed by OK. And, we might as well finish by highlighting columns C, D, and E, and go [Format]=>[Cells]=>[Fraction]=>[Up to one digit (1/4)] followed by OK.  In cell C2, replace the "2" with a "1.33333", followed by Enter.  Change D2 from 1 to 2.5 then make E2 read 25.8. The answers should be 243.2g and 8.58 oz. There is no rules on what values you can use in the Cup, Tbs, or tsp columns. The program will indulge your whims.  Save your work, we are almost done.

Click in cell B2, then hit the [Backspace] key to clear the cell of the "120" that was entered there. Repeat this fro C2, D2, and E2. You should have only a " 0.0" in F2 and a " 0.00 " in G2. Now with the "slate clean", we will copy line #2 and repeat it for more data entry rows. But first, so you will know what lines are functional, Type " Blank " in cell A2. Next, place you mouse cursor in cell A2, hold the left mouse button down and drag the mouse cursor to the right until cell G2. You should have cell A2 through G2 ALL highlighted. Now, copy them by pressing [Ctrl]-[C]. When you do that, you should see a dashed boundary around the area copied. Control-C copies all information that is highlighted. Now, click on cell A3 and holding down on the left mouse button, drag down column A to cell A8 and let go. That group A3 through A8 should be highlighted. Now, press [Ctrl]=[V]. Control-V pastes the copied info into the highlight cells, and since the copied information covered several cells in a row, all the row data is copies as well.

SAVE YOU WORK!!!

You are done, until you want to start using you Ingredient Conversion Calculator. But just to wrap it up a bit, notice that there are 4 rows of example in the image at the start of this posting.

Expanded Calculator Example

*** A Second Vesion using Ounces per Cup:

For those who would like to also have a second Calculator for Oz/cup do this:
1/ Follow the instructions above for the g/cup calculator.
2/ Once completed and saved, open that calculator and go to [File] and [Save as...] then save the file with a new name - like Oz/cup Calculator. and click OK
3/ Using the new copy, change cell B1 to read " Oz/Cup "; cell F1 to read " Oz. "; and cell G1 to read " grams ".
4/ Highlight all rows EXCEPT row 1 and row 2. Then delete those rows.
5/ Make cell G2 active, and edit the lower toolbar's line from:
=F2 / 28.35
to read:
=F2 * 28.35
Then click the check mark to the left of the "=" sign.
6/ Check the work as follows:
a/ Enter " 4.23 " in cell B2 and press [Enter]. Columns F and G should show only zeros.
b/ Enter " 1 " in cell C2 and hit Enter. F2 should read " 4.2 " (Oz.) and G2 should have " 119.92 ". Clear cell C2.
c/ Enter " 16 " in cell D2 and hit Enter. F2 should read " 4.2 " (Oz.) and G2 should have " 119.92 ". Clear cell D2.
d/ Enter " 48 " in cell E2 and hit Enter. F2 should read " 4.2 " (Oz.) and G2 should have " 119.92 ". Clear cell E2.
7/ Select the total F column by clicking the "F" above cell F1. Go [Format]=>[Cells]=>[Number] and type in 2 in the "Decimal places" [ 2 ], then click OK.
8/ Select the total F column by clicking the "G" above cell F1. Go [Format]=>[Cells]=>[Number] and type in 1 in the "Decimal places" [ 1 ], then click OK.
9/ Remove any data you have in cells A2 through E2 - leave F2 and G2 unchanged.
10/ Type " Blank " in cell A2. Next, place you mouse cursor in cell A2, hold the left mouse button down and drag the mouse cursor to the right until cell G2. You should have cell A2 through G2 ALL highlighted. Now, copy them by pressing [Ctrl]-[C]. When you do that, you should see a dashed boundary around the area copied. Control-C copies all information that is highlighted. Now, click on cell A3 and holding down on the left mouse button, drag down column A to cell A8 and let go. That group A3 through A8 should be highlighted. Now, press [Ctrl]=[V]. Control-V pastes the copied info into the highlight cells, and since the copied information covered several cells in a row, all the row data is copies as well.
11/ Save your Oz/Cup Calculator......

There is nothing in this example of how I store/create/analysis recipes that you could not build yourself just from the start the above info provides. I do hope this will help some of you who have problems with conversions.

Just as sure as some loaves come out in unexpected ways, you will damage some copy of these programs, if you use them. So, stay safe and save a Read Only copy, then, simply open that copy and save acopy as a your new work-named copy whenever you want to work with it.  Then, you will not be ruining your master copy, but rather "just" a work copy.

I have had some additional thoughts, which you can see at:

http://www.thefreshloaf.com/node/19777/calculating-baker

Ron

 

 

wayne on FLUKE's picture
wayne on FLUKE

Hi Ron,

I too use OpenOffice. I made something similar, but not so pretty. I like how you dealt with the different possible measuring units, but it does require the user to convert to grams/cup which is easy enough for some of us, but I was thinking of having the spreadsheet caculate it. The user could select the units from a "selection list" (I think that is the correct term) and then the spread sheet could handle it.

Since some recipes give flour in oz and liquid in fluid oz, those could be options too.

If you want to send me your file I could try some of these ideas and see what you think. Send me a private message and I'll provide my email address if you don't have some place to post the file online.

I think this link will send me a message:

http://www.thefreshloaf.com/messages/new/19256?destination=user%2F19256

let me know -- wayne

RonRay's picture
RonRay

Wayne, I did reply, as per your request, via e-mail.

"but it does require the user to convert to grams/cup"  Yes, but the problem really is that it requires going Volume to Weight.

With the exception of water, one can never be certain of how much a cup of an ingredient will weigh. So, the weight of the ingredient must be supplied by the user - there is, after all, a very great number of potential ingredients. :-) Given that a weight must be provided, and that a gram is 28.35 times finer in resolution than an ounce, I will vote for grams, and if one wishes to convert grams to ounces the divide by 28.35 would be easy enough to do to the calculator creation as given.

However, if enough people wanted the oz/cup, rather than the g/cup, I suppose I, or anyone else, could provide the alternative steps of instruction right here as a posting.

wayne on FLUKE's picture
wayne on FLUKE

I understand about the problem with volume measurement in general.

When I said "but it does require the user to convert to grams/cup" I was thinking about the fact that some people (that don't like math) would find the number for grams/teaspoon (like for salt) and not want to have to convert teaspoons to cups. That's all.

Unfortunately when most people (amatures and professionals) publish a recipe in volume, they give you no idea how much flour they packed into a cup, so converting (or making) the recipe is a matter of guesswork especially since often times no attempt is made to decribe the kneaded dough.

wayne

 

 

RonRay's picture
RonRay

Of course, life is an adventure ;-) Here is my solution to the flour packers... When I get a flour that I've not used before, I measure a 1/4 cup, packed as hard as I can. and level with a straight knife blade, on its side angle. I weight that and write it down. I then repeat that a second time. I then measure 1/4 cup sprinkling the flour from a spoon, level with knife as before. Weigh, log and repeat. I log the data in terms of g/cup and the average becomes my (personal) g/cup for that type of flour.  I do not worry about the seasonal moisture considerations. While moisture is a concern, for me it is minimal within my home. Were I living on a boat it would outweigh most other concerns LOL.

Thus, all of my ingredients are in my personal database by ingredient, calories/tsp, grams/cup, and indicated as to it being Gluten-Free, or not - I bake for a couple of friends that cannot eat anything with wheat gluten in it (No bread for them - just cookies).

Ron

RonRay's picture
RonRay

Double posting deleted

Chuck's picture
Chuck

Ask three different conversion websites "how many grams in a cup of flour". Most likely you'll get three slightly different answers. The other especially notable problem is "how many grams in a teaspoon of salt" - table salt or sea salt or kosher salt? (That's why volume measures are preferred.)

And as to complicated fractional math (5280 feet in a mile, how many in 1/4 mile? 36 inches in a yard, how many in 1/3 yard? 4 Tablespoons in 1/4 cup, how many in 3/4 cup?), IMHO the only reasonable solution is to throw it all out and instead use some sort of metric (i.e. times 10) system. If everything is in grams, all I have to do is move the decimal point left or right; no funky fractional math whatsoever.

(Conversion from "cups" is such a PITB that the few times I'm forced to do it I write the answers right on the recipe so I'll never have to do it again. Mostly, if I get a recipe book that doesn't give ingredient weights [neither in addition to the old volume measures nor by themselves], I just throw it on the back of the shelf and get a different recipe book -- to me it's just plain not worth the hassle:-)

RonRay's picture
RonRay

There is a difference, I only have to write it once, and it's there for all furture needs, as well as, that 1st time I needed it. But, as long as we each get it done, in our own perfered method, I guess that is as good as it will get. ;-)

Ron

GregS's picture
GregS

Ron,

Thanks for the hard work on this excellent tool. Now, lazy old coot that I am, I wanted to know if there was a way to download this little gem without keying in all the data. I'm quite conversant with Excel, and I'm sure I could handle it. Do you have a location where you could post the file, or would you be willing to send it to my email address?

Thanks from this "couch loaf" (kinda like a couch potato).

GregS

RonRay's picture
RonRay

Greg, I really do not wish to become the suppler of software programs, free or otherwise. However, because it is only for those here on TFL, I will try it and see how it goes from there.
Go to this URL and send me an e-mail from there with "TFL-Per Cup" in the subject line - just to be sure you do not get lost in the Spam filter.

I will send you a copy of both versions in Excel format.

http://www.RonRay.us

steelchef's picture
steelchef

As I understand it, humidity plays a large part in using either weight or dry measure. Also as noted by others, liquid remains a constant.  It seems to me that "feel" or appearance is the only reliable means of determining when a loaf is ready for the oven.

I don't mean to diminish and totally respect the effort that RonRay has put into his spreadsheet.

Here's a link to a KAF conversion site that I've used with some sucess for a while now. I've also encountered other conversion programs on the web and having challenged some of their calculations, never heard back or got some excuse.

http://www.kingarthurflour.com/recipes2008/master-weight-chart.html

Following Floyd's advice in his primers on basic bread is the best thing I ever did in this endeavour.  Thanks Floyd for sharing your knowledge with us.

Colin

wizarddrummer's picture
wizarddrummer

Nice work.

I know full well how much effort (love; and possibly frustration) went into that.

I've been designing computer apps for 3 decades.

I found this useful tool at the Pizza Making Forum. And, as a matter of fact it was a reference in that forum that led me to this forum months ago.

http://www.pizzamaking.com/expanded_calculator.html

I use this to calculate pizza dough, but it also works for any kind of bread recipes as well.

It's very simple and it gives you weights in oz, grams and liquid and dry measure.

If you check the next link there's also a preferment calculator and some other tools.

http://www.pizzamaking.com/dough_tools.html

 

And no I didn't write these apps, I wish I had, because they are well written. I have been contimplating taking the dough calculator and doing it in a database so you can do testing, scenarios and save favorte batches.

 

RonRay's picture
RonRay

Thanks for the interesting links, they are very good references to have.

I have another application that I just posted yesterday http://www.thefreshloaf.com/node/19777/calculating-baker but I need to review it and put together some basic instructional aids to handle full bread formula/recipe files. It is a water-downed version of what I use myself. I am sure you would agree the marriage of PC & baking can make great bread (and pizza).

Ron