Mapping Spreadsheet Calculator

This spreadsheet calculator requires Microsoft Excel™ version 2007 or newer.

The syntax of an Excel Spreadsheet for the Spreadsheet calculator is fairly simple but there are specific naming or labeling rules that are important to understand.

Multiple Worksheets

There must be a worksheet for every permutation and combination of the conditionals.  For example, if there are five crops, there will need to be five worksheets.   As in the example above, one workbook can be created for each nutrient and that workbook can contain several worksheets - one for each different crop that might be needed.

Conditionals

The block in blue is the Conditionals named range.   It contains a row for every conditional that is defined for the workbook. The first column (Column B) is the name of the conditional and the second column (Column C) is the value of the conditional for this worksheet.   These become the RequiredConditional properties of a SpreadsheetCalculatorOption.  The Spreadsheet.SelectOption method will search each worksheet to find the one that has RequiredConditional values that match the ActualConditional values that have been set on the SpreadsheetCalculator class.  The individual conditionals do not need to be named ranges, but the blue box must be explicitly named Conditionals and must be a sheet-specific name, not a workbook-specific name.

Variables

The Variables named range is shown in pink.  It must have three columns:  a variable name, its value in the calculation (updated automatically by the calculator for each row of the dataset) and a unit-of-measure.   For now, the input cells (where the 110, 12, and 150 are) should be single cell named ranges, with the name being the variable name (K, CEC, and Yield).   This will change as an absolute requirement but will always be a recommended practice.

The Units fields have specific requirements.  Acre, for example, must be spelled out as acre and not the common abbreviation, ac.   pH is another in that its range must be named pHw to work properly.  

Acceptable units for these variables are:  ppm, lbs per acre (st), meq/100g, tons/acre, bu/acre, and none.  No other unit types or names will work.  Select the one to use for the given variable.   Below is a table of variables and their syntax:

Results

The Results named range is shown in green.   At this time, it has three required columns:   result name, result value, and result unit.    The value cell (the 110) should:

• Contain the formula used to calculate the value from the variables.
• Be explicitly typed as text, numeric, etc.
• If numeric, have a format defined that shows the number of decimal places, etc.

In the current implementation, the formula should use name range references for the variables, rather than cell references.   For example, IF (K < 151… rather than IF (C6 < 151…   This will change in a future build, but will always be a recommended practice.

The Results area has specific cell Range names required for the nutrients used in the calculator.  The units for the nutrient recommendations can be either lbs/acre or tons/acre. UD_Rec in the last row stands for the one User-Defined nutrient allowed in Agvance.   The table below displays those Range names:

Building a Spreadsheet

Label the worksheet tab by double-clicking the current name (ex. Sheet 1) and typing the equation name.

1. In the first cell, type Conditionals:
2. Type the conditionals into the rows below using one conditional per row.
   • For example:   Crop
3. In the adjacent column, enter the conditional values.
   • Example:   Corn
4. Select the cells that make up the conditional labels and values (B2:C2 in the example below).
   
5. Choose the Formulas menu option and select Define Name.
   • If using Excel version previous to Office 2007, go to Insert | Name | Define
6. In the New Name dialog:
   
   • Name = Conditionals
   • Scope = Worksheet name (not “Workbook”)  
   • Comments = None
   • Refers to = Default
7. Select OK.
8. Select the first cell in a row below the Conditionals section.
9. Type Variables:
10. Type the variables into the rows below using one row per variable.
    • For example:   K, CEC, Yield
11. Leave the adjacent column empty for now as this will be where the variable value is entered.
12. In the next column, enter the unit-of-measure for each variable.
    • For example:   ppm, meq/100g, bu/acre
13. Select the value cell adjacent to the variable label.
    
14. Choose the Formulas menu option and select Define Names.
15. In the New Name dialog:
    •  Name = variable name (ex. K)
    • Scope = Worksheet name (not “Workbook)
    • Comments = None
    • Refers to = Default
16. Select OK.
17. Upon selecting the variable value cell, the name will be visible in the Name field of Excel instead of the cell name like C8.
18. Repeat steps 14-17 for the remaining variables.
19. Select each/all variable value cell(s), right-click, and choose Format Cells.
20. Enter the Category and other information.  
    • Example:  For numeric data select, Number, two decimals.
21. Select the cells that make up the variable labels and values.
    
22. Select the Formulas menu option and choose Define Names.
23. In the New Name dialog:
    
    •  Name = Variables
    • Scope = Worksheet name (not “Workbook)
    • Comments = None
    • Refers to = Default
24. Select OK.
25. Select the first cell in a row below the Variables section.
26. Type Results:
27. Type the results label(s) into the rows below using one row per result.
    • For example:   K_Rec
28. Leave the adjacent column empty for now as this will be where the results value equation is entered.
29. In the next column, enter the result unit.
    • For example:   lbs/acre
30. Select on the value cell adjacent to the results label.
    
31. Select the Formulas menu option and choose Define Names.
32. In the New Name dialog: 
    •  Name = result name (ex. K_rec)
    • Scope = Worksheet name (not “Workbook)
    • Comments = None
    • Refers to = Default
33. Select OK.
34. Repeat steps 28-34 for each additional Result.
35. Select the result value cell(s), right-click, and choose Format Cells.
36. Enter the Category and other information.  
    • Example:   For numeric data select, Number, zero decimals.
37. Select the cells making up the results labels and values.
    
38. Select the Formulas menu option and choose Define Names.
39. In the New Name dialog:
    
    •  Name = Results
    • Scope = Worksheet name (not “Workbook)
    • Comments = None
    • Refers to = Default
40. Select OK.
41. Choose the result value cell and enter the equation in the Formula Bar.
    
42. Use the variable names (ex. CEC), not cell names like C6 in the equation.
43. Repeat step 42 for additional results.
44. Test the equation by entering values for the variables.
45. Repeat all steps for each additional worksheet.
46. Save the workbook.