Troubleshooting Calculators

Overview:
Field360 Studio has specific requirements for calculators that they must possess in order for them to function properly. This article will help you understand the components of calculators and some common mistakes that will cause user-created calculators to fail. 

Requirements:

• Access to Field360 Studio is required.
• Access to Microsoft Excel.

Answer:

If you want to build your own calculator, the best place to start would be with the template that already exists within Field360 Studio.

Click Setup and then click Calculators.

On Calculator Editor let’s click the button. 

Expand the How To section. Then select Building a Spreadsheet Calculator.

Now click the link for the Calculator Template.

This will prompt you to either open or save the file.

Note: Calculators must be in an .xlsx or .xls file type.

Calculator Overview:

There are three parts that make up a calculator:

Conditionals-  are used in Excel are used for IF functions. They will return one value if the condition is true or another value if the condition is false. Examples of conditionals that might be used in a nutrient calculator are crops or sequence. Your calculator is not required to have a conditional, but if you decide not to use a conditional, be sure that you delete the whole named range. If you don’t, it will cause an error.

Variables- are, simply put, a number we don’t yet know. The Variables named range must have three columns: a variable name, its value in the calculation (updated automatically by the calculator for each row of the data set) and a unit-of-measure. A fourth column is optional. It identifies what data use the variable is in.

Results- are required. You could have multiple results on your calculator. Results coming out of an Excel spreadsheet or reclassification table need to be in nutrient form, such as K_Rec and P_Rec not Potash and DAP, respectively. The conversion from nutrient to product is accomplished in the Nutrient Formulator. The Results named range has three required columns and two optional columns. The required columns are: Result Name, Result value, Result Unit. The optional columns identify date use and data type.

Common Reasons for Failure

• Naming and Scope are a large contributor of failure for people who already know how to write formulas. Incorrect naming standard, having the scope incorrectly set, or having invalid formulas are very common errors.
  • Both naming standards and scope can be validated in Excel by navigating to Formulas ^ Name Manager.
  • Selecting Name will allow you to sort these alphabetically. From here verify that all names are spelled correctly, that capitalization is consistent and correct, and that you used the proper naming standard as these will cause an error on import.
  • The scope for name ranges must always be set to the worksheet and never the workbook. Scope can also be selected for sorting and reviewing.
• Formulas can be evaluated by plugging in values to the worksheet to make sure that the results of those calculations are similar to what is expected. Excel can also be utilized to help validate your formulas. To do this, navigate to Formulas ^ Evaluate Formulas ^ Evaluate. This tool will walk through your formula. You will need to continue clicking Evaluate for each step in your calculator. This can help you identify errors in your order of operations.
• Manual inputs for variables are often a source of failure. Text values placed in a Fertility Script must match exactly the text values from the calculator. Below is a list of the of the variable inputs for the calculators.

Calculator Manual Inputs

Custom Yield Target or Harvest
Extraction Method
    BrayP1
    Olsen
    M3-ICP
    M3-Col

Build years
    (numeric value - 1,2,3 or 4)

P Optimum Low
    (numeric value)

P Optimum High
    (numeric value)

K Optimum Low
    (numeric value)

K Optimum High
    (numeric value)

Custom Yield Target or Harvest
Target pH
    (numeric value)

Tri-State Yield Target or Harvest
Extraction Method
    BrayP1
    Olsen
    M3-ICP
    M3-COL
    Tri-State Lime
    None

Illinois Yield Target or Harvest
Extraction Method
    BrayP1
    Olsen
    M3-ICP
    M3-COl
    Supplying Power
    Low
    Medium
    High

Illinois Lime
    None

Iowa Yield Target or Harvest
Extraction Method
    BrayP1
    Olsen
    M3-ICP
    M3-COL

Iowa Lime
S.A.A. (Soil Association Areas - west part of state 6.0 - east part of state 6.5 - Alfalfa 6.9
    East
    West
    Alfalfa

Depth
(numeric value)