Blueberries, Watering and Excel

[hatman]

Anyone who has glanced at my profile has probably noticed that in addition to my day job, I also have a blueberry farm at home. It's a fun sideline that I inheritted from my grandfather when he passed away several years ago. And 500 bushes is enough that I not only take the work, but also the profit seriously. For those of you interested in what 500 blueberry bushes look like, here's some aerial photography.

Being the engineering geek that I am, this spring I started building an Excel File to track and trend various factors in the business (if you can call it that). The biggest concern for me is the amount of water that I provide for the crop. The requirements for watering is expressed as 1-2 inches for each 7 day period. Calculating a running sum of the previous seven days is no problem... and I have what seems to be a satisfactory method of calculating how long I should run the sprinklers each night to provide a minimum of 1 inch of water by the end of a running 7 day period. As anything of this nature goes, I have been tweaking my prediction algorithms as I get more historical data.

Okay, so I'm finally getting closer to the point. I have enough historical data for this season so far that I want to get an idea of how well I have been sticking to the 1-2 inches of water per week. Using a helper column, I can easily create a list of values that represent the sum of each previous 7 day period (E11:E26), and then average that (E28). But I figure there should be some way to do this in a single CSE formula. But I'm at a loss. D28 has my best attempt... but it just returns the first member of the series. Obviously, there is something about this CSE formula that I'm missing... I was hoping that someone might be able to explain what I'm missing, and perhaps offer an alternative.

Eventually, I also want to expand this to utilize a dynamic range based on variable start and stop dates... but that's for next time

Book1
	C	D	E	F
5	06/01/07	0.125
6	06/02/07	0.0625
7	06/03/07	0.25
8	06/04/07	0.5
9	06/05/07	0.0625
10	06/06/07
11	06/07/07	0.3	1.3
12	06/08/07	0.4	1.575
13	06/09/07		1.5125
14	06/10/07	0.125	1.3875
15	06/11/07	0.25	1.1375
16	06/12/07	0.25	1.325
17	06/13/07	0.2	1.525
18	06/14/07	2	3.225
19	06/15/07		2.825
20	06/16/07		2.825
21	06/17/07	0.125	2.825
22	06/18/07	0.0625	2.6375
23	06/19/07	0.125	2.5125
24	06/20/07	0.25	2.5625
25	06/21/07	0.25	0.8125
26	06/22/07	0.3	1.1125
27
28		1.3	1.94375
Sheet1

 

[Greg Truby]

Paul,

I ain't an ag major nor even a farm boy. My only experience w/ irrigation is laying a couple of miles of pipe every spring (and picking it up every fall) on Grandpa's farm and o' course, settin' gates and the usual spadework.

Nonetheless, I would have thought that irrigation requirements would be driven by temperature, rainfall and where you're at in the growing cycle. (But then I do have a tendency to over-complicate things!)

 

[hatman]

Paul,

I ain't an ag major nor even a farm boy. My only experience w/ irrigation is laying a couple of miles of pipe every spring (and picking it up every fall) on Grandpa's farm and o' course, settin' gates and the usual spadework.

Nonetheless, I would have thought that irrigation requirements would be driven by temperature, rainfall and where you're at in the growing cycle. (But then I do have a tendency to over-complicate things!)

It's funny, all of my sources for Blueberries simply state that from appearance of the first leaves in the spring to losing the last leaves in the fall the bushes require 1-2 inches of rainfall per week. There are some additional statistics that on a hot day, a fully mature bush can consume some ungodly number of liters of water in a day (like 30 or something), but no discussions regarding how temperature or humidity variations impact the need for water. In general, I try to keep the ground moist as consistantly as possible... some weeks that means closer to 2 inches of water, some weeks it means less than 1 inch, in spite of what my calculator is telling me. And then there are days (like this past Saturday) when I get 2 inches of rain in an hour... obviously, that doesn't mean I stop watering for 2 weeks.

Obviously I still have a lot to learn... my grandfather planted the bushes in the 1960's... and took all of his experience to the grave, telling us that we could figure it out as easily as he did.

 

[Smitty]

NOTE beforehand: I'm not in Ag and haven't watered anything other than my yard in a long time...

I'd check with your local Agricultural Extension Service.

You can get really detailed with how you monitor water application and things like your evapotranspiration rates, to how well your plants are absorbing water based on the water table in a particular zone.

I think blueberries have fairly shallow roots and need a fairly shallow water table as a result, which may also have a bearing on how well they can utilize fertilizer. You can take a soil sample to a local ag lab and have it tested for Ph and other key indicators of what the plants need to really grow well.

How does your 1-2 inches translate into gallons per plant/week?

Is there blueberry packer or association around you? (http://www.blueberry.org/ might be a good start). Almost every industry association has different methods for determing recommended water & fertilizer applications and how to measure it.

Smitty

 

[Greg Truby]

Paul,

Ummm, seein's ta how neither Smitty nor I have actually answered your original formula question...

I think this is what you were after:<ul>[*]{=AVERAGE(SUBTOTAL(9,(OFFSET(D5,ROW(INDIRECT("1:16"))-1,0,7,1))))}[/list]

 

[hatman]

I got a little bit larger scope on the feedback than I expected. Not sure where to start...

Smitty: There are a lot of tests I could pay to have done (and others I could probably get done for free), which would potentially drive me to change certain ways that I am doing things. There are a lot of variables that are potentially under my control, though some cost more (time and/or money) to control than others. The easiest and most obvious thing for me to control is when I turn on my irrigation pump, and how long I leave it on. As you point out, I can get as technical with even that seemingly simple set of decisions as I want. Right now, I'm keeping my sights low: provide the crop with consistant soil water content. I'll see what I need to change from there. I can tell you that we have had the most productive year ever, so far... even regular customers who have been coming for the last 40 years have commented on how lush and green the bushes seem compared to past years.

I really couldn't speculate on what 1-2 inches of precipitation per week translates to gallons per plant. The best estimate I can make is based off of my irrigation system: 3 hours of irrigation is approx 0.25 inches of precipitation, and each of my 17 heads consumes about 2.85 gallons per minute at the pressure I have measured at the heads... average all that out for 500 bushes, and I get somewhere around 70 to 140 gallons per plant per week. Based on this type of calculation, I figure I have put about 200,000 gallons of river water on these thirsty devils since mid May.

Yes, Blueberries have a very shallow root system. In the wild, they tend to grow best in Swamps, Bogs and along Rivers, Streams, and Lakes. As far as fertilizer uptake, I have been following the recommendations in several Berry Growing books for type of fertilizer and when and how much to apply.

My father and I are still learning (dad is retired, and comes down most days to help out, while I work at my day-job). Good suggestion about a local blueberry association. There are a few other farms in the area, and we all sorta collaborate... but to be honest, I have the most sophisticated system, with the most controls in place. My competition tends to let nature have the upper hand, including an apparent lack of proper pruning (that's another thing we're still trying to figure out, with 10 varieties, and no map of which bush is which variety, it gets tough).

Greg: Thanx for answerring my Excel question! I don't get it. I mean, I see what you are doing (I often forget about Subtotal() as an alternative), but I can't figure out why my usage failed to work. Okay, so you are indexing the Row Offset argument, instead of the Origin argument... and you are using Indirect() to break the link with an actual adress, so inserts and deletes don't affect this (I started with that too, but eliminated it as a possible source of a typo)... fine, I see those as best-practice nuances, which shouldn't impact the basic functionality of how the result is computed (shouldn't they?). Why Subtotal() rather than Sum()?

I haven't had a chance to play with this yet... so my observations are based purely on interpretting youe formula versus mine... I hope to be able to re-visit this by the end of the week (home and work have BOTH thrown me curve-balls, and suddenly time is non-existant)

 

[Greg Truby]

The ROW(INDIRECT("k:n")) bit is a common trick in array formulas to generate an array of integers k, k+1, k+2,...n. I use SubTotal() because , for reasons that Aladin seems to understand but I don't, sometimes SubTotal() will play nice in array formulas where Sum() won't.

 

[hatman]

The ROW(INDIRECT("k:n")) bit is a common trick in array formulas to generate an array of integers k, k+1, k+2,...n.

Yup, I do that myself, most times...

I use SubTotal() because , for reasons that Aladin seems to understand but I don't, sometimes SubTotal() will play nice in array formulas where Sum() won't.

Something Aladin understands, but Greg doesn't... I can't hope to compete in such august company ray: I guess the lesson I need to take away is to keep Subtotal() in my back pocket as a backup for Sum() when it fails to work as expected in Arrays. Thanx!

 

[hatman]

Paul,

Ummm, seein's ta how neither Smitty nor I have actually answered your original formula question...

I think this is what you were after:<ul>[*]{=AVERAGE(SUBTOTAL(9,(OFFSET(D5,ROW(INDIRECT("1:16"))-1,0,7,1))))}[/list]

In it's final form, I ended up with this:

=AVERAGE(SUBTOTAL(9,(OFFSET(INDIRECT("E"&A9),ROW(INDIRECT("1:"&A10-6))-1,0,7,1))))

where A9 caculates the row of the first datum, and A10 calculates the row of the last datum... that way, as I add data through the season, the average is always current... when I copy this sheet for next year, the result will be bogus for the first 7 days, but I can live with that...

In case anyone cares, my result to date is an average of 0.83 inches per week... which makes sense, given the data.

 

[Oorang]

Pointless addition to thread:
I love blueberries They are the yum