Optimizing the drip line

This is a screen capture from my gardening log, which is in Excel. Excel is my brain expansion pack; I can do nothing without it.

So this is why I keep such obsessive records of how much water I'm putting into the garden. You see, for those of you who don't know me yet, I work out of town, therefore the garden has to water itself. Last year I was in Yellowknife and I didn't come home very much, and the garden got watered every two or three weeks. It didn't produce very much, and I also didn't take many photos. Last year was not a good year in any aspect of my life.

This year, I'm working way the heck out of town in Saskatchewan. Specifically, in the flooded part of Saskatchewan. And I am, as always, the low man on the totem pole, so everyone goes in before me. Therefore, I'm "working" in Saskatchewan in principle, but in practice I'm at home on shortage of work, pending an end to the floods. But if it hadn't been the worst year on record for floods, I would have been home only once a month or maybe less. Therefore, I bought a drip system for the garden. And now I need to know what size drippers to use for each planter, right?

The drippers come in 2, 4, 8 or 16 L/hour, and the total drip can't exceed 200... wait, now I wonder. Was it 200 L/hour or 200 gal/hour? I've been using liters, but when I think about it, all the information I read was in gallons, so it must have been gal/hour. That makes a huge difference.

Anyway, I'll check the units later, but for now, this is how the drip line optimizer works. First I take the amount of water for each planter in the last three weeks. The oriental lily and the baobabs are excluded from the calculation because the amounts are too small.

Then, whichever planter uses the least water per day becomes the reference. Currently, and for the foreseeable future, it's the geraniums. This becomes "1" in the "drip needed" column, and everything else is expressed in units of the geranium. So you can see, the Jungle uses 13 times as much water as the geranium.

Next, I multiply it. By a multiplier. How do I find the multiplier? Simple. I need to get as close as possible to the drip line's maximum output, be it 200 liters or gallons per hour, without going over. So I multiply the "drip needed" by the multiplier and get "max drip". If the total drip exceeds the line capacity, the cell lights up red. But of course I'm constrained by the drippers, too, so everything has to be a multiple of 2 L/hour. Excel takes care of the rounding. I love Excel. So under "max drip" you can see that everything is an even number and it adds up to less than 200.

In the next column, "drip per week", we find out how many minutes per week each planter needs, based on its consumption in the last three weeks and the drippers determined in "max drip". And at the bottom I take the average of these numbers, and then a standard deviation, to see the spread, obviously. And then I express the standard deviation as a percentage of the average, so it's more legible. So now we see why I need the multiplier. The higher the multiplier, the lower the percentage is going to be, because I can create a closer approximation of each planter's optimal drip. Under the current system, you can see that the planters will mostly be getting within 10% of their requirements, as determined by their consumption in the last three weeks.

After that the columns show how many of each size dripper each planter needs, and the last column checks that I've got the right amount by lighting up in yellow. If I don't have the right amount of drip, it stays white.

So there you have it. The drip line is now optimized. Except that if I've been using the wrong units, I can almost quadruple my multiplier, which would give me much better accuracy; on the other hand I only have so many of each dripper, and there is a limit to how many I would put in each planter anyway, for obvious reasons of space. So I'm gonna have to see how many drippers I have, check the units, and then re-optimize it. That will kill some more time. Yay! I got lots of time to kill, as it happens.