Flow calculation Excel sheet for flume hourly data

How do you calculate hourly flows from flumes, weirs, and orifices, when what you actually measure is water depths? That’s how these devices work: the device creates an engineered curve, so that the depth at a certain distance from the entrance or exit gives an accurate flow.

Getting the flow equation is simple enough – for the flume below, flow = 11.6948 * (depth + 0.0068932) ^ 1.64505. What if you divert for seven months of the irrigation season? 7 months * 30 days * 24 hours. That comes to 5,040 measurements – too much to calculate by hand.

A concrete and metal water control structure with a gate and surrounding vegetation, situated in a streamside environment.

You’ll need to use Excel or similar software to handle all the data. Here is an example of a spreadsheet for this flume. Data is copied into the sheet from the water level logger output, then the spreadsheet automatically calculates the corrected depth, flow, and hourly volume.

Why are there columns “Year” and “Month” in this spreadsheet? Those are for pivot tables, which can quickly sum many thousands of rows of data, by year and month. A pivot table is used to get summed monthly flows and the maximum flow for each month, which are necessary to fill out your annual report to the Water Board.

A downloadable Excel file of this example spreadsheet is included at the bottom of this post.

Spreadsheet showing data for the Watchman Briggs 3-Foot Wide, 2-Inch-Ramp Flume, including water depth, flow rates, and calculations from the Hobo MX-2001 Transducer as of April 2, 2026.

Using Circular Headgates as Submerged Orifices

There you go. Based on the change of stem height, you can calculate how much the headgate is open. Convert that to a percentage. For example. A 2.0′ diameter headgate open 0.5′ is 25% open. Then use the table’s 50% row to where it intersects the 2.00 foot diameter column to get an area of 1.913 square feet.

H-S Flumes For Accurate Measurements Of Small Flows

What if you have a small diversion, but grass or debris would interfere with a standard weir?  A weir has to have unobstructed, free-flowing water over

        Weir with debris and grass on crest

the crest so measured depths accurately relate to a calculated flow.  A weir with debris problems has to be cleared whenever flow is measured, which increases the time requirement.

When weirs have low flows, they trap debris more frequently, and they are less accurate when the depth over the crest drops below  0.2 feet (2.4 inches).  Then the only way to measure flow is with a narrow suppressed weir, or with a contracted weir, typically half or less the maximum width.  A V-notch weir can be used for measurement of low flows.

Changing the weir boards for different flows requires someone with experience,

                      Contracted weir

who will recognize when the depth over the weir is 0.2 feet or less and then use a contracted weir board.  However, people are busy when irrigating, and even busier when flows drop.  Weirs are often neglected during the time they need more frequent maintenance visits.

A good flume for passing debris and measuring low flows is the HS flume.  These are accurate right down to zero flow.  For the maximum flow, they require more

1.0-foot HS flume, for flows of 0.00 to 0.80 cfs

material than a rectangular Winflume, Montana, or Parshall flume.  However, they are more accurate than other flumes at very low flows – testing by the University of Minnesota found an average accuracy to be +/- 3.2% for ideal approach conditions.  They will pass debris down to zero flow – the flume shown here has an opening of 0.05 feet, or 5/8 inch at the flat bottom, and the opening increases with height.

        HS flume for flows up to 0.8 cfs

                 HS flume at 0.025 cfs

Why aren’t HS flumes common in California?  I suspect that the early adoption of Parshall flumes here established the standard.  I have seen a few hundred flumes, but I had never seen an operating HL (wide, high flow), H, or HS flume, prior to my installations.

Why go to the trouble of using an HS flume, if Parshall flumes are readily

                   New Parshall Flume

available?  A Parshall flume may be +/- 10% accurate down to perhaps 5% of its maximum flow.  Below that, the accuracy decreases.  An HS flume is +/- 10% accurate down to 1% to 2% of its maximum flow.  If the flow regime is predominantly low with occasional high flows, it is important to measure those low flows with the best possible accuracy.  Some places where low flow measurement is critical include field runoff where pollution is proportional to flow, small water rights, and dam leakage.

HS flumes are easier to construct than a Parshall, too.  The HS flume bottom is flat, and it has 3 vertical planes.  The photos of the Parshall flume here show

            Bottom of Parshall flume

that it has 3 horizontal planes, and 5 vertical planes.  An HS flume takes less time to build, and can be put together fairly quickly in any farm or ranch shop.  Parshalls are complex enough that they are purchased, including design and shipping costs.

Do Something Wrong, Instead Of Nothing Right

Do something wrong, rather than nothing at all. Have you ever heard that before? I have heard it from Army veterans, a boss, even an elder of a church.  George Patton said, “A good plan violently executed now is better than a perfect plan executed next week.”  A non-military way to say that is, “A poor plan now is better than no plan at all.”

What it means to you and me is, if action is necessary, do something, maybe ANYthing, rather than freezing in place or ignoring a problem. This is obvious when you see a tornado 5 miles away, for example; either drive away from it if you are in a car, or take shelter if you are on foot. If you have a plumbing leak in the house and no parts to replace broken pipe, then put a bucket under it, or turn off the valve, and call a plumber. All of us have seen a TV show (or maybe had it happen to us) where the bad guy pointed a rifle and said, “Don’t move”. What do we all say to the TV? “Don’t just stand there, run!”. Doing nothing is a much worse choice, if the result for freezing in place is death or injury.

What about water rights – how does doing something wrong help? Everyone knows by now that surface water diverters need measurement devices, so put in a weir box and boards and measure your flow before the threats come from the Water Board, your watermaster, your ditch tender, or your neighbor.  Even just stick horizontal boards in a ditch and seal the sides with plastic – something to take positive action to reduce future pain.

Remember to file the information for the measurement device with the Water Board, either via your annual report of diversions, or using the Water Right Form and Survey Submittal Portal.

Take a look at the blog posts below.  There is enough information and how-to directions, that you should be able to do it well enough to satisfy the Water Board.  Check out these posts:

There is a philosophy based in law and a lot of experience, that says don’t put any controls on yourself until the court or government makes

Temporary Weir In Ditch

you. Why remodel your house to accommodate the wiring or plumbing, if you aren’t selling the house and everything works okay right now? Who would put a lot of money into an old truck to make it pass smog, if it just might pass a smog check the next time it has to be done? What farmer would change how he irrigates or ranches if everything still operates and the bank will keep making operating loans?

All of the Water Board deadlines have passed to install measurement devices, or file Alternative Compliance Plans.  If you haven’t got your device or plan done yet, get a Request For Additional Time done as soon as possible.

Be proactive.  Take some inexpensive, temporary action.  Educate yourself for free with some time in the Internet. Even a small, less-than-perfect improvement in your measurement device, flow and water use record keeping, can pay back a lot more when you have to deal with potential Water Board fines, a court case, or even just an angry neighbor in the future.

Converting Logger Pressure to Depth & Storage/Flows

If you have a pipeline as part of your diversion, then an in-line meter with an integrated data collector can be installed.  The data files from these units are

McCrometer McPropeller inline meter with data collector

easily readable in Excel, and the files can be sent directly to the Water Board to meet the requirements of SB 88.

What if you don’t have a pipeline?  Then your flow needs to be measured in the open ditch with a weir, flume, or orifice.  These devices measure the flow but they don’t record the data.  To continuously record data, a submersible logging instrument must be used to measure the water pressure at the bottom of the box.  These logging instruments are commonly put into stilling wells that are inside or outside the measurement device.

Flume with attached stilling well for water level logger

How are water pressure logger measurements converted to diverted flows or reservoir storage?  Why does anyone even have to have an electronic pressure logger?  Onset ComputerPMCIn-Situ, , and other manufacturers sell data loggers and water level loggers, not pressure loggers, so why is this post talking about measuring pressures at all?

Bluetooth Hobo Logger, cabled to recorder and barometric compensator unit – least expensive option for a single location

Loggers record pressure, because that is the easiest physical attribute to measure.  A data logger in water does not know how deep it is, and it does not

 know how much flow is going by, or how much water is being stored in a reservoir.  Pressures relate directly to static (standing) water depths, and then equations convert the depths to flows, or to reservoir storage volumes.

How is pressure converted to depth?  It’s an easy calculation – water that is one foot deep has a pressure of 0.4335 psi at the bottom.  So, if your logger measures 1.60 psi, then the calculation to get depth is 1.60 psi / 0.4335 psi per foot = 3.69 feet of depth.

Note that water level loggers can be of two types.  The least expensive are completely submersible, and do not compensate for barometric pressure.  For an idea of  the readings of barometric pressure in a measurement device, a 2 foot deep logger records a pressure of 0.8670 psi.  Atmospheric pressure at sea level is about 14.7 psi, and high in the mountains may be 12.0 psi.  Air pressure is much greater than those measured in ditches.  Usually two of these loggers are used at once, one in the water, and one out of the water measuring only air pressure.  This also eliminates the variability in pressure due to weather changes.

Stilling well in a weir

The second type of data logger compensates for barometric pressure at the same time water pressure is being recorded.  That way, the water and air pressure data sets do not have to be combined before conversion to depths.  These loggers were always more expensive until the Bluetooth Hobo water level logger came along; as of February 2019 I found that it is the least expensive option for a single location.

Now that you can calculate any depth, how do you convert depths to reservoir storage?  That requires an Area-Capacity curve, also known as an Elevation-Storage curve.  The points can be picked off the curve.  For example, in the curve below, a depth of 8.5 feet would correspond to an elevation of 2,802.5 feet, and a reservoir storage volume of 30 acre-feet.

An owner of a reservoir with a capacity over 10 acre-feet must collect monthly storage values.  That’s easily done by hand.  However, a reservoir with a capacity of 50 AF requires weekly measurement; over 200 AF requires daily measurement; and over 1,000 AF requires hourly measurement.  That is really tedious to do by hand.

This is where an Excel spreadsheet can make the task a whole lot easier!  The spreadsheets shown below are just for this.  The first sheet helps translate a graph into a table of elevations and storage volumes.  The second sheet translates collected pressure values into depth and storage values, for as many data points as needed.

For diversion ditches from a stream, how are pressures converted to flows?  The logger is in a stilling well, usually a pipe connected to the inside or outside wall of the weir, flume, or orifice.  It measures pressure, which is easily converted to depths.

As with reservoirs, Excel spreadsheets make the conversion process a whole lot easier.  The sheets below have the rating curve for a suppressed weir, and the second sheet converts pressure to actual water depths over the weir boards.  Even for thousands of hourly readings, the hourly flow volumes are quickly calculated and are ready to send to the Water Board: 

← Back

Thank you for your response. ✨

Flow/Volume Data and Data-Heads

SB 88 requires diverters to measure diverted water flow and/or volume, then report the measurements.  For small to medium-sized diversions and reservoirs, there is a often transducer measuring and recording pressure.  The pressure data has to be converted to depth and flow, or depth and volume.  Data may be hourly, daily, weekly, or monthly.  Whatever the frequency, the Water Board wants data files uploaded with annual Reports and Supplemental Statements.

IMG_0740Where’s the manual for how to do this, for any of several data loggers, and for  meters, weirs, flumes, and orifices, and flumes?  It exists in pieces and parts.  Each data logger manufacturer has a manual for each product.  Sometimes products are similar, and sometimes very different, as are the manuals.  The long-existing measuring devices, weirs, flumes, and orifices, are described and general measurement instructions listed in the U.S. Bureau of Reclamation Water Measurement Manual.

When it comes right down to it, a person has to be a “data-head” to enjoy collecting the data, and going through all of it to find bad results, missing data, and odd trends.  Then stage and flow have to be calculated and checked against periodic readings taken during visits to the reservoir or stream.  Data have to be listed in a format to upload with the Report or Supplemental Statement to the Water Board, and summed monthly to fill out the online form.

If you enjoy educating yourself and taking on new tasks, then you can be a data-head.  If not, then you’ll need to have an employee do it, or more likely hire an expert.Data Head

Who are the experts?  There are engineering firms, manufacturers, vendors and others who can download data for you.  It still comes down to the person helping you That person who does the work has to have done data reduction, calculations, checking, and quality control in the past.

Make sure you get help from someone who knows data inside and out!  If the Water Board has any questions, your data-head can explain and defend every bit of it for you.  He or she will already know the answers to any questions that come up.

See Your Diversion With Social Distancing

Farmers and ranchers don’t spend much energy “implementing social distancing”.  For many folks, they do that already in their daily work.  However, I have a few clients that have to be really careful not to get sick – no shaking hands, keep a dirt road width between us.

That brings up a question:  do I always have to visit your place to see your diversion or reservoir the first time?  Not necessarily.  If I can see some live or recorded video, and some photos, that may be enough to advise you on what needs to be done, and a rough idea of what the options and costs are.  Especially if you are doing part or all of your installation, remote advice may meet your needs.

Do you have a smart phone?  That can take all the field videos and photos, and photos of necessary documents, too.

So, call and tell me what we’re looking at, and let’s try the video and photo option, especially if you are trying not to get sick!  If that just won’t work, then I’ll need to come out anyway, but I’ll have more information from what I have seen already.

H-S Flumes For Accurate Measurements Of Small Flows

What if you have a small diversion, but grass or debris would interfere with a standard weir?  A weir has to have unobstructed, free-flowing water over

        Weir with debris and grass on crest

the crest so measured depths accurately relate to a calculated flow.  A weir with debris problems has to be cleared whenever flow is measured, which increases the time requirement.

When weirs have low flows, they trap debris more frequently, and they are less accurate when the depth over the crest drops below  0.2 feet (2.4 inches).  Then the only way to measure flow is with a narrow suppressed weir, or with a contracted weir, typically half or less the maximum width.  A V-notch weir can be used for measurement of low flows.

Changing the weir boards for different flows requires someone with experience,

                      Contracted weir

who will recognize when the depth over the weir is 0.2 feet or less and then use a contracted weir board.  However, people are busy when irrigating, and even busier when flows drop.  Weirs are often neglected during the time they need more frequent maintenance visits.

 

A good flume for passing debris and measuring low flows is the HS flume.  These are accurate right down to zero flow.  For the maximum flow, they require more

1.0-foot HS flume, for flows of 0.00 to 0.80 cfs

material than a rectangular Winflume, Montana, or Parshall flume.  However, they are more accurate than other flumes at very low flows – testing by the University of Minnesota found an average accuracy to be +/- 3.2% for ideal approach conditions.  They will pass debris down to zero flow – the flume shown here has an opening of 0.05 feet, or 5/8 inch at the flat bottom, and the opening increases with height.

        HS flume for flows up to 0.8 cfs
                 HS flume at 0.025 cfs

Why aren’t HS flumes common in California?  I suspect that the early adoption of Parshall flumes here established the standard.  I have seen a few hundred flumes, but I had never seen an operating HL (wide, high flow), H, or HS flume, prior to my installations.

Why go to the trouble of using an HS flume, if Parshall flumes are readily

                   New Parshall Flume

available?  A Parshall flume may be +/- 10% accurate down to perhaps 5% of its maximum flow.  Below that, the accuracy decreases.  An HS flume is +/- 10% accurate down to 1% to 2% of its maximum flow.  If the flow regime is predominantly low with occasional high flows, it is important to measure those low flows with the best possible accuracy.  Some places where low flow measurement is critical include field runoff where pollution is proportional to flow, small water rights, and dam leakage.

HS flumes are easier to construct than a Parshall, too.  The HS flume bottom is flat, and it has 3 vertical planes.  The photos of the Parshall flume here show

            Bottom of Parshall flume

that it has 3 horizontal planes, and 5 vertical planes.  An HS flume takes less time to build, and can be put together fairly quickly in any farm or ranch shop.  Parshalls are complex enough that they are purchased, including design and shipping costs.

 

 

Converting Logger Pressure to Depth & Storage/Flows

If you have a pipeline as part of your diversion, then an in-line meter with an integrated data collector can be installed.  The data files from these units are

McCrometer McPropeller inline meter with data collector

easily readable in Excel, and the files can be sent directly to the Water Board to meet the requirements of SB 88.

What if you don’t have a pipeline?  Then your flow needs to be measured in the open ditch with a weir, flume, or orifice.  These devices measure the flow but they don’t record the data.  To continuously record data, a submersible logging instrument must be used to measure the water pressure at the bottom of the box.  These logging instruments are commonly put into stilling wells that are inside or outside the measurement device.

Flume with attached stilling well for water level logger

How are water pressure logger measurements converted to diverted flows or reservoir storage?  Why does anyone even have to have an electronic pressure logger?  Onset ComputerPMCIn-Situ, , and other manufacturers sell data loggers and water level loggers, not pressure loggers, so why is this post talking about measuring pressures at all?

Bluetooth Hobo Logger, cabled to recorder and barometric compensator unit – least expensive option for a single location

 

 

Loggers record pressure, because that is the easiest physical attribute to measure.  A data logger in water does not know how deep it is, and it does not

 know how much flow is going by, or how much water is being stored in a reservoir.  Pressures relate directly to static (standing) water depths, and then equations convert the depths to flows, or to reservoir storage volumes.

How is pressure converted to depth?  It’s an easy calculation – water that is one foot deep has a pressure of 0.4335 psi at the bottom.  So, if your logger measures 1.60 psi, then the calculation to get depth is 1.60 psi / 0.4335 psi per foot = 3.69 feet of depth.

Note that water level loggers can be of two types.  The least expensive are completely submersible, and do not compensate for barometric pressure.  For an idea of  the readings of barometric pressure in a measurement device, a 2 foot deep logger records a pressure of 0.8670 psi.  Atmospheric pressure at sea level is about 14.7 psi, and high in the mountains may be 12.0 psi.  Air pressure is much greater than those measured in ditches.  Usually two of these loggers are used at once, one in the water, and one out of the water measuring only air pressure.  This also eliminates the variability in pressure due to weather changes.

Stilling well in a weir

The second type of data logger compensates for barometric pressure at the same time water pressure is being recorded.  That way, the water and air pressure data sets do not have to be combined before conversion to depths.  These loggers were always more expensive until the Bluetooth Hobo water level logger came along; as of February 2019 I found that it is the least expensive option for a single location.

Now that you can calculate any depth, how do you convert depths to reservoir storage?  That requires an Area-Capacity curve, also known as an Elevation-Storage curve.  The points can be picked off the curve.  For example, in the curve below, a depth of 8.5 feet would correspond to an elevation of 2,802.5 feet, and a reservoir storage volume of 30 acre-feet.

An owner of a reservoir with a capacity over 10 acre-feet must collect monthly storage values.  That’s easily done by hand.  However, a reservoir with a capacity of 50 AF requires weekly measurement; over 200 AF requires daily measurement; and over 1,000 AF requires hourly measurement.  That is really tedious to do by hand.

This is where an Excel spreadsheet can make the task a whole lot easier!  The spreadsheets shown below are just for this.  The first sheet helps translate a graph into a table of elevations and storage volumes.  The second sheet translates collected pressure values into depth and storage values, for as many data points as needed.

For diversion ditches from a stream, how are pressures converted to flows?  The logger is in a stilling well, usually a pipe connected to the inside or outside wall of the weir, flume, or orifice.  It measures pressure, which is easily converted to depths.

As with reservoirs, Excel spreadsheets make the conversion process a whole lot easier.  The sheets below have the rating curve for a suppressed weir, and the second sheet converts pressure to actual water depths over the weir boards.  Even for thousands of hourly readings, the hourly flow volumes are quickly calculated and are ready to send to the Water Board: 

← Back

Thank you for your response. ✨

 

Maybe: Measure H2O Phone App By USBR

A watermaster recently showed me a handy phone app 

to calculate flows through your measurement device.  Measure H2O  works on iphones and android phones.  I have used it for several devices – it’s quick and accurate.  Just fill in the blanks and out pops the flow amount.

However, it is currently not available when I hunt for it in the phone app store.  I don’t know why, I’ll see if I can find out.

Data needs to be collected at your diversion or reservoir at SB 88’s required intervals, whether monthly, weekly, daily or hourly.  This could help you meet the need for monthly or weekly data, if you can visit the site that  often.