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

And for my finale - below is a snippet from my product page for FoxFarm Open Sesame. Did I get that right?

az2000 said:

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

Click to expand...

Ok, cool. Thought maybe I was doing something wrong.

I would just like to say to the community that this tool is extremely handy for modeling boutique recipes. EXCELLENT work az2000 and thank you for your efforts.

I'm not familiar with LibreOffice, is there any handy mechanism for logging weekly recipes for trending and reference purposes?

EDIT: Disregard, I can just save it each time with a date and time stamp.

I'm glad you like it. It opened up a new world to me to think about NPK ratios instead of opaque, multi-bottle schedules.

I think one of the best "outreach" activities we can do for new growers is to encourage them to use the spreadsheet just to be aware of the resulting NPK ratios they're using. That might help them get out of the "collect'em all!" mentality, more of a plug-n-play way of thinking about how to do the same thing with generic products. At least people on a budget could benefit from that.

orbo said:

I'm not familiar with LibreOffice, is there any handy mechanism for logging weekly recipes for trending and reference purposes?

Click to expand...

I put the results near the top so a person could copy/paste it into a log file. A text editor. Or, LibreOffice Write will preserve the formatting.

I thought about making it a program like HydroBuddy. Then it could save details like a journal. Maybe one of the "grow software" products will integrate something like this into their software. The calculations aren't hard compared to the larger grow software.

I'm playing with moving all the Product Worksheets into their own spreadsheet. That makes the main (calculation) sheet work faster. Easier to browse through the Product Worksheets (without losing focus of the calculation sheet). But, that might be more complicated to the average person.

I can't decide if it's better to 1) reference Product Worksheets in a separate spreadsheet -- keeping them permanently separate from the calculation spreadsheet. Or, 2) keep the Product Worksheets in a separate spreadsheet only as a place to hold them when unused. I.e., right-click the worksheet tab to move it in and out of the calculation spreadsheet as needed.

Having a lot of products in the calculation sheet makes it tedious to find a few things to work with. That's why I want to move them out. But, I can't tell if it's better to 1) reference them in the separate spreadsheet, or 2) "right-click -> move" as a manual process to include/exclude desired products.

#1 can be a little slower. Sometimes it takes 3-4 seconds for it to resolve information from the external separate sheet. Maybe right-click -> move is the way to go. But, I don't know if that would be too tedious for the average person (compared to specifying the path to the external sheet, then referencing the products by key (abbreviated name). I'm concerned specifying the path might be tedious for the average person too.

az2000 said:

I'm glad you like it. It opened up a new world to me to think about NPK ratios instead of opaque, multi-bottle schedules.

I think one of the best "outreach" activities we can do for new growers is to encourage them to use the spreadsheet just to be aware of the resulting NPK ratios they're using. That might help them get out of the "collect'em all!" mentality, more of a plug-n-play way of thinking about how to do the same thing with generic products. At least people on a budget could benefit from that.



I put the results near the top so a person could copy/paste it into a log file. A text editor. Or, LibreOffice Write will preserve the formatting.

I thought about making it a program like HydroBuddy. Then it could save details like a journal. Maybe one of the "grow software" products will integrate something like this into their software. The calculations aren't hard compared to the larger grow software.

I'm playing with moving all the Product Worksheets into their own spreadsheet. That makes the main (calculation) sheet work faster. Easier to browse through the Product Worksheets (without losing focus of the calculation sheet). But, that might be more complicated to the average person.

I can't decide if it's better to 1) reference Product Worksheets in a separate spreadsheet -- keeping them permanently separate from the calculation spreadsheet. Or, 2) keep the Product Worksheets in a separate spreadsheet only as a place to hold them when unused. I.e., right-click the worksheet tab to move it in and out of the calculation spreadsheet as needed.

Having a lot of products in the calculation sheet makes it tedious to find a few things to work with. That's why I want to move them out. But, I can't tell if it's better to 1) reference them in the separate spreadsheet, or 2) "right-click -> move" as a manual process to include/exclude desired products.

#1 can be a little slower. Sometimes it takes 3-4 seconds for it to resolve information from the external separate sheet. Maybe right-click -> move is the way to go. But, I don't know if that would be too tedious for the average person (compared to specifying the path to the external sheet, then referencing the products by key (abbreviated name). I'm concerned specifying the path might be tedious for the average person too.

Click to expand...

Have you considered putting the path in one cell one time and then referencing that cell for all path requirements? Just a thought.

I have pretty much read all of your notes and the Readme and believe the PPM value is supposed to be somewhat accurate at this point, correct? The reason I ask is that you can see my product config for FoxFarm - Open Sesame in post #21 above, which I'm assuming is correct, gives me a PPM of 175 when mixing just 1 amt as a standalone;

However in real life when I add 1 tsp of FF-OS to 1 gallon of water I get 597 PPM. This deviation was worthy of asking about. Do you see anything wrong in my product setup?

Better yet, how about a data form to capture Product Details? This way you could easily store, edit, reference and maybe even create a pseudo-table of contents of the stored form data?

orbo said:

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

Click to expand...

The Density difference between my Product Worksheet and yours is interesting. I have both a quart and gallon ("I am not a smart man."-- Forrest Gump.).


They come both come out 1.05 g/ml. However, there are 946ml in 1qt (not 960). Consumer labels probably aren't very accurate. There's probably rounding errors.

If you're not in the US, your labels might be based upon w/v. I haven't understood that topic yet. In the US everything is %w of weight (as far as I know). PK 13/14 (product name) results from w/v, I believe. Under our w/w system, it's 10/11 on the guaranteed analysis. I don't know if that type of labeling still exists.

I noticed you defined a Density for Open Sesame (a dry product). I haven't done that. I weigh everything that's dry. Maybe I should emphasize how it can be used for dry product as a convenience (abstraction) for measuring.

I think I saw a post earlier today asking if the PPMs are accurate. I think so. But, I'm not clear how dry chemicals containing H & O molecules dissolve or give up their gas(?). For example, epsom salt is MgSO4*7H2O. I'm pretty sure that stuff after the * is something like water. It must not contribute ppms, yet it's weighed out when preparing a gram to dissolve in 1 gal. I'm not sure where that goes. (Same thing with calcium acetate which contains h3o2. I don't know if that dissolves into the water and doesn't count as "parts".).

Also, the guaranteed analysis (in the US, at least) is a guaranteed minimum. I imagine companies (especially the mass-consumer variety) manufacture for 10% more just to avoid any fines by the Department of (whomever).

It wouldn't surprise me if the more glam'd up, secret-saucey companies don't completely misstate it to hide their "proprietary" ratios. For example, a company might label a product 6-12-8 but actually use 8-16-14. That would be perfectly legal. You're getting the minimum quantity you bargained for. But, the company isn't selling a commodity. They don't worry about someone selecting a different brand because "you get more nutrients for your money." They bank on people buying the the experience (the cartoon labels, the "collect 'em all" gimmick, and the good results which you wouldn't be able to recreate from their labels.).

I calculate a "per the label" PPM and an "actual" which is intended to represent PPMs if only elemental P and K are counted (not the heavier P2O5 and K2O). For now, I think it can be expected to be somewhere between those two PPMs. I'm still comparing calculated to observed.

orbo said:

However in real life when I add 1 tsp of FF-OS to 1 gallon of water I get 597 PPM. This deviation was worthy of asking about. Do you see anything wrong in my product setup?

Click to expand...

You still have to specify the Amount in ml (volume) or g (weight). You can use volume for a dry product, but you have to specify the amount in ml. The Density adjusts the volume to weight. Treating a dry product as a liquid.

az2000 said:

You still have to specify the Amount in ml (volume) or g (weight). You can use volume for a dry product, but you have to specify the amount in ml. The Density adjusts the volume to weight. Treating a dry product as a liquid.

Click to expand...

I don't understand? Is there some way to differentiate volume vs weight? When I define a Product - and as such the Liquid Density calculates a grams per milliliter (g / 1 ml) amount for me - I assume this g / 1 ml value is what is used with the multiplier on the mix page (in the Products to use->Amount section)? I'm thinking I missed something.

orbo said:

I don't understand? Is there some way to differentiate volume vs weight? When I define a Product - and as such the Liquid Density calculates a grams per milliliter (g / 1 ml) amount for me - I assume this g / 1 ml value is what is used with the multiplier on the mix page (in the Products to use->Amount section)? I'm thinking I missed something.

Click to expand...

If you want to specify Amount as teaspoons, in the Liquid Density's Weight cell, multiply that by 5 so the resulting Density value is 5x more than it would be. Then, when you specify 1 in the calculation sheet's Amount cell, it will stand for 5 ml, not 1 ml.

I work with grams and milliliters because, pure water, an ml of water is a gram. The Liquid Density makes that equivalent when the water isn't pure. When entering Amounts for liquids I think in terms of "5" is tsp ("7.5" is a 1-1/2, etc.).

You're going the other way, treating a dry product by volume. If I did that, I would still think in terms of "5" is a tsp. You're basically treating dry as a liquid. So, you would specify ml's instead of grams. The Density works out the difference for you. If you wanted to work in units of teaspoons you could adjust the Denisity to represent that. (But, I wouldn't. It seems like it would be confusing compared to just thinking in multiples of 5).

FYI: I uploaded a new version of the spreadsheet. Initially, this was just to be a reorganization of the product worksheets into an external spreadsheet (too many product worksheets, slow to save, etc.).

But, I noticed a bug in LibreOffice. When you Move/Copy a product worksheet, the Liquid Density calculations (in the product worksheet) stop working. It says "Unit!" (as if it doesn't see the drop-down unit of measure). So, I moved those calculations into the main spreadsheet. I left the original calculations in the product worksheets in case this bug is fixed someday. But, just know that if you see "Unit!" you can ignore it. (Or, you can copy the formula for that cell and paste it back into itself. That fixes it -- until you copy/move the worksheet to another spreadsheet again.).