"Product Mixer" (spreadsheet) to find NPK/PPM of mixed products

After I switched from GH Flora 3-part to Grow More Sea Grow, I wondered the actual NPK ratios (and strength) had been at the various "mixes" of GH Flora, and whether that could be duplicated with my Grow More nutrients. I created a spreadsheet (<<link) and thought this might be useful to others.

For example, if someone uses one of the more boutique nutrient lines, this spreadsheet could help them see the details of what they feed and try other products mixed to similar proportions.

Here's an example from the README:

You use GH Flora Series (3-part) and Koolbloom (liquid and powder “bloom booster”). You'd like to try a new product. You wonder how “different” it is, or how to achieve similar NPK ratios .

In the UNMIX worksheet enter the quantities (ml volume of liquid product, or grams weight of dry) you used for a feeding. See the yellow cells:


Those are half-strength of GH's recommended “1-2-3” ratio and Koolbloom powder at recommended strength. The results in the green cells are “Parts” (ratio) of product weights.

Enter those parts into the MIX worksheet's “Parts” row and you will see the NPK of those mixed products:

Next, scroll down to the “Strength” row and enter the total grams (from the UNMIX worksheet) and you'll see the PPM produced by those combined products at that strength. Notice that it tells you how much of each product to use (in milliliters or grams if dry). These quantities should be exactly the same as what you entered in the MIX worksheet.


Now you can play with the “Parts” of other nutrients to approximate that NPK. In this example I'll use Grow More Sea Grow All Purpose (veg), Flower & Bloom, Hawaiian Bud (booster) and Gen. Organic's CaMg+. The following “Parts” produce an NPK pretty similar:


Next adjust the “Strength” to get the desired PPM:

The resulting amounts to use are in a reasonable range. For the dry products, that's about 1/3 tsp, 2/3 tsp and 1/8 tsp respectively. CaMg+ is 4-3/4 tsp. These are within the manufacturer's suggested quantities.

Note: You'll need to weigh a tablespoon (or cup, 16 Tbsp) of your dry nutrients to know how results in grams relate to a volume in teaspoons.

You can adjust the “Volume: Quantity to make” and the spreadsheet will tell you how much of each component to use to make that NPK ratio, PPM strength for a quantity of water.

I put the spreadsheet on my blog page (<<link) so I can update it. I'm wondering if others see any serious flaws in what I'm doing. I created it in OpenOffice. It should work in Excel, but I don't have it. If it doesn't work, download OpenOffice here. The spreadsheet uses ordinary formulas. (No macros.).

For me it's been kind of fun. For example, seeing how Botanicare's CalMag+ adds N to my flowering ratio. I'm trying General Organic's CaMg+ which has no N (thanks to Mick941 for suggesting that.).

We often hear a flower ratio of 1-3-2 is the best. Or, that Advanced Nutrients maintains a 2-1 PK ratio. It's been fun to me to see how I can mix 2-3 Grow More products to achieve similar ratios.

Thoughts?

Are your calculations correct, or is this just going to make people skeptical of hydrobuddy's correct calculations (which are indeed spot on)?

Also, you should stop looking for a source of calcium without nitrogen. You need to understand why other hydro products containing calcium DO have nitrogen. I wouldn't trust that mick guy in general. If you're going to get general organics CaMg+ because it lacks nitrogen, you may as well get a big bag of limestone (not soluble) from home depot. They don't put N there by accident, the nitrate in calcium nitrate is what makes it soluble and also what makes it suitable for hydroponics..

churchhaze said:

Are your calculations correct, or is this just going to make people skeptical of hydrobuddy's correct calculations (which are indeed spot on)?

Click to expand...

I believe the calculations are correct. I've never been good at math, but it seems basic. I hope other eyes will validate it. (I should add comments to various sections explaining what the cell-formulas are doing, to make it more transparent for everyone.).

It wasn't my intention to compete with, or imply HydroBuddy is incorrect. I tried to do this in HB but couldn't get it to work to my satisfaction. If I were going to mix raw salts the way you do, HB is clearly superior. But, mixing 4-5 commercial mixtures to approximate a mix of 6-7 other commercial mixtures, I couldn't see how to get HB to do that.

I should add a link to HydroBuddy. Acknowledge that the spreadsheet is in the same spirit and, if someone gets down to less "boutique" nutrient "line ups," they should look at HB as a "next step." That it definitely should not be used to model raw salts like HB. A "baby step" toward the principles and visibility of HB.

churchhaze said:

you may as well get a big bag of limestone (not soluble) from home depot

Click to expand...

I thought Ca released from calcium carbonate (CaCo3, the ingredient of lime) is as available as Ca can be?

I do have a footnote in the Readme.pdf making the point that GH is intended for hydro and Grow More Sea Grow has no Ca, expecting it to be delivered in the soil. I wouldn't use that much "CaMg+," it's just to illustrate how similar ratios can be achieved in the spreadsheet.

Thanks for your feedback.

Sorry for bashing the calculator without trying it, it's just that I see a lot of people coming up with ppm calculations that are sort of fuzzy, which isn't bad, but it definitely made it harder for me to accept that there are actually correct ways to calculate the ppm (mg/L) of each element in a nutrient solution.

Insoluble forms of calcium like calcium carbonate (lime) or calcium phosphate (bone meal) are great for building soils and potting mixes.

It should be mutually exclusive whether you use calcium nitrate or an insoluble form. Either mix lime/bonemeal in with your soil, or use calcium nitrate in your limeless media.

My point is that it's a huge rip off to get calcium carbonate from a bottle.. considering the solubility is so low, there must be very little in there. I remember being able to get 50 pound bags of lime at home depot for like 10 bucks. It's a shame they don't have 50 pound bags of yara calcium nitrate too.

churchhaze said:

I see a lot of people coming up with ppm calculations that are sort of fuzzy, which isn't bad, but it definitely made it harder for me to accept that there are actually correct ways to calculate the ppm (mg/L) of each element in a nutrient solution.

Click to expand...

I put off PPM until the end because I thought it was something extremely complex. I kept thinking I needed to take into account the molecular weight of each element (Mg is heavier than N). But, fortunately for me it worked out ok (I think). For other readers, 1 gram of [something] in 1000 liters (1,000,000 milliliters) is 1 part per million. If I were dealing with 1 tsp of N, then I'd need to know the molecular weight of N so its PPMs would be accurate and comparable to the PPMs of a teaspoon of Mg.

Since the spreadsheet 1) deals in the weight of dry product, or adjusts the weight of a milliliter of pure water (which is 1 gram) by the heavier (or lighter, such as silica) ingredients, and 2) uses a label's guaranteed analysis which is a percentage of the product's weight, I accidentally arrived at micrograms of a nutrient in a liter.

I'm still unclear how "guaranteed analysis" labels work. For example, when a product says "K 5% (as K2O)", does the product have 5% (of its weight) of K2O (and therefore less than 5% K? ) Or, does it have more than 5% K2O to achieve 5% K ?

It probably doesn't matter since I'm just creating a new label by combining labels.

Also, I've read European countries allow percentages of nutrients to be expressed as volume/volume (ml/liter) unlike the US where it's required to be weight/weight. The latter is more directly an expression of PPM (as described above). The former would need to calculate the molecular weight of a volume of N. I need to understand this more. Hopefully Europeans are accustomed to converting their v/v labels into w/w. Or, if there's a way I could do it in the spreadsheet I would.

I've mentioned a few times I used GH Flora 3-part following the "useless" schedule.[1] I had great yields, few problems. (A little tedious mixing something different each week.). I've wondered what the NPK details were. Below is what I got from the spreadsheet:

--------------- R A T I O S
-------- N-P-K ----------- K-Mg-Ca -------- PPM
VEG
week-1 1.32-1.00-2.41 - 10.46-1.00-3.31 --- 316
week-2 1.85-1.00-3.06 - 15.15-1.00-5.30 --- 560
week-3 2.06-1.00-3.31 - 17.36-1.00-6.24 --- 651 [2]
BLOOM
week-1 1.00-1.29-1.88 -- 5.32-1.00-2.07 --- 737
week-2 1.00-1.47-1.70 -- 4.02-1.00-2.04 --- 763
week-3 1.00-1.52-1.70 -- 3.87-1.00-2.00 --- 871
week-4 1.00-1.72-1.87 -- 3.72-1.00-1.75 --- 942
week-5 1.00-2.21-2.25 -- 3.46-1.00-1.36 --- 963 [3]
week-6 1.00-2.70-2.36 -- 2.91-1.00-1.23 --- 966
week-7 1.00-3.16-2.73 -- 2.88-1.00-1.05 --- 930

That's the results using the unmodified schedule. However, I added GH liquid Koolbloom (LKB) during flower, and powdered Koolbloom (PKB) the final week (prior to 10-20 days of water only). The following is how it turned out:

week-2 1.00-1.92-2.16 -- 5.09-1.00-2.04 --- 884 [4]
week-3 1.00-2.12-2.31 -- 5.25-1.00-2.00 - 1,051 [5]
week-4 1.00-2.53-2.68 -- 5.34-1.00-1.75 - 1,182 [6]
week-5 1.00-4.09-3.34 -- 5.26-1.00-1.26 --- 737 [7]

I extended those periods because I flower longer. Week 5 was more like Week 9. (I used 60% Pro-Mix HP, 20% perlite and 20% Kellogg Patio Plus Premium Outdoor Potting Mix.).

Anyway, I thought that might be an interesting data point. An example of why I wanted to create the spreadsheet. When I grew with GH Flora it was all a mystery to me. 50 ml of the green stuff, 120 ml of the purple stuff... It would have been nice to know what I was creating.

[1] Useless schedule: http://forum.growkind.com/showthread.php?t=35174#.UmIsPhDK7Kc
[2] Repeat until bloom.
[3] Cut dose in half if using bloom booster.
[4] 2ml/gal LKB
[5] 3ml/gal LKB
[6] 4ml/gal LKB
[7] Cut base nutes in half, as described in the Useless schedule. Use 1.25g/gal PKB.

When I get a chance, I'll have to download openoffice and a take a look.

Just to confirm, do your calculations take into consideration the density of the commercial stock solutions? When you add 5mL of GH flora micro, do you first convert to mass using the product's density?

I still haven't looked at the calculations in the spreadsheet itself, but just so everyone knows, the NPK rating on bottles of liquid nutes is by mass, so if you record volume, you will have to convert to mass using the product's density.

I also assume you are showing K2O conversion factor? This is always the most confusing. (whether P means P2O5 and K means K2O or not)

churchhaze said:

Just to confirm, do your calculations take into consideration the density of the commercial stock solutions? When you add 5mL of GH flora micro, do you first convert to mass using the product's density?

Click to expand...

Yes. I didn't show a screen shot of a "1 to 20" worksheet where commercial products are defined. But, there is a cell called "Liquid density." It has a comment explaining its purpose.

churchhaze said:

I also assume you are showing K2O conversion factor? This is always the most confusing. (whether P means P2O5 and K means K2O or not)

Click to expand...

I don't get down to that level. Should I? I figured I'm just creating a new NPK label from existing labels which already account for how much K is contributed by K2O. (I don't even know what the label means in this regard. When it says 10% K as K2O, does that mean the product contains enough K2O to yield 10% K? Or, that it's 10% K2O and actual K is less?).

My goal is to raise awareness for the average bottle user. Reduce the abstraction while still dealing in abstracts. If a user wanted to be more detailed, their next step would be HydroBuddy.

I want to be accurate though. If differentiating between forms of P and K is necessary I'll do it. But, I like the simplicity (for the intended audience). I definitely could note something in the README about this.

Let me know your thoughts.

FYI: I updated the spreadsheet to calculate "Liquid Density (g/ml)" (in the 1-20 worksheets) in any unit of weight and volume commonly published on product labels. Prior to this you had to do your own math.

FYI: I added a "Summary" section to consolidate significant information into one place, for easy screen-capture or copy/paste into a grow journal:



I'm calling this the final version and updated the blog page.

churchhaze said:

I also assume you are showing K2O conversion factor? This is always the most confusing. (whether P means P2O5 and K means K2O or not)

Click to expand...

This is confusing. I found a hydro topic where Mel Frank is said to recommend NKP 250-70-150 ppm. GH Flora 3-part is then mixed at a certain ratio to produce that. But, when I input those amounts of 3-part into my spreadsheet, my P & K ppms are different.

That's a difference in language I hadn't thought about. If a hydro person says they prefer NPK 3.57 - 1.00 - 1.14 (which the above is), are they representing a ratio of PPMs of actual elements, or the label's abstracted N - P2O5 - K2O (which contains less actual P and K). Putting the same amounts of 3-part into my spreadsheet produces NPK 1.53 - 1 - 1.36.

If I heard someone say they prefer 3.6 - 1 - 1.1, I'd think they're talking about labels (the way most people think of NPK?). If they're talking about ppms, they really mean 1.5 - 1 - 1.4.

NPK rating is kind of dumb... and it brings a lot of confusion with it. NPK ratings are really N - P2O5 - K2O.

That makes it really hard communicating ratios with other growers. I prefer to use K:Ca vs K2O:Ca, as an example.

http://www.agroservicesinternational.com/Education/Fert6.html

http://en.wikipedia.org/wiki/NPK_rating

"
Method for converting an N-P-K value to an actual composition
The factors for converting from P2O5 and K2O values to their respective P and K elemental values are as follows:

• P2O5 consists of 56.4% oxygen and 43.6% elemental phosphorus. The percentage (mass fraction) of elemental phosphorus is 43.6% so elemental P = 0.436 x P2O5
• K2O consists of 17% oxygen and 83% elemental potassium. The percentage (mass fraction) of elemental potassium is 83% so elemental K = 0.83 x K2O
• Nitrogen values represent actual nitrogen content so these numbers do not need to be converted.

Then from the guy who made hydrobuddy,

http://scienceinhydroponics.com/2010/08/the-npk-mistery-what-do-these-numbers-mean-and-how-are-they-calculated.html

az2000 said:

This is confusing. I found a hydro topic where Mel Frank is said to recommend NKP 250-70-150 ppm. GH Flora 3-part is then mixed at a certain ratio to produce that. But, when I input those amounts of 3-part into my spreadsheet, my P & K ppms are different.

That's a difference in language I hadn't thought about. If a hydro person says they prefer NPK 3.57 - 1.00 - 1.14 (which the above is), are they representing a ratio of PPMs of actual elements, or the label's abstracted N - P2O5 - K2O (which contains less actual P and K). Putting the same amounts of 3-part into my spreadsheet produces NPK 1.53 - 1 - 1.36.

If I heard someone say they prefer 3.6 - 1 - 1.1, I'd think they're talking about labels (the way most people think of NPK?). If they're talking about ppms, they really mean 1.5 - 1 - 1.4.

Click to expand...

This is why 1:1 ratio of N : P2O5 is reasonable, but 1:1 ratio of N : P might be too much P.

If you look at P2O5, it looks like you're using way more P.

FYI: I substantially re-wrote the spreadsheet:

Changes:

1. Specify actual amounts used, not abstract "parts."
2. Eliminated the 1-20 product worksheets. Now you can have as many product worksheets as you want. You access up to 10 of them by entering the worksheet name (the name in the tab) as the key.
   This makes the spreadsheet load/save faster. And, it's easier to visually locate a product worksheet.
   
3. There's only one calculation worksheet which performs both the mix and unmix objectives.
4. PPMs are calculated using P2O5 and K2O. This should be close to the actual ppm you'll see after mixing nutrients, not a theoretical ppm of just P and K. (More info in the README.).

I originally thought working in "parts" would be simpler. You could play with NPK ratios without thinking about the strength. (You used "total weight" to scale the amounts up or down to get a desired PPM without changing the NPK ratio.).

But, it seemed tedious that way. Two calculation worksheets (to "unmix" an existing regimen into "parts" which could be input into the "mix" worksheet to play with parts and see the resulting amounts to use.).

Now it's just one worksheet to perform both goals.

One thing which may not be clear: If you find an NPK ratio and want to scale the PPM (strength) up or down, you can use the Quantity (to make) field. This field is intended for telling you the amounts to use if you want to mix more than one gallon (or liter). But, you can use it to tell you how much to increase or decrease the input amounts. For example, if you want to reduce PPMs by 20%, enter 0.8 in the Quantity (to make) field. Then transfer those amounts to the input amounts at the top.

It works with OpenOffice and LibreOffice (free office suites). It should work with Microsoft Excel, but I don't have it to test with.

I've been gone for awhile. But, I updated the spreadsheet. It calculated PPMs incorrectly. I added a flag to indicate PK are "as P2O5" (or K2O). This results in a little more info about the elemental PK.

https://www.rollitup.org/Journal/Entry/product-mixer-spreadsheet-to-find-npk-ppm-of-mixed-products.30205/

Still not sure if the PPMs are calculated correctly. But, the goal of the spreadsheet was to unravel a "boutique" multi-bottle "lineup" into NPK ratios which could be recreated with other (less mysterious) products. The PPMs are just a tool to be in a sane range of strength. If you use the spreadsheet, you'll notice the total PPMs are about 20% lower now (because the PK PPMs had been calculated higher). Now the total PPMs seem to be close to what I actually measure. Should be much more accurate now.

Is this thread open and is the Author receptive to questions? I know I was messin around in 5th grade and all but in my defense they didn't have MS Excel with an OpenXML/ODF Translator Add-In back in 1977.

EDIT: Disregard. I was able to resolve the issue.

orbo said:

Is this thread open and is the Author receptive to questions?

Click to expand...

Of course.

(It seems you had a reaction to my comment in another thread about two years in 5th grade. That was intended to be self deprecating. It took me about a year to understand these details.).

az2000 said:

Of course.

(It seems you had a reaction to my comment in another thread about two years in 5th grade. That was intended to be self deprecating. It took me about a year to understand these details.).

Click to expand...

Thank you for that clarification. I get stuff wrong sometimes.

As for the Product Mixer, I needed to install LibreOffice to make it work. Excel 2013 with the Add-In doesn't work well for calculating the formulas.

I did have a questions regarding the product page for BO-CM+. I see you had some values in there and I wondered why your values don't match the values on my bottle of BO-CM+? Did they make a change?

As you can see only a slight diff in density but they seem to have shaved off a whole percentage from the N;

Original BO-CM+


Updated BO-CM+

orbo said:

I did have a questions regarding the product page for BO-CM+. I see you had some values in there and I wondered why your values don't match the values on my bottle of BO-CM+? Did they make a change?

Click to expand...

Thanks. That must be a typo. My bottle says 2% N.