Functions and formulas

The keydata section of the readings screen can be used to manually record and display data collected in the field. For example, plant height, fruit size, and pan evaporation could all be recorded and displayed. But in addition, functions (or formulas) can also be entered that will calculate and display a result based on other data.

Functions can be used either as keydata items in individual sites, or as global functions.

Keydata Functions

A keydata function will only calculate during an update of the readings screen, but the data will then be recorded and displayed. Keydata functions are entered as keydata items on SiteEdit. If a keydata function is entered or changed users must make sure the whole of the spreadsheet is updated, and not just the latest readings.

Global Functions

Global functions are the same as keydata functions, but can apply to all readings screens (below the keydata section), time graphs and readings reports.

For example, if a user wants to include data from COTTON.DWU on every time graph, then the function +COTTON.DWU should be entered as a global function. A global function will calculate the result as the data is displayed on the screen, or is printed or graphed. The results are not recorded. The advantage of a global function is that it need be entered once only, but can be applied to all readings screens.

Global functions are entered on UtilitiesGlobals, and a maximum of sixteen (16) can be used. They are saved in a globals file, entered at UtilitiesUpdateOptions. A file of global function examples called COTTON.GLB is supplied with the software.


A function can have only one (1) or two (2) terms. For example, if a keydata item called Pan had been used to manually record pan evaporation data, then:

+ProbeDWU/Pan will divide Probe DWU by pan evaporation

The software decides that a keydata item is a function (rather than user entered data), by looking at the first character. If it is a +,- or @, then it assumes it is a function and attempts to work out what it means.

If it is + or -, it looks for the next +,-,* or /, checks for valid keydata items, and calculates the result.

If it is an @, it checks for a valid @function name, checks inside the brackets for a valid keydata item(s), and calculates the result.

Checking for valid keydata items

Functions can be entered in UPPER or lower case. The software looks at items in the following sequence:

1 Searches down the left hand column of the current site readings screen, starting with Date, Time, 20cm ... ProbeDWU,Est DWU,.etc. If it finds a match, it knows that the value from that row of the spreadsheet is required. The actual value used will be from the corresponding column, or date, in the spreadsheet.

2 It then checks to see if a site constant.has been entered These are:

PDATE planting date from SiteEdit
AREA area from SiteEdit
GDWU either Global DWU, or from SiteEdit/Variety (see site standard DWU below)
FULL Full point from readings screen
REFILL Refill point from readings screen

These reserved words will return the corresponding value. For example:

-Refill+0-70cm water available in Root Zone 1

4 It then checks to see if a number has been entered . For example:

+DEFICIT/2.54 deficit divided by 2.54

3 It then checks for a file name, by looking for the . before the file name extension. It assumes that it is a file in DWU format, and will load that file and calculate the DWU on the corresponding date. The DWU file is day-of-year (1st June is day 182), so the date is converted to day-of-year.

+COTTON.DWU dwu from this file, on this date


The following @functions, with examples, are available.

@SUM(Bolls..Squares) add (vertically) all values in the range from the Bolls row to the Squares row.
@AVG(20cm..60cm) average VSW from 20-60 cm
@COUNT(50cm..120cm) count the number of valid data items
@RATE(20cm) the rate of change of soil moisture at the 20 cm level.(the differential of the values in the 20cm row)
@CUM(ProbeDWU) the cumulative water use of the crop (the integral against time of the values in the ProbeDWU row, starting at the planting date)
@SM(ProbeDWU,0.1) Smoothing of ProbeDWU, using a digital filter factor of 0.1. By changing the factor between 0-1, different degrees of smoothing will be obtained.

Data Format Codes

On the UtilitiesGlobals screen, and on SiteEdit, users are asked to enter a data format code. This is a numeric code that tells the software how to format the display - it does not affect the accuracy of the actual calculated result. It is the equivalent of formatting in a typical spreadsheet, but additionally will format to different units as specified with UtilitiesPreferences. The calculated value is multiplied by a scaling factor so that the user sees, for example, ins/ft rather than %VSW. Keydata function format codes are entered at the end of the function (following a :), but a global function code is entered on UtilitiesGlobals. A keydata function will default to format code 9 (general format) if a code is not entered.

For example, a keydata item:

+Date-Pdate:0 calculate days-since-planting, with display to the nearest day
Code Data Format
0 0 decimal places
1 1 decimal place
2-7 2-7 decimal places
9 numeric, but general format (display to any accuracy)
10 DWU (mm/day etc)
11 Amount (Rainfall, mm etc)
12 Area (Ha etc)
13 Volume (Irrigation water applied, Ml etc)
14 Moisture (VSW% etc)
15 Root Zone (0-70cm etc)
16 Date (d/m/y etc)
17 Time
18 Depth (50 cm etc)

Where etc indicates that the displayed value will be depend on the preferences settings.

Site Standard DWU

The Global DWU (GDWU) is calculated from a file set in UtilitiesUpdateoptions. However, individual sites can have their own, local, GDWU. If a DWU file name is entered at SiteEdit Crop/Variety:, then the GDWU for that site will be calculated from the data in this file instead of the GlobalDWU. For example:

Crop/variety: GRAPE1.DWU

will calculate the GDWU for this site from the DWU data in GRAPE1.DWU.

In later versions of the software this value will be called Site Standard DWU, but in addition it will become a full function, allowing for field compaction affecting standard DWU data.

Time Graphs

The horizontal axis of the time graph is normally set to DATE. However, it can also be set to any other keydata item. For example, if plant height and number of nodes had been recorded, then:

Horizontal axis: HEIGHT

will plot, if nodes is the selected item, a cross-correlation graph showing the relationship between plant height and number of nodes.

Another example (if a global function of +DATE-PDATE had been entered, to calculate days-since-planting) would be:

Horizontal Axis: +DATE-PDATE

which will plot horizontally days-since-planting, rather than actual date. If an overlaid time graph report is generated, with the horizontal axis set to +DATE-PDATE, then a time graph will be drawn that has data from different sites plotted on the same graph, but the horizontal axis will be days-since-planting, rather than the actual date. The graphs will therefore be synchronised to days-since-planting, irrespective of the actual planting date, or even if data is being compared from one year to the next..

Examples of Functions

Note that the formatting code after the : is only required for keydata functions - it needs to be entered separately for a global function.

@CUM(ProbeDWU) cumulative water use
+ProbeDWU copy ProbeDWU, possibly for later adjustments
+cotton.dwu:10 DWU from file, in preferences units
+Date-Pdate:0 days-since-planting, to nearest day
+ProbeDWU/Pan.DWU calculate the ratio of Probe DWU and pan evaporation. This ratio gives an indication of the relative water use efficiency of the crop.
@RATE(diameter) calculate the rate of growth of the crop. If this is graphed against 0-70cm soil water content, growers can display the relationship between crop photo-synthesis and water supply.
@CUM(ProbeDWU) calculate the total water use of the crop to date. Maize growers are finding that this function, when compared with cumulative evaporation pan data, is giving a good predictor of total crop yield.
+ProbeDWU/COTTON.DWU calculate the ratio between the DWU of the current crop, and the long term average DWU of cotton, from the file COTTON.DWU.

Function restrictions

The software can get confused when working out what the formula means.

Consider, for example:


>amount available in Root Zone 1

However, the 0-70cm is interpreted to mean 0 subtract 70 , and the function will not work.

But, because the software analyses from left to right, the following function will work:


amount available in Root Zone 1

There are some additional problems with global functions, caused because the values are being calculated each time the data is displayed. The problems occur when using @CUM() or @RATE() that refer to other global function results (and not to data in the actual readings screen), because the values from previous dates or other rows are required to calculate these values.

This global function will not work:

@CUM(ProbeDWU) total water used by crop

because the accumulating total water used is not available to the @CUM() global function.

Global function @CUM() cannot be used, and @RATE() will work only if it refers to an item on the readings screen, and not an item on the global screen itself.

Future Developments

The functions and formulas have been developed in close consultation with users of the software. Some of the problems and future developments outlined above are being addressed, and will be included in future releases of the software. Research Services New England would be be pleased to hear about any errors, faults or suggestions for future developments to keydata or global functions