WIND EROSION EQUATION (WEQ

WIND EROSION EQUATION (WEQ)
Guidance Document
USE OF MICROSOFT EXCEL SPREADSHEET MODEL


· Purpose - This guidance document will give the basic instructions to use the Wind Erosion Equation (WEQ) Microsoft Excel spreadsheet, computer version, developed by Keep, Sporcic and Nelson. Users will need, as a minimum, a 486 computer; Windows, 95, 98, NT or XP; MS Excel version 7.0 or more recent; 64 meg of RAM or more; a copy of this document; and the WEQ EXCEL Spreadsheet model from the web site below:

· WEQ Data Hyperlinks

WEQ EXCEL Spreadsheet model
http://www.nm.nrcs.usda.gov/technical/TechNotes/agro/ag55.xls

Guidance Document for WEQ EXCEL Spreadsheet Model
http://www.weru.ksu.edu/nrcs/weqguidance.doc
or http://www.nrcs.usda.gov/technical/ecs/agronomy

Access to the National Agronomy Manual: http://www.nrcs.usda.gov/technical/ecs/agronomy.

Wind Parameters for WEQ (Prevailing wind direction; Preponderance: and Erosive Wind Energy (EWE): http://www.weru.ksu.edu/nrcs/

WEQ Wind Parameters by Region and States in EXCEL Format; Populated and maintained by Lorenz Sutherland, La Junta, CO: See instructions in text below.
http://www.weru.ksu.edu/nrcs/

WEQ “E” tables for each combination of C & I factors are on the www browser: http://www.weru.ksu.edu/nrcs
Click on the hypertext for: etable.doc (for MS Word) and etable.wpd (for Word Perfect).

Random Roughness Photos: http://www.nrcs.usda.gov/technical/ECS/agronomy/roughness.html or:
http://www.weru.ksu.edu/nrcs
These photographs are also available in printable format on this site.

C-Factor Map (Electronic):
http://data4.ftw.nrcs.usda.gov/website/



· General – The WEQ management period method EXCEL spreadsheet can be used in states that use the management period method to estimate wind erosion.

To use the spreadsheet model, the user will need to have a good understanding of Part 502 of the National Agronomy Manual (NAM). Also, planners will need to have a basic understanding of the wind erosion equation (WEQ), understand irrigation (where the land is irrigated), and have basic EXCEL spreadsheet skills.

The State Agronomist or Erosion Prediction Specialist will need to set up the wind climate data, crop data and operation (tillage) files (for many states, these have already been populated). Cropping systems developed with the model (WEQ Input Worksheet) can be saved to a file and used as templates for future planning. The light yellow shaded areas are the only “user” data required to run the program. The worksheet is “protected”. This is not because the user is not trustworthy; it is because some of the data should only be changed state wide, and if a formula is lost it can be very difficult to replace.

Saving and using Templates –Cropping systems that are newly developed on the Res Wks can be saved as templates. To do so, save the first six columns of data to an empty (new) workbook. Start by highlighting and COPYING all the cropping system in the light yellow shaded area, beginning with the second row under the Crop and Management Records part of the WEQ Input Worksheet. Open a new workbook and paste this system on a blank sheet. Name this new template workbook, as well as the tab, and now the workbook can also be used to file additional cropping system templates under other tabs. Templates can then be copied from the template workbook sheet and be pasted back into the WEQ Input Worksheet.

· Irrigated Circles - Many center pivot irrigated fields are planted in a circular pattern. To estimate wind erosion on fields planted and/or tilled in a circular pattern, two estimates need to be made. The first estimate should be made after selecting a NS tillage direction and the second should be an EW tillage direction. The average of the two estimates is the correct soil loss value.

· Definitions - The first sheet in the spreadsheet (Instr) has most of the column headings defined and an explanation of the information required for the spreadsheet to run. Please print a blank worksheet (Res Wks tab at the bottom) and the calculation sheet (Calc) before reading and following the Instr sheet. Remember only the shaded cells need data input by the user. Please read the Instr sheet before starting.

· Circular Reference Bug - There is a circular reference bug in the sheet. As you enter data for the first time you will find an error caution coming up and telling you there is a circular formula error in the sheet. DO NOT PANIC. Just close the warning box when it pops up and continue to add the data needed for the run. The error warning will stop when the first harvest is entered.

· Climate database – The WEQ EXCEL Spreadsheet Model requires monthly values of three wind related climate parameters for a given location. These parameter values, found in the “Climate” spreadsheet (tab) of the WEQv8.01 Excel workbook, are (1) prevailing wind erosion direction, (2) preponderance, and (3) wind energy. The wind energy is expressed as the annual cumulative amount on a monthly basis. There are two options for loading the appropriate wind parameters into the WEQ EXCEL Spreadsheet Model. Also there is a limit to the number of climate stations that can be included. Therefore, it is suggested that only data needed in the state where the sheet is used and the adjacent states are added to the State climate database.

q Option #1.

In addition to the WEQ Model Excel workbook, there are four other Excel workbook files that contain the climate parameters for each of the participating regions. These files, which will be provided before and during the training, contain the required wind parameters to run the WEQ EXCEL Spreadsheet Model. The files can be accessed and downloaded from the national WEQ web site. The wind parameter data is aggregated by region. For example the file named EXCEL-WEQ Climate DB_West.xls contains the wind parameter data for each state in the West USDA-NRCS Region.

Persons authorized to load, change, or add to the Climate database will open the WEQ Model Excel workbook, click on the Climate tab, and unprotect the worksheet. If there is existing data not needed by your state, then highlight only the data not needed, including the location names; right click the mouse and select ‘clear contents’. Open the region *.xls file and click on the tab for your state (i.e. Nevada). Select (highlight) the location names and data, then copy and paste to the Climate tab of the WEQ Model Excel workbook. When the task is complete, the sheet must be protected and workbook saved.

q Option #2.

The wind parameter data can also be manually loaded by entering the data from Exhibit 502-7a in the National Agronomy Manual. The table is located at the following web location: http://www.weru.ksu.edu/nrcs/windparm/windparm.pdf

To copy the needed data from the above file, open the WEQ Model file and the windparm.pdf file mentioned above. Locate the windparm data for your state and manually copy this data to the bottom of the climate sheet in the WEQ model. Be sure to get all the data columns. Next locate and highlight all other state’s data that you do not need, and hit the delete key. Once the needed climate locations have been added, and the unwanted states have been removed, sort the entire table on the first column (B). This will take out all the blank rows and size it down to read the needed locations. When the task is complete, the sheet must be protected and workbook saved.

· Adding to the Crops database – When adding a new crop to the Crops database, two types of data are needed, Residue data and Green Growth data. Residue data is entered on one line, and green growth data is entered by 15 day growth intervals. The first growth period must be adjusted for emergence. If it takes 10 days to emerge then there will be only 5 days of growth the first period. The growth periods express an average dry matter (lbs/ac) accumulation for the period. The growth periods can be extended as long as additional dry matter is added to the crop. Residue is expressed as dry matter (lbs/ac) left above ground after the harvest operation.

Persons authorized to change or add to the Crops database will click on the Crop tab and unprotect the sheet, enter data as instructed below, sort the data table, and protect the sheet again so that data will not be accidentally lost. Passwording the sheet is required. Data entered in this table must be similar to surrounding states.

Residue data needed is: an estimated yield, the unit wt. per acre, the residue in lbs/ac at harvest, the residue/unit wt. (lbs/ac divided by the unit wt), the cover residue table (either Corn, S Grain, or Cotton), and the flat small grain equivalent chart to be used (See Exhibit 502-10 pg 502-61 of the NAM). Green growth data needed is: a growth curve table, which is dry matter (lbs/ac) accumulative by 15 day intervals, the flat small grain equivalent chart to be used (See Exhibit 502-10 pg 502-61 of the NAM), the green growth equation for the selected chart representing rows “perpendicular” to the prevailing wind and the green grow equation for the selected chart for represented rows “parallel” to the prevailing wind. Much of the green growth data and residue data has been developed by individual States. Green growth table names are tied to regressed curve equations and can be copied to new cells (crops) as needed.

The crop name, without a number following the name, is used to call in residue values. The crop name, with numbers after the name, is used to call in green growth values. Example: a line with the name Corn, grain is a line of residue values, a line with the name Corn 15 indicates this is the first 15 day green growth period for corn.


· Adding to the Operations database- Residue reducing tillage operations vary by speed, soil type, depth, spacing, amount of residue present, type of residue (fragile or non-fragile), and soil moisture. It is not assumed that the listed operations will reflect all the situations where this model will be used. There are four parameters needed for each operation, % residue remaining (mass), random roughness (RR) created by the operation, ridge height created (inches), and ridge spacing (inches). The N and F listed in the name indicates Non-fragile or Fragile residue. In the old NAM, 2nd edition, Amendment 5, 1993, Part 503 subpart E pages 503-13 and 503-14, tables 1 and 2 list the N and F crops.

If it is necessary to add or change the operations table, authorized persons may change or add to the Operations in the database. The new operations data will be added to the bottom of the worksheet. Do not change the names of the brown colored operations since they are used in formulas and if changed, will cause the sheet to give incorrect answers. Start the by clicking on the “Oper” tab at the bottom of the workbook. Click tools, and unprotect to unlock the sheet to add or change the data. Enter or change the data as needed and protect and password as instructed above.

Step by Step

This is a step by step process to show how to use the program. Start the step by step process after reading the Instr sheet (the tab at the bottom of the WEQ Excel spreadsheet).

Step 1 - Fill in the Producer, Planner, Crop Rotation, Location (Farm number or Sec., Town., Range), Tract, and Field boxes.

Step 2 - Use the drop down menu to select the Climate Data Station. When the climate data station is selected, the model automatically pulls the data from the Climate sheet (see the tab at the bottom of the worksheet).

Step 3 - Enter the Field Width in feet (short side of field), Tillage Direct. (EW or NS, drop down), Length/Width Ratio (drop down), Field Direction (EW or NS, drop down), and Adjusted Soil “I”, which is the assigned I value for the soil texture plus the adjustment for knolls (drop down). Fill in the C Value (in whole numbers. Divide the isobar interval only once from the C factor map). Insert yes or no for Irrigation (Y/N, drop down). When Irrigation is checked yes, this automatically places the I factor into the next less wind erodible soil group. Therefore when you have an I value based on sieving and want to take credit for irrigation induced non-erodible wet days, you need to change your “adjusted sieved I” by one higher wind erodibility group before checking yes in the irrigation block.

Step 4 - Determine the Wind Erodibility Group (WEG) from the FOTG soil survey, and fill in the number (1-7 or 4L, drop down).

Step 5 - Place 1/1/xxxx on the first line of worksheet (same line as “Start Rotation”). In the first column of line 13, put in the date 1/2/xxxx. Next place the cursor in the Crop column next to the date, left click in the box to activate the pull down and select the previous crop harvested (select from the list). Move to the Operation column. Start the first management period with an Over winter loss operation. In cell C13, click the pull-down and select the Over winter loss, fragile (F) or non-fragile (N) operation (see NAM Part 503 Subpart E, table 1 & 2, for definition), repeat these steps with correct dates until all tillage operations, planting operations, and harvests are completed. As you select the planting operation change the crop to the new crop being planted.

Step 6 - The next date after planting will be the end of the first 15-day growth period. The date can be entered as a formula. In the blank date column A, type =, then point and click on the cell just above and type +15, then hit the return key. This will enter a formula that tells the computer, to type the date above and add 15 days. All growth periods are 15 days except winter wheat or other winter crops, which have a 60-day growth period over winter (see the Crop table for details). The 15-day date formula can be copied down for the number of growth periods for the crop planted. Next, in the Crop column click and select the growing crop name with a number (15 to 75 days after planting) next to it. Continue to select down the column a series of growth periods.

Example of a 2nd way to enter crop during growth: Bean 15, Bean 30, Bean 45, Bean 60, and Bean 75 can be copied and moved at the same time to the Crop Name column of the Res Wks. This must be done using the paste special function. Select the Crop Tab and find the correct series of grow names, highlight them and copy them. Change back to the Res Wks sheet, place the cursor in the first cell under Crop (column B) where crop growth begins, right click and click on paste special, then select the radio button for values under the paste section, and click OK.

In the Operation column enter (click and select) “Grow” for all the growth periods. “Grow” can be pasted in the first cell for “Grow” and then copied down the sheet (Res Wks) as needed. All growth series of data can be copy from the tables in groups and paste special used to paste only the values to the Res Wks. If you copy and paste normal you will lose the formatting in the cells.

Step 7 - Enter the Harvest date, the harvested crop name (without a number extension), and the word “Harvest” for above ground crops or “Harvest, root crop” for root crops, in the operation column.

Step 8 - Enter the date of any post harvest tillage, crop name, of the crop just harvested, and the name of the operation. Repeat step 5, 6, 7, and 8 for any additional crops in the rotation. 100 management periods can be used in each calculation. If more are needed try removing lines where there is no erosion. An example would be to reduce the number of operations of “Grow” to just what is needed to take erosion to zero for the rest of crop growth period.

Step 9 - End the run with a 12/31/xxxx date, last harvested crop name, and the End Rotation operation.

Step 10 - Enter the number of irrigations for the periods listed. (This is NOT the cumulative number of irrigations or “Irrigation Days”).

Step 11 – In the Flat Res. column on each line of the run enter 0 when residue is 100% standing (no flat residue), and 100 when all the residue is flat (as in heavy inversion tillage). Example, if 60% of the residue is standing after a tillage, then enter 40 or 0.4 and hit the return key. The number will be in percent.

Step 12 - Finally, adjust any yield values that are different than the default yields in column G-H. You can change the yield by 50% up or down by using the drop down in the Yield Adjustment column (F).

Example

· Iam Windy farms, tract 123 on an irrigated circle (field 1) of continuous grain corn, where the soil has an I of 56. The circle field has a diameter of 2640 ft, and is near Clovis, NM. The C is 100. The grower tills and/or plants approximately perpendicular to the damaging winds from the west during the spring critical period. The field is farmed north and south. Iam plants corn on 4/15 and harvests 10/15. The stalks are disked with an offset disk and packer on 11/1. In the spring the field is disked and packed again on 3/15. On 4/1 the circle is moldboard plowed, conventionally, and packed. Then, on 4/10 a seedbed maker is used to set up the field for the corn planter. The corn is cultivated on 5/15. His average yield has been 200 bushels/acre.

Step 1 - Fill in: Iam Windy, MAS, Corn, grain, Sec 10 T80 R45, tract 123, and field 1 on the WEQ Input Worksheet.

Step 2 - Select the climate data station of Clovis, NM from the pull down list.

Step 3 - Put 2640 feet in the field width box, NS for tillage direction, length width ratio is 1.0, field direction is EW, the Adjusted soil I factor is 56 (keep in mind that the selection of yes in the irrigation box automatically adjusts the I factor value by one favorable group), put 100 in the Site “C” Value box, and Irrigation is Y (yes).

Step 4 - WEG is 5.

Step 5 - Enter 1/1/2000 in the first line (cell A12) of the table. Enter 1/2/2000 in the second line (cell A 13) and select (cell B13), enter Corn, grain, high yield from the Crop table drop-down list and select Over winter loss N for the operation . Enter the first tillage on 3/15/2000, copy Corn, grain, high yield from the cell above, select Disk, offset, heavy N from the Oper drop down list. Do the same for the Packer, roller on the same date. Enter 4/1/2000 and put in the Plow, moldboard, conventional and Packer, roller. Enter 4/10/2000 and copy Corn, grain, high yield. There is no seedbed maker operation, but a Chisel-disk-harrow-packer (comb) N is close, so use it. Enter 4/15/2000, Corn, grain, high yield and Planter, DD opener, 30 in sp N.

Step 6 - Copy the 4/15 date and add 15 days to the formula. Copy or select Corn, grain 15 through Corn, grain 75 from the Crop table and “paste special” in the crop column. Type or select Grow in the Operation column and copy down to match the Crop column. Copy down the Operation Date column to match the growth cells. Note that there is a cultivation on the second growth date. Copy the six columns and four rows of data from 5/15 down one line to add the cultivation. On the second 5/15 date, which is the second Corn, grain 30 line, copy and paste the Cultivator, rowcrop, 3 in ridge operation in the Operation column.

Step 7 - Enter the harvest date 10/15/2000, copy down the crop Corn, grain, high yield and type or select the word Harvest.

Step 8 - Add the post harvest tillage on 11/1/2000, which is a Disk, offset, heavy N and then a Packer, roller. Copy both tillage operations from the cells above.

Step 9 - End the rotation year by entering 12/31/2000, copy Corn, grain, high yield and copy or select the End Rotation operation.

Step 10 - Estimate the number of irrigations needed for each growth period or use Iam’s records for each period. The number of Irrigations is determined by the number of times that irrigation water will wet the soil surface in a given management period. The attached example may have too many irrigations in some management periods.

Step 11 - Go down the Flat Res column and estimate the percent flat residue. Start with the Harvest Operation. Estimate about 50% of the residue is flat after harvest and 100% is flat after the fall tillage. Enter 50 in the flat Res column after harvest, and 100 in the management period for fall packing. Note only the 1st period after harvest has standing residue.

Step 12 – Select .5 on the drop down in column F, Yield Adjustment. This increases the 135 bu/ac to 200 bu/ac.

See the attached example: (Res Wks sheet and Calc sheet).