Teach Soap

Hi,
I thought I would write down step by step directions on creating your own lye calculator.
It is very convenient and quite versatile. It doesn’t involve any databases so it is easy to manage in the future. The only thing it does not tell you is the hardness and cleansing and such. One day when I find a roundtuit I will add that too (if I can find how that is figured).

What it will do:
Calculate the amount of oils need for a given mold.
Calculate’s the superfat, water amount and fragrance/per pound of oils
By using a list box you can change oils at any time and it will recalculate everything
It will calculate the parts per thousand or “ppt” which , to me, means how much soap is in a given bar.
There is a note section.
Lye will be calculated for those who do not premix lye and for those that premix a 50:50 ratio
For a 50:50 ratio, it will tell you how much EXTRA water to add.
It tells you the percentage of water and lye.
Calculates in ounces and grams.
It tells if your oils are at 100% and warns when they are not. (not done yet)

There will also be a cell devoted to calculating price of goods but I don’t know if I will get the far. The skills learned by doing the rest will enable you to create this part for yourself if I don’t get that far.
It is very easy and just plain fast and accurate (only because you can input SAP values yourself. For instance some lists list Olive at .130. The manufacturer I use list Olive at .135. Additionally, not all Palm kernel and Shea is the same value).

So, if you came this far, lets get started.
1. I use Libreoffice which is free and which my steps are coming from. But in most cases the steps should be very similar. There are other free programs to download if you don’t have Microsoft Excel. If you do have Microsoft Excel, when it concerns adding a list box it might be slightly different so you will just have to google the correct steps. I am not familiar with the “ribbon” that it uses now.

2. When downloaded, open the spreadsheet portion and click “ File->save as” and save as “Lye calculator”. These spreadsheets are created in tabular form so if you look at the bottom there should be a couple of tabs. By clicking on one you can change from sheet to sheet. It should say something like “Tab 1”, “Tab 2”.

3. Hover the mouse over a tab and right click the tab and scroll down to “Rename” to rename the tab “Recipes” or what you prefer.

4. Right click the tab again and click “Insert Sheet”. It doesn’t matter whether it is before or after. Name this sheet “SAP Value”.

5. While the “SAP Value” sheet is open the columns are lettered and the rows are numbered. In the top left corner Between “A” & “1” is an empty square. Press the square to highlight all squares. Hover the cursor over the highlighted portion and right click and click “Format cells”. Click the borders tab and under line arrangement “Default:” click the first window to the left with no lines.

6. Click the font tab and select Family “Arial”, Typeface: “Regular”, Size “18” . Click the “Numbers” tab and under format click “-1234.123” and under options leading zeroes should be “0” and decimal places should be “3” and press ok.

7. In cell A1 type “Oil”. In cell B1 type “Naoh”. Click on cell A1. Here I am solely using my mouse pad on my macbook but there is a way to click on a cell, with your mouse, and by highlighting that cell you can click and drag it over to the next cell to include both cells. Both cells should be highlighted.

5. At the top there should be a “U” that is underlined. Click that to underline.

6. Here, you can add your own oils or you can use what I have here by highlighting and pressing and holding the command or control button and then pressing “C”. They are out of order or multiples of on purpose. Additionally, on doing this I found some values are different for the same oil like Palm oil and Shea butters are different.

Almond oil
Abyssinian seed oil
Tallow
Apricot oil
Argan oil
Avocado oil
Babassu oil
Black Cumin seed oil
Black Currant oil
Borage seed oil
Camelina oil
Cottonseed oil
Coconut oil
Castor oil
Flax seed oil
Evening Primrose oil
Almond oil
Argan oil
Avocado oil
Babassu oil
Castor oil
Coconut 92º
Cocoa butter
Grapeseed oil
Lard
Macadamia oil
Monoi De Tahiti
Olive oil
Olive Pomace
Palm oil
Palm kernel
Pine Tar
Shea refined/organic
Hazelnut oil
Hemp oil
Hemp seed oil
Jojoba oil
Karanja oil
MCT carrier oil
Meadowfoam seed oil
Neem oil
Peanut oil
Peach Kernel oil
Pumpkin seed oil
Rice bran oil
Safflower oil
Sesame oil
Shea Olein
Sunflower oil
Kokum Butter
Mango Butter
Shea Ghana/Beige/unrefined

7. Click on cell A2 and press and hold command or control and press “V”. After pasting the oils, hover the cursor between column “A” and “B” at the top of the spreadsheet just above the numbered lines. Hover in between where it turns into a “+” and double click. The cells should lengthen enough to read the words.

8. Click on cell B2 only and copy&paste these values. They should paste into the corresponding cells.
.137
.119
.136
.135
.136
.133
.178
.134
.139
.134
.133
.137
.178
.128
.134
.133
.137
.136
.133
.178
.128
.178
.137
.133
.141
.139
.182
.135
.133
.144
.178
.043
.131
.136
.137
.137
.068
.142
.237
.120
.142
.136
.136
.132
.131
.135
.136
.131
.136
.135
.135
.128
9. Repeat steps 5 & 6 if necessary.
10. There are repeats and they are out of order. Click on cell A2 only. At the top there should be a A/Z sort ascending button. Click it to sort the names and values. Now you can delete the duplicates or the oils you don’t want. It is important to press the number of the row to select the entire row, right click and click “Delete row”.

9. Now for the fun stuff. Click on the “Recipes” tab and Type “Oil Amount” in A1, “SF” in B1, Water in “D1”, Fragrance pr/pd in “E1”. Highlight all those and underline with the underline button and click the button to center horizontally. Double click between “D” & “E” to fully read the letters.
10. In cells A2, B2, D2, E2 type 38, .05, 1.75, 1.1
These will be used later. Highlight those cells and center horizontally.
11. Repeat steps 5 & 6 for the recipes tab.
12. Click cell B2 and right click to format cell. Click the numbers tab and click “Percent” in the category section. The Decimal places should be 0 and leading zeroes should be 0. Click ok.

13. In cell B3 type “Bar #1” or the name of your recipe.

14. In cells A4, B4, C4, D4, E4 type “Oils”, “%”, “sv”, “.oz”, “g”.

15. Click on cell A4 and hold down the control/command button and click those same cells one at a time to select all of them and click the “U” to underline.
16. In cells A12, A13, A14 type “Water”, “Lye”, “Fragrance”.

17. In cell A17 must type and not paste “Water +/- & Cost of oils”
This row is used for people who use a 50:50 premix solution but is adding extra water to make the batch. There is an extra cell that calculates the cost.

18. Select cells A17, B17, C17 and click the merge and center button with the yellow stripe at the top.

19. In cell A18 type “Notes:”

20. Highlight the range A3:E21 to apply a border. At the top on the right side is a square with an arrow. Click the arrow and the appropriate square to outline the entire portion. This outlines the entire recipe so the same procedure can be copied and pasted to F3 in the future.

21. Select cell A3 and highlight the range A3:B16 and click the borders button and outline the rectangle.

22. Click cell C3 and highlight the range C3:C16 and give that the same border. Do the same with D3:D17, and E3:16.

23. Highlight cells A17:E17 and give that a border. Do the same with A16:E16 and B15:E15.

24. Click cell B16 and enter “1”. Press enter and click on the cell again to right click and format cell. Under category click NUMBER. Under Options Decimal places should be “0” and Leading zeroes should be “0”. The format code should say EXACTLY #”%” . Press ok. The cell should now read 100%.

25. Highlight cells B5:B11 and center horizontally with the button under the binoculars. Enter the number 5 in range B5:B11.

26. Click on cell B16 and type “=Sum(B5:B16)” and press enter. The number should now say 35%.

27. In cell D5 under “oz” Type in “=$A$2*(B5/100)”. Press enter. The answer should be “1.9”. Click on cell D5 again. Notice there is a small black square on the lower right side. That is called a fill handle. Hover over the fill handle till the cursor turns into a “+”. Click and drag the fill handle all the way to cell D11 and release. All the cells should now say 1.9. When cells are clicked and dragged those formulas adjust to the cell. The $ sing ensures that cell A2 never changes in the formula but everything else does. Now as you change the % the oz. will change. Remember, all cells with formulas should not be changed. They will adjust.

28. Click on cell E5 and in the formula bar at the top type
“ =D5*28.349523125 ” without the quotation marks. That is oz converted to grams and is only inaccurate to a very small number. Click and drag the fill handle to cell E11.

29. Type in cell E15 “ =Sum(E5:E14) ” and press enter. 430.91

30. Lets do it differently - Type in cell D15 “ =Sum ( ” Click and drag cell D5:D14 and press enter. 15.2

31. Now for the fun stuff. If you have Microsoft excel google “ Excel How to create a drop down list”. It might be similar dunno?
It also might be familiar in other programs also. If you want to skip this step then pick 7 oils from the “SAP value” sheet and type in each one in the range A5:A11 and their Sap values in the sv column.
Click cell A5 and click “Data —> Validity” at the top. Under the Criteria tab in the Allow field click “Cell range”. In the “Source” field there is a text box and a little window to the right of the text box. Click the window. The form will minimize. When it does click on the “SAP value” sheet and highlight the range A2:A47. Click the window again and press ok. Click on another cell and re-click cell A5 and notice it now has a list box with your oils.

32. Now with A5 having a drop down menu, click on A5 again and press the paint brush at the top. Click cell A6. Now A6 is a list box. Click and highlight both A5 and A6. Now click cell A7. Now A7 & A8 are list boxes. Repeat to cell A11.

33. Click cell C5 and type “ =VLOOKUP(A5,’SAP Value’.$A$2:B47,2,0) ”. Press enter and click and drag the fill handle to cell C11. Now all oils should be showing their saponification values except for one thing…

34. Press command or control button and click the C, D, E columns. Click the “Center horizontally button” . Highlight cells C5:C11 right click to format cells and click the numbers tab. Under the format field press “-1234.123” and press ok, You can also format the oz and grams column by doing this same method to the way you prefer.

35. Almost finished - Under the fonts list box at the top. Find the “Name” list box. Click on cell B2 and in the “Name box” (Which currently says B2) type Superfat and press enter.

36. Copy and paste this in cell D13 without quotations “ =(1-Superfat)*((C5*D5)+(C6*D6)+(C7*D7)+(C8*D8)+(C9*D9)+(C10*D10)+(C11*D11)) ” If 5% is still in all oil values the answer should be 1.96. Change the percentages correctly to equal 100 to see the correct value. Keep in mind 0 can also be used if not using that oil or oils.

38. Click cell E11 and drag the fill handle to E14. The lye value is now in grams.

39. Click cell D13 and enter “ =D13*$D2 ”. Now the water should be in oz and grams. 3.42 oz and 97.07 grams.

40. In cell B12 type “ =E12/($E$12+$E$13) ” For the water ratio.

Edited: In D12 type " =$D$2*D13 " for the amount of water to be used.

41. In cell B13 type “ =E13/($E$12+$E$13) ” for the lye ratio
42. In cell D14 type “ =($A$2/16)*$E$2 ”
43. In cell E14 type “ =D14*28.349523125 ” to convert to grams.
IF PPT IS NOT WORKING THE TOTAL AMOUNT IN GRAMS SHOULD BE IN CELL E16. MINE WAS IN THE WRONG PLACE.
44. Click cell B15 and type “ =(E13/(E15+D17))*1000 ” Don’t worry. Not working yet.
45. Click cell D17 and Type “ =E12-E13 ”
46. Click E17 and type “ Cost? ” for right now.
47. Click cell D15 and press and hold control/command & x
48. click cell D16 and press and hold control/command & v
49 click cell D15 and type “ =D13*2 ” and press enter. This is for 50:50 premix lye/water users to look at. That is how much 50/50 lye/water to add.

This should have it working. If any letters are jumbled you can hover over the column letters to turn the cursor into a “+” and double click to correct everything. This is not through and I will be back for other goodies and much simpler directions on what goes into what cell.



Here is my version. It is pretty accurate so I just made up a recipe and compared it to soap calc and took screenshots of both as a comparison. I have no idea how to use the image link. I can't seem to get it to work.
If you know how to get the image link to work please let me know otherwise my name is out there for identity thieves for helping people make their own calculator.
The numbers in red parentheses are for those who premix 50/50 lye water. The number in blue is the extra amount of water that is to be added.
Once completed, the entire thing can be copied and pasted in the adjacent cell for another recipe. I tried to design the formulas to be able to be copied and pasted and still work. If it does not you can message me and I can tell you how to fix it.

Wow that was a lot of research you did and thanks for sharing.