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.

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.

*** 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