On Taking Initiative to Automate Success

This post will not appeal to everyone– I am sure about that.

I am also confident that I must not be the only one who has had a very difficult time finding many tools or resources to learn lotion recipe formulation.

Trial and error are good learning tools, but for those of use who are spoiled by having wonderful lye calculators for predicting soap outcomes, we are left lamenting the lack of similar tools for predicting lotion outcomes.

My goal is to make an excel calculator for formulating lotion and cream recipes that will work on my Mac (the only one I could find doesn’t work on a Mac, even with Office for Mac installed).

I put on my programmer hat and got to work in Excel. I have a Version 0.00000001 (so it is so draft it is not even funny at this point), but it looks promising.

I took initiative and started really digging into learning how the HLB (Hydrophilic-lipophilic Balance) values work and how they can help you determine how much emulsifier is needed in the recipe depending on the variable factors such as the specific oils and other ingredients used.

If this is something that peaks your interest — read on!

While doing my research, I also learned that some other ingredients you choose (such as silicone or botanical ingredients) can affect different amounts of other ingredients (such preservatives or emulsifiers) that you have to use — as one example.

HLB Calc

Consider this example PDF >>HLB Calc Example 1  (this should be a good recipe, according to the calculator, but I haven’t tested it yet.  If you do, and it turns out, please let me know.  I will try it this weekend and post an update.)

The example in the PDF file shows a simple excel spreadsheet.

On row 1, you can input the recipe name and number of ounces or grams you want to achieve.

Column A lets you choose the ingredients you want to use in each phase (water, oils, cool-down).  It pulls from a drop-down list that links to other worksheets which have stored values such as ingredient names, which phase they belong in, and HLB values.

Column B is where you enter the desired percentage of each chosen ingredient. When you enter in the ingredient name and percentage, some Excel magic happens in the back ground as the formulas calculate the required amount of emulsifiers, based on your choices of oils and butters and specific emulsifiers and thickeners you chose.

Column C shows you the calculated number of ounces for each chosen ingredient (so this is your recipe).

Column D is where you can enter in some comments, such as how many drops of essential oils you used, or which preservative you used, for example.

Column E shows you a quick “error” indicator.  It checks if there are any blank ingredient cells, or cells that have a “not used” ingredient that has a percentage specified for that line as that would throw off your recipe.

On the right hand side, you have some charts which help to balance the recipe.  It shows you:

  • Top: Total percentage values for each lotion phase including a tally
    • This should equal 100% when you are done entering and balancing your ingredient percentage amounts
  • Middle: HLB Balancer and Viscosity Predictor
    • It calculates the HLB values of the oils, butters and other oil soluble ingredients, and the HLB capability of the emulsifiers and thickeners you chose.
    • You use this to make sure that the HLB requirement of the oils is equalled (or close to equal) the HLB capability of the chosen emulsifiers.
    • If it does not balance, you just go back and adjust percentages of the oils and emulsifiers until it does balance.
    • It tells you, based on the total percentage of oils in the recipe, what the predicted viscosity will be — anything from a gel to a thick hand/foot cream, and everything in-between.
  • Bottom: Cautions –
    • It does some error checking and reminds you of any errors it finds, such as:
      • blank ingredient cells
      • percentages entered for “not used” ingredients
      • It reminds you if you chose a silicone, that you should use a silicone capable emulsifier and add the silicone in the cool-down phase.
      • It reminds you if you use botanicals such as Aloe Vera Liquid, that you should consider using the higher end of your preservative recommendation (as those are harder to preserve).
      • It tells you if the recipe totals more than 100%
      • It tells you if there are any errors due to blank cells or numbers entered on lines that have no ingredients.

Above this section I posted a PDF file showing a “good recipe” — one with no errors, no special cautions.

As a contrasting illustration, here is an example of a recipe that shows multiple errors and cautions >>HLB Calc Example 2

I protected the sheet so that you can only enter in values in the cells that “should” be changed such as the ingredient names, percentages to use, and comments.  This should help avoid changing any cells which contain formulas and breaking the tool.

So far it seems pretty nifty!   I haven’t proven it out yet, as I am just getting it to the point that I feel it is ready to use.   I am hoping to be able to test it this weekend with a couple of lotion batches and see how they turn out.

I have entered in some real lotion recipes from some different formulators (such as Nature’s Garden, Brambleberry, SwiftCraftyMonkey, and others), just to make sure that the HLBs seem to be working correctly based on how well the recipes calculate out — and so far–so good!

There are a few things I know I need to work on.  For example:

  • Change column A to force a value in the cells so that they can’t be blank
  • When “not used” is chosen in column A, zero out any values in column B (reset the percentage to 0).  (Then I can get rid of column E altogether).
  • Implement a “print” button that hides the “not used” rows so your recipe prints just the chosen ingredients and amounts needed.
  • Enhance the control tables to add more oils, butters, emulsifiers, and other things you can choose for the water and cool-down phases.
  • Explore how the % of the water phase can be calculated into the lotion viscosity predictor (right now it just tallies the oils, but doesn’t consider the other liquids).
  • Add some more cautions to the cautions list such as “recipe total under 100%” or “HLB values are not balanced”.
  • It would be awesome if the proper amount of the chosen emulsifiers and thickeners would “auto-fill” based on the chosen oils in the recipe (so you don’t have to manually balance them).
  • I am sure I will think of plenty more.

Two caveats:

  1.  For some oils, I cannot seem to find reliable HLB values.  For those oils, I am just assuming an HLB of 7 (for now) or if a thicker oil, maybe a little higher (8).  This can be a bad assumption, however, because something like Argan oil is a thinner oil but has a higher HLB requirement.   It is probably close enough in most cases, but there are the odd exceptions.
  2. For blended emulsifiers such as BTMS-50, or Emulsifying Wax NF or Polawax — since these are proprietary blends — there doesn’t seem to be information about the quantities of the mixture ingredients.  For example, BTMS-50 is a blend of Behentrimonium Methosulfate, Cetyl Alcohol and Butylene Glycol.  But how much of each one?  I can find the HLB for each one separately and assuming they are all used in equal parts, extrapolate…but… I don’t know that they were used in equal parts.  So I am going with the assumption that the high HLB components are a little higher percentage and the low HLBs are a lower percentage.   But I have no idea if this is right.

I am excited about this little “side project”.  If this works the way I am intending it to, this could be a really great tool, similar to using a lye calculator to make soap.

I, of course, have much testing to do in order to make sure that the formulas I wrote are viable, but please do offer feedback.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s