Ejuice calculator (added quantity calculations)

[VA Slim]

Dan....great interview. Thank you for your voice on our behalf. Yes, you made us proud to be fellow vaperers.

 

[yourgems510]

Are bickford's flavorings the same as loran's? Is there a problem when you mix the oil base flavors? I also noticed that bickford has some flavors that are soy oil base. Can anyone please tell me if bickford is good to use?

 

[Scottes]

Dan,

Thanks for doing all the work on this calculator! It's extremely useful when my wife and I are experimenting with our juices.


Both of us have found that we tend to invent mixes, and we do this by making up a very small batch with 2 or 3 different flavorings. We will usually make a wild-... guess about how many drops of each flavor to use, and we test that. Invariably we will find that the balance is off - perhaps one flavor is not prominent enough, or another is too strong. So we will add a drop or two of a weaker flavor.

In the end, after a lot of tweaking, we will have our "flavor recipe" which will have different amounts of drops for each flavor. This "flavor recipe" might be something like 7 drops of #1, 3 drops of #2, and 1 drop of #3. We then go to your E-Jucie Calculator to make a larger batch.


This is where the pain comes in, since your calculator currently requires me to enter the percentages for each flavor. But I have the counts for the drops for each flavor. If recipe calls for something easy - if it contains a total of 10 drops of flavoring for instance - then it's easy to calculate the percentages. But if the recipe contains 4 flavors totaling 17 drops then it's a mild form of torture to figure out the percentages.


So I edited your spreadsheet, and added a section that will calculate the flavoring by ratios.

One simply enters the number of drops used for each flavor in the small test batch. The formulas figure out the Total number of drops, and for each flavor it figures out the percentage for that flavor, and multiplies that by the total number of drops or milliliters required for the batch. In psuedo-English, the formula is simple: (Drops / TotalDrops) * BatchDrops.


In the example below, my recipe calls for 3 flavorings with a total of 7 drops in a 4:2:1 ratio. The batch calls for 2.5ml, or 50 drops, of flavorings in order to make my desired 10ml of final product. This new section calculates how many drops of each flavor is required, based on the ratio after all my tweaking.

I think this sort of calculation might be convenient for others to use, so I was hoping that you could add it to your Excel spreadsheet. I made these edits in Microsoft Works, but you should be able to open it in Excel and cut & paste it into your spreadsheet. The file can be found here:
http://www.itsanadventure.com/postimages/E-Juicie_Calculator_Ratio.xlr


Again, thanks for all the work on this. I hope you find my edits to be useful.

 

[Dale.s]

Ive made a few changes to your wonderfull sheet and i wanted to share! Section 5 is now a total juice recipie solution! Goto sheet called "recipies" and input the data for the juices you have made in this format:

Flavor name | Total Flavor % in final mix | Flavor 1 | % of flavor | Flavor 2...

for example:

Jolly Rancher | 30% | Watermelon (Loranns) | 100%

This would give you a juice name of "Jolly rancher" using 30% of final mix of Loranns watermelon flavoring.

Now goto section 5, at the top of this section is a dropdown, pick your juice. This section now fills in with total flavor % to the right (this is the same % you would enter in section 1). Each flavor name, % of that flavor, and ML required of flavor per mix you picked in section 1. - Note, % Flavor in section 1 is not auto filled so you can do this by hand when working on new mixes.
View attachment ejuce_calculatorv6.5.xls

 

[Scottes]

Dale, I was working on something very similar but ran into a couple problems because I don't know Excel all that well.

My plan had been to add a button that would copy all the fields onto the "Recipes" sheet, recording the quantities for different size batches. So I was going to output something like this:

This way I'd (probably) never have to type into the Calculator sheet again. I was also thinking about adding another cell so it could be pasted here using those [ code ] tags that keep formatting pretty.


What do you think about my output?

 

[Dale.s]

Thats awesome, keep working on it for sure!

 

[Scottes]

Thats awesome, keep working on it for sure!

I will - though I might have to steal some of your code. I know VB very well, but not the Excel-specific VB commands, so it's sometimes frustrating to figure out to do things like "go to the first available blank row". I would normally type "ctrl-home, end, down-arrow, down-arrow" to do this by hand, but what the heck is the macro command to do the equivalent? I even recorded a macro, and typed "end down-arrow down-arrow" but when I use those in a macro it always take me to A65536, not the first available blank row!

I'll keep hacking away though.



Let me pass on another idea I had, and see if people think it's useful:

Most of my bottles have built in droppers. For the others, I use pipettes. This means that every drop is a different size.

For experimenting with very small batches this is easy since I'll just drop each ingredient until I get to 2 or 3 ml. But when I want to make 30ml I want to use milliliters - I'm not going to count out 600 hundred drops! But the ratios are wrong since the drop sizes differ.

Dan conveniently made a "drops per ml" configuration item, but this assumes that every drop is exactly the same size.

Would it be useful to have a "drops per ml" for each and every ingredient? This way one can configure 17 drops per ml for pipettes, 25 drops per ml for LorAnn's tiny droppers, etc.

If nobody wants to use this one could set them all to "20" but it does give greater accuracy for those of us who care.



By the way, Dan, I hope that you don't think that I am trying to steal any thunder with such ideas. Heck NO! I'm just hoping to add some features, and to do it in a way that lets you copy & paste code into your spreadsheet. I'm just trying to help.

 

[vapn]

THANKS DAN>>> I have been looking for something like this to help me with this 54mg TW Platinum Ice I got today. I wanted to bump up some 12mg juice I have but was unsure how to do the ratio... This spreadsheet is on my desktop where it will be used many times.. Until I know it in my head anyway...
Thanks for sharing this with us...
Until I got this I found this on the forum also
e-juice calculator....

Thanks again Dan...

 

[Dale.s]

Scottes, I was looking at it the same way as you, using macros. But take a look at the function i used in section 5 (i forgot what it was, im sleepy!) and lookup the help file on that, there a few variations on the same function that can look for certain items, match sections (like you could like every lorann flavor you use for example) and find the row number of blank lines. Its SO easy to think "MACRO!" but i found its a lot easier to use what excel already has to offer, if you cant find the right function lemmie know what your trying to do and ill go a hunting for you!

 

[Scubabatdan]

Dan,

Thanks for doing all the work on this calculator! It's extremely useful when my wife and I are experimenting with our juices.


Both of us have found that we tend to invent mixes, and we do this by making up a very small batch with 2 or 3 different flavorings. We will usually make a wild-... guess about how many drops of each flavor to use, and we test that. Invariably we will find that the balance is off - perhaps one flavor is not prominent enough, or another is too strong. So we will add a drop or two of a weaker flavor.

In the end, after a lot of tweaking, we will have our "flavor recipe" which will have different amounts of drops for each flavor. This "flavor recipe" might be something like 7 drops of #1, 3 drops of #2, and 1 drop of #3. We then go to your E-Jucie Calculator to make a larger batch.


This is where the pain comes in, since your calculator currently requires me to enter the percentages for each flavor. But I have the counts for the drops for each flavor. If recipe calls for something easy - if it contains a total of 10 drops of flavoring for instance - then it's easy to calculate the percentages. But if the recipe contains 4 flavors totaling 17 drops then it's a mild form of torture to figure out the percentages.


So I edited your spreadsheet, and added a section that will calculate the flavoring by ratios.

One simply enters the number of drops used for each flavor in the small test batch. The formulas figure out the Total number of drops, and for each flavor it figures out the percentage for that flavor, and multiplies that by the total number of drops or milliliters required for the batch. In psuedo-English, the formula is simple: (Drops / TotalDrops) * BatchDrops.


In the example below, my recipe calls for 3 flavorings with a total of 7 drops in a 4:2:1 ratio. The batch calls for 2.5ml, or 50 drops, of flavorings in order to make my desired 10ml of final product. This new section calculates how many drops of each flavor is required, based on the ratio after all my tweaking.

I think this sort of calculation might be convenient for others to use, so I was hoping that you could add it to your Excel spreadsheet. I made these edits in Microsoft Works, but you should be able to open it in Excel and cut & paste it into your spreadsheet. The file can be found here:
http://www.itsanadventure.com/postimages/E-Juicie_Calculator_Ratio.xlr


Again, thanks for all the work on this. I hope you find my edits to be useful.

Love it will add it in, I like the drops method also I think we can satisfy both sides with percent and a ratio section. Great work!

I have been busy with my BS degree and havent been on much lately. I will post V7 with your additions.

I do not care it anyone want to upload their version of this calculator, that is why I left it un passworded so people can tailor it to their needs. I am just glad it is helping folks!

Dan

 

[Scottes]

Cool, thanks Dan. I'm glad you're open to this community-based open-source programming.


I'm going to work on the recipes stuff I mentioned in post 85. I don't know how long it will take - or even if I will finish it since my experience with Excel is low. But it's a worthy goal so I'll give it a shot, and I have some time coming up.


And hopefully I'm not biting off more than I can chew, but here's some other ideas I had, and will attempt:

1) The spreadsheet has a tab with ALL the flavors from 3 companies. I think it would be convenient to make them accessible using drop-down lists, rather than typing them in every time.

2) I'm getting very frustrated with all my droppers being different sizes, and I do not want to convert to identical droppers. The bottles that come with droppers are very convenient. So I think I'll add a "drops per ml" for each ingredient line.

3) I've also been working on the http://www.e-cigarette-forum.com/forum/diy-e-liquid/12259-loranns-strength-o-meter.html, and started adding Capella's to that thread. I'd like to add that info in, too, and keep all this info centrally located in a single location, this spreadsheet.

4) I'd like to automatically add the "drops per ml" based on the flavor chosen, configured in the Flavors worksheet. That is, have the Flavors spreadsheet contain a column for "drops per ml".

5) To be determined, but it would be nice to have all the flavoring info appear in the drop-down list when you pick a flavor. So my idea for the drop-down flavor list would show something like (as an example only):
LorAnn's Apple (Very Weak) (OK to use) (22 drops per ml)
LorAnn's Apricot (Strong) (Warning: Contains Artificial Coloring) (22 drops per ml)
Capella's Toasted Almond (Insanely Strong) (DANGER: Oil based) (17 drops per ml)


All of this should also provide some consistency in how recipes are shared, which could make for the creation of a great database of this community's recipes.

 

[Scubabatdan]

Ive made a few changes to your wonderfull sheet and i wanted to share! Section 5 is now a total juice recipie solution! Goto sheet called "recipies" and input the data for the juices you have made in this format:

Flavor name | Total Flavor % in final mix | Flavor 1 | % of flavor | Flavor 2...

for example:

Jolly Rancher | 30% | Watermelon (Loranns) | 100%

This would give you a juice name of "Jolly rancher" using 30% of final mix of Loranns watermelon flavoring.

Now goto section 5, at the top of this section is a dropdown, pick your juice. This section now fills in with total flavor % to the right (this is the same % you would enter in section 1). Each flavor name, % of that flavor, and ML required of flavor per mix you picked in section 1. - Note, % Flavor in section 1 is not auto filled so you can do this by hand when working on new mixes.
View attachment 4471

Excellent, I also love this addition, I think I will add yours to the bottom section 7 and move scotties up to section 5 and move the VG/PG calculation section to the bottom. That way we can figure the recipe by ratio and it can gives us % that we can record on the recipe sheet and call up any time with your section.

Great Idea!
Dan

 

[Scubabatdan]

I will - though I might have to steal some of your code. I know VB very well, but not the Excel-specific VB commands, so it's sometimes frustrating to figure out to do things like "go to the first available blank row". I would normally type "ctrl-home, end, down-arrow, down-arrow" to do this by hand, but what the heck is the macro command to do the equivalent? I even recorded a macro, and typed "end down-arrow down-arrow" but when I use those in a macro it always take me to A65536, not the first available blank row!

I'll keep hacking away though.



Let me pass on another idea I had, and see if people think it's useful:

Most of my bottles have built in droppers. For the others, I use pipettes. This means that every drop is a different size.

For experimenting with very small batches this is easy since I'll just drop each ingredient until I get to 2 or 3 ml. But when I want to make 30ml I want to use milliliters - I'm not going to count out 600 hundred drops! But the ratios are wrong since the drop sizes differ.

Dan conveniently made a "drops per ml" configuration item, but this assumes that every drop is exactly the same size.

Would it be useful to have a "drops per ml" for each and every ingredient? This way one can configure 17 drops per ml for pipettes, 25 drops per ml for LorAnn's tiny droppers, etc.

If nobody wants to use this one could set them all to "20" but it does give greater accuracy for those of us who care.



By the way, Dan, I hope that you don't think that I am trying to steal any thunder with such ideas. Heck NO! I'm just hoping to add some features, and to do it in a way that lets you copy & paste code into your spreadsheet. I'm just trying to help.

The drops per ml is there for people to calculate small recipies, if you use the same dropper/pipet etc then the drops per ml does not change. I use the droppers from loranns that fit into the dram bottles for figuring out my small recipies <10ml for big batches I go by ml of flavoring and not by drops. The drops per ml is there to help in getting the recipie the same everytime. But if you use different droppers then you will have to calculate each flavor based on drops. That is why I went to the Lorann droppers. I also use Cappella so I do understand what you are talking about when you mean different drop sizes. It is more convinient to use their dropper bottles than to open them and us a pipet. I suppose I could play around with the drops per ml per flavor, but then if someone uses a different dropper thean you then the point is mute. I found that the Lorann droppers are at 40/ml so it is perfect for what I need.
Hmmm let me think about it.
Dan

 

[Scottes]

I suppose I could play around with the drops per ml per flavor, but then if someone uses a different dropper thean you then the point is mute.

My thoughts had been about having this option for every ingredient. My nic-juice bottle is different from my PG bottle, which are different from the pipettes used for LorAnn, which is different from the Capella dropper bottles. Some of these differences might be slight - though your comment about 40/ml for LorAnn is wildly different from the generalized 20 or 25 drops per ml that most people quote around here.

The issue I find is dropping to make a test 2ml batch, and then converting that recipe to measured milliliters for making a 30ml batch.

Imagine a case where one has the calculator set to 20 drops per ml. Let's say they make a 2ml test batch with 5 drops of Bickford using a 20/ml pipette, and 5 drops of LorAnn using a 40/ml dropper.

Assuming that both are measured at 20 drops per ml, the chef would think that there's 1/4 ml of each flavor in that 2 ml batch. In reality, there's 1/4 ml of Bickford and 1/8 ml LorAnn.

Converting such a 2ml dropped recipe to a 30ml measured batch would produce a wildly incorrect flavor. The calculator would produce an equal number of ml for each flavoring, when there should be twice as much Bickford as LorAnn.


I had also planned on it being optional. It certainly doesn't have to be changed every time one makes a recipe.

Hmmm let me think about it.

I think you should continue to work on your BS, which is far more important than this. I'll play with it if I get time, or maybe I won't and the idea can be re-visited in the future.

Maybe a bunch of folks will come along and say it's a crazy idea. Or maybe a bunch will say that it's the most important idea ever conceived. Either one may give us incentive to do it, or ignore it.

 

[Scubabatdan]

Ok I have added both Dale, and Scottes suggestions!
I moved things around a bit, Section 5 is now Scottes addition and section 6 is Dales addition. Section 7 is now the VG/PG calculation section.
Now you can enter the ratios for figuring your recipes in section 5 and it will calculate the %, and when the recipe is the way to like it, you can record it on the recipe sheet in % from section 5. These can now be pulled up at any time in section 6 to be duplicated.

Here it is V7 of the ejuice calculator

Let me know what ya think!
Thanks alot Dale and Scottes!
Dan

 

[Scubabatdan]

BTW I have put my own calculator to good use and made 5 different batches totaling 130 10ml bottles and am doing quite nicely as a side business selling unique flavors the to the local retailers here. There is definantly money to be made in DIY eliquids, consistancy is the key. I agree with Scottes that the differences in drops can play a big difference in flavor so be aware of that. Always test your recipies in small quantiys when using someone elses recipes and adjust based on the drops you use. Talior it to your own flavor and by all means adjust the calculator if you need to add on

Thanks to everyone who has contributed, lets continue to make this better.
Dan

PS I can BS quite well har har har

 

[Scubabatdan]

though your comment about 40/ml for LorAnn is wildly different from the generalized 20 or 25 drops per ml that most people quote around here.

Well I took a 3ml syringe and counted as I squeezed drops in .5ml was 20 drops using the lorann droppers and flavoring. I will test the capella flavors and report back.

Dan

 

[Dale.s]

Awesome work dan! just one note about my addition, the "Total flavor %" was the amount of flavor per batch like in section one, one recipie might use 20%, another 30% for example, not the total of all flavors added. Might need to find a way to make that clearer. great work btw!

 

[Scottes]

Awesome Dan!

 

[Scubabatdan]

Awesome work dan! just one note about my addition, the "Total flavor %" was the amount of flavor per batch like in section one, one recipie might use 20%, another 30% for example, not the total of all flavors added. Might need to find a way to make that clearer. great work btw!

I understand that, that is why I changed it
You see I was looking at the total flavoring combined should equal 100% that way we can bounce the 100% against the flavoring from section one no matter what % you put in. That way it is divided out accurately no matter how much you add in proportionally.

Notice section 5, when it equals the ml in the amount in section 1 the % in section 5 also total to 100%. Then we can move those % to the recipe page and have it forever. Ratios or percentages wil work no matter the volume.

Make sense?

Dan