Product Mixer Spreadsheet

How To Use

Your input goes in GREEN cells. Important results are in RED cells. (Other cells should be protected, and prevent you from inadvertently changing them. You can unprotect the sheet by right-clicking on the worksheet tab.). Mouse over any cell with a red dot (■) to see an explanatory note.

You will use this spreadsheet to do three things:

1. Define a Product

Each Product is defined as a worksheet (the tabs along the bottom). Each Product Worksheet (tab) is named with an abbreviated Name. This will be the Product Key (used in the Mix worksheet). In this case the tab name is GH-FloG:

If the product is liquid, enter the Weight & Volume so a Density (grams per milliliters) can be calculated. If you don't enter this information, a default 1g per ml (the density of pure water) will be used. This will cause inaccurate results. (But, not terribly inaccurate.).[2]

Add more Products by copying an existing worksheet to a new name. (Right-click a tab, choose “Move/Copy Sheet,” and select “copy.” Right-click that newly-created tab to rename it. Then change the values of that new worksheet.).[3]

2. Find the NPK of a multi-bottle “lineup”

Let's say you use General Hydroponics Flora Series (3-part). You’ve considered using other products, but wonder how they compare. In the Mix worksheet, enter the quantities you feed in ml (for liquid products, otherwise the weight in grams for dry products.[4]). Your input goes in the GREEN cells:

Those are the amounts used in GH’s “1-2-3” flower schedule.[5] Note how the three Products were made available (for mixing) by entering the worksheet names in the Product Key. The important results are in RED.

Notice the NPK Ratio is 1-1.28-1.59. Also notice the PPM (849 for a gallon). That's extremely high. GH 3-part is typically used half strength. If we reduced the Amounts to 2.5, 5 and 7.5ml, it would be a more reasonable 425 PPM.[6]

3. Create a custom NPK

Now, let's create that same NPK ratio using other products:

The above three dry Products (in those gram amounts) produce the same NPK Ratio. I used enough of each to get a sensible PPM for my light soil (I know this is a reasonable strength from my own use of Grow More - Sea Grow.[7] Going stronger risks salt buildup in early- to mid-flower. Sea Grow contains organic sources of nutrients. It’s different than purely synthetic nutrients for hydroponics, like GH Flora 3-part.).

Notice the PPMs "by product." Fertilizer products typically produce more PPMs than their labeled strength. "Guaranteed analysis" (percentage of weight) are minimum guarantees. The best way to use this spreadsheet is to add half the specified amount of the product, ensure it's dissolved (if dry product), and measure the resulting PPMs. Determine how much more product to add (to get the PPMs shown in the "by product" cells.). This sounds tedious. But, after you have done it a few times, and keep track of the amounts you add (and the volume of water added to), you'll know how many PPMs your product creates compared to what the label guarantees.

Notice the Volume to make section. This is a convenience feature which will calculate the Total amount to use when making more (or less) than one gallon (or liter). When you have a feel for how many PPMs your product creates (compared to what it calculates as creating), you can adjust those amounts to get the desired PPMs.

Remember: These Amounts are not expected to produce the calculated PPMs. You're expected to add enough product to get the desired PPMs. Through that exercise, you'll learn how to adjust these spreadsheet Amounts to your product's actual strength.

Hint: A handy use for this Volume to make feature: when you find the right NPK ratio but the PPMs are too low/high, you can scale the amounts up/down using Volume to make. For example, let's say you wanted to raise the PPM 10%. Enter 1.1 in Volume. Then enter those resulting Total amounts in the Amount (Products to use). The Ratio will remain the same, but the PPMs will rise 10%.

Additional Topics

1. Adding products

The Products worksheets page contains many Products Worksheets you can copy into the Product Mixer spreadsheet.

Warning: a spreadsheet containing many worksheets can be slow to open and save. If “Autosave” is enabled, the spreadsheet will seem to hang for no reason. (Be patient. Or, you can disable Autosave in Tools > Options > Load/save).

It’s best to keep unused Product Worksheets in a separate spreadsheet. Right-click a worksheet tab and choose “Move/Copy” to copy. (Both spreadsheets must be open to copy a worksheet from one spreadsheet to the other.).

2. Measuring sub-gram amounts of dry fertilizers

You need a jeweler's scale for best/reliable results. I have a Horizon HB-01 (100g capacity; 0.01 legibility) and PRO-20B (20g capacity; 0.001 legibility). I found eBay seller “AnyVolume” (who also has a website http://www.AnyVolume.com). I use small plastic dosing cups, like those that come on the top of a cough-syrup bottle. Or, plastic condiment containers used by fast-food restaurants for to-go orders.

3. NPK labels are not accurate

NPK labels are a guaranteed minimum content. In reality, products are typically 10-20% stronger. (My Akaska Fish is 50% higher.).

This means you can't just measure your specified grams (or milliliters) and dump it in, expecting to have the calculated NPK Ratio & PPM strength. It's almost assured you'll end up with stronger. If you want to be precise, pour half the amount of a single product, stir and measure the PPMs. You can keep adding small amounts to get the precise PPMs you want (for each product). After doing this a few times, you'll know how many PPMs a product creates. You can easily adjust your specified amounts accordingly without having to be so careful about PPMs.

4. See "cell comments" for more information

Many cells have comments (■) to explain its usage or calculation. Hover your mouse over one to see the comment.

5. Copy/paste “special” values

If you copy/paste anything be sure to use Edit->Paste Special (CTRL-SHIFT-V). Choose only “Text” and “Numbers.” If you don't do this, you'll paste comments, formatting and formulas.

You can quickly wreck the spreadsheet using ordinary (CTRL-V or SHIFT-INSERT) pasting.

6. P & K (aliases for P₂O₅ & K₂O). Si confusion too.

This is a confusing topic.

P and K are usually supplied as P₂O₅ and K₂O. That weighs more than elemental P and K. When a fertilizer product says it contains 10% P by weight, it actually contains 10% P₂O₅ – which is 43.64% elemental P by weight. (If it says 10% K, it actually contains 10% K₂O, which is 83.02% elemental K.).

Normally you wouldn't concern yourself with this. But, PPMs are based upon the amount (weight) of the element, not the compound. Therefore, when adding Product Worksheets, it's important to accurately specify how the product's label lists P, K and/or Si. In the US, P & K are (as far as I know) always shown on the label "as" P₂O₅ and K₂O.

Silicone is more confusing. Some products state their Si content as Silicone Dioxide (SiO₂). This compound contains 46.74% (by weight) of elemental Si. Other products state their Si content as Monosilicic (sometimes called Orthosilicic) Acid, which contains 29.22% elemental SI. (Finally, I've seen one product which states the content of Si as elemental Si.).

So, again, when adding a Product Worksheet, be sure to understand this topic and set the "as" value correctly. In the US, the only products you need to concern yourself with are those with Si. That seems less standardized. (Outside the US, I don't know.).

Three important things to keep in mind about this spreadsheet:

• NPK ratios are calculated by combining the product labels of the products used. If you could find a product sold with that same mixture, it would have a “Guaranteed Analysis” the same as this spreadsheet calculates.

• In some ways, the calculated PPMs are informational only (if you were to add the specified Amount). In practice, you should add half the specified Amount to water, and measure how many PPMs were added. In time you'll learn how calculated and actual PPMs differ. When you know that, you can always adjust the specified Amount accordingly, and not have to spend time finely adding PPMs.

The conversion factors are:

• To convert PPMs (or weight) of P to P₂O₅: multiply by 2.291. To convert P₂O₅ to P: multiply by 0.4364

• To convert PPMs (or weight) of K to K₂O: multiply by 1.205. To convert K₂O to K: multiply by 0.8302

• To convert PPMs (or weight) of Si to SiO₂: multiply by 2.1395. To convert SiO₂ to Si: multiply by 0.4674

• To convert PPMs (or weight) of Si to Monosilicic Acid: multiply by 3.4223. To convert Monosilicic Acid to Si: multiply by 0.2922

7. Circular references ("Err:522")

You may see Err:522 occasionally. This is due to the way the calculations are laid out, with the results at the top of the worksheet (creating circular references). You can stop this error with:

Tools > Options > OpenOffice.org Calc > Calculate. Enable Iterations.

That lets the spreadsheet recalculate until the error goes away.

8. Compatibility

This spreadsheet was created in the free LibreOffice Calc. It uses no macros nor any obviously proprietary formulas. It should work in any spreadsheet program. If you feel it isn't working, please download LibreOffice to verify. See: https://www.libreoffice.org/

9. Inspiration and limitations

This spreadsheet was inspired by HydroBuddy.[8] It is intended to promote a similar spirit of DIY, flexibility, openness, etc. However, this spreadsheet is for pre-mixed commercial fertilizers -- especially mystery “lineups” with franchised feeding schedules. This spreadsheet allows you to see what you're really feeding, and alter (or reproduce) that. It's not intended for mixing raw nutrients (bulk salts. Use HydroBuddy for that.).

One limitation: This spreadsheet works with fertilizer “guaranteed analysis” showing “percent of weight.” This is the way all fertilizer products are labeled in the US. Other areas of the world may represent nutrients as “percent of volume” or “weight / volume” ratio. (I don’t know how to convert between those. If someone knows, maybe this spreadsheet could be enhanced to work with both labeling standards.).

License

You are free to redistribute and modify this spreadsheet, but please maintain a reference to the original source. If you distribute a modified version of the spreadsheet, please use a different name and maintain a link to the original.

Changes

2014-August-12: New.

2019-July-25: Changed the formula in row-4 to detect a non-numeric value in row-5. It was possible to make a typo, like 1,5 (using a comma instead of a period). The spreadsheet silent ignored that value. If the user had a few other products being mixed together, they might not notice one product was ignored.

2020-October-1: Added "as" values for P, K & Si (in the product worksheets). This is more flexible (and Si PPMs weren't being calculated correctly previously.). Also added PPMs "by product" for easier visibility of the PPMs you should target (for each product).

Footnotes

[1] This spreadsheet deals with fertilizer products whose labels show ingredients as percent of weight. (For example: 5-3-6 means 5% of the product's weight is nitrogen. If the product weighs 1 pound, then 0.801 ounces, or 22.7g is nitrogen.).

US fertilizers are labeled this way by law. Other countries may use labels which display ingredients as “percent of volume” or “weight / volume” ratio. Such labels don't express weight, which varies by each nutrient's molecular weight. This spreadsheet is only accurate with products whose labels show “percent of weight.”

[2] Most liquid products have the “net weight” and volume printed on the bottle's label. If not, you’ll have to call the company and ask for it. You can also use the weight of a similarly-sized product containing similar NPK values. That would be closer than pure water, which is 1ml = 1g.

[3] See the Products worksheets page which contains many products ready to be copied into this Product Mixer spreadsheet.

[4] To use this spreadsheet with dry products, you will need a fairly accurate sub-gram scale. The Horizon Pro-20B jeweler scale is very good & inexpensive. It measures up to 20 grams with 0.001 resolution. You don’t need that much precision. A Horizon HB-01 weighs up to 100 grams with 0.01 resolution. That’s good enough for this purpose. (Those scales are sold by AnyVolume.).

[5] GH Flora's feeding schedule in bloom is called “1-2-3,” referring to teaspoon amounts. (In veg, it's “3-2-1.”). People normally run half strength (2.5-5-7.5 ml). An excellent schedule for GH Flora 3-part is the so-called “Useless schedule.” See: https://forum.growkind.com/threads/gh-3-part-useless-formula.33799/ It’s stronger than half-strength, but not too strong. If I used it again, I would reduce its strength by 10%.

Note: Using this spreadsheet, I have resolved the NPK ratios & PPM strengths for GH Flora 3-part's schedule. It can be seen in the schedules subdirectory. (Some other schedules are there too.).

[6] Fertilizer product labels typically (in the US, anyway) report phosphorus and potassium content as P₂O₅ & K₂O. This spreadsheet calculates the PPMs for elemental P & K, which is what results when added to water. This topic is discussed further in the "Other Topics" section.

[7] I created (and use) a schedule for Grow More - Sea Grow (see the schedules subdirectory). This strength works (without salt buildup) when I mix enough volume to get 10 to 20% runoff. Less runoff may require slightly weaker strength. I use it in a very light (almost soilless) medium. If the soil is richer, you should reduce the PPMs to about 200-240. If the soil dries fast, and you feed each watering, then consider how you're supplying more nutrients (through frequency). I would reduce the strength if I were feeding every 2 days (or more frequently).

[8] Download and learn more about HydroBudy at: https://scienceinhydroponics.com/2016/03/the-first-free-hydroponic-nutrient-calculator-program-o.html