1 / 52

The Development of the JAES LVI Calculator Version 3, rev 1a

The Development of the JAES LVI Calculator Version 3, rev 1a. Concept, Development, Implementation, and Operation of the LVI Calculator By Jeff Darr (407) 836-1439 jeffrey.darr@ocfl.net Orange → County Environmental Protection Division. Constructs for JAES LVI Calculator.

makaio
Download Presentation

The Development of the JAES LVI Calculator Version 3, rev 1a

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. The Development of the JAES LVI Calculator Version 3, rev 1a Concept, Development, Implementation, and Operation of the LVI Calculator By Jeff Darr (407) 836-1439 jeffrey.darr@ocfl.net Orange→County Environmental Protection Division

  2. Constructs for JAES LVI Calculator • Fore, L. 2007. Assessing the Biological Condition of Florida Lakes: Development of the Lake Vegetation Index (LVI) • 2008 FDEP SOP LT 7500-5, 5.1- 5.8 Lake Vegetation Index Determination – Index Calculation • Common Software (Accessibility) MS Excel® • Simple and Familiar Data Entry Process (Field Data Sheet FD 9000-7)

  3. New Features of Version 3 • Single page Taxa list: Version 2 had two columns of taxa and data, which sometimes made it difficult to find taxa. Version 3 has a single column of taxa and data. • Automatic Taxa insertion: Version 2 required you to look up the taxa in the SOP, transcribe the CofC values, and determine what nativity status it was in order to choose the proper calculation formulae. Then you had to copy and replicate those formulae across all sample sections. Version 3 only requires you to “Click” on the new taxa on the included worksheet, then press a button. Version 3 will automatically insert the new taxa in the appropriate row, and properly insert all the correct formulae, automatically across all sample sections. • Worksheet Protection: Version 2 did not protect the calculation cells. Sometimes users would accidentally erase rows or columns that were calculating the score. Version 3 worksheet protects the calculation and result cells. Users can only edit the cells that require data entry.

  4. Data Sheet FD 9000-7

  5. JAES LVI Calculator Worksheet

  6. JAES LVI All_Taxa Worksheet This is the Second Worksheet Included with the JAES Calculator. It’s basically LT7500-1 with a column of calculations to figure out formulas. Notice the Automatic “Add” Button on THIS sheet. This will be covered in detail later.

  7. Closer Look Calculator Sheet – Upper Left

  8. Closer Look Calculator Sheet – Lower Left This is the only Entry the User will make down here: “North” or “South”

  9. Top Right of Form – Lake Sections User Enters the 4 Sample sections from the Lake. These 4 numbers will distribute themselves throughout the form automatically

  10. Top Right of Form – Lake Sections This Button will allow you to delete rows. First, you select the Taxon under column A, then you press this button. This will be covered in detail later.

  11. Enter Data in Section Column User will enter a “1” for present, or a “C” for Co-dominant, or a “D” for dominant, for each section.

  12. Scores are automatically calculated

  13. Adding New Taxa

  14. Adding Taxon Data • Aeschynomene indica • At some point, the user will need to enter taxa that are not listed on the calculator. This calculator has two worksheets included in it. The first is the calculator worksheet, the second is the complete list of taxa from the FDEP SOP Table LT7500-1. • In our example here, the user wants to add Aeschynomene indica to the calculater worksheet. The next screens will show the steps on how to do this.

  15. Adding Taxa: Step 1 User Clicks on the “All_Taxa” worksheet tab to reveal all the taxa from which to select.

  16. Adding Taxa: Step 2 User then finds the desired taxon under column A, and “Clicks” on the taxon. (Just in Column A -- not the whole row)

  17. Adding Taxa: Step 3 User then “Clicks” on “Add Row to Calculator” button.

  18. Adding Taxa: Verifying Add User will verify the Add by clicking YES. If taxon is incorrect, then user can click NO.

  19. Adding Taxa: Complete When you click the “Add” button, you will notice a lot of scrolling of the worksheets happening; this is Normal. When finished, you will notice the new taxon inserted alphabetically in the calculator. It will automatically have the correct formulae embedded into its row. You are finished with the Add.

  20. Deleting Taxa

  21. Deleting Taxa within a Protected Document The JAES LVI Calculator worksheet is protected by password to keep unauthorized changes from happening to cells that calculate the score. Consequently, the user cannot delete any rows within the worksheet without using the delete button included in the upper right corner of the calculator worksheet. If we wanted to delete a row that we don’t want anymore in the calculator, we can use the delete button, and delete as many rows as we want, one at a time. The following slides will show the easy 2 step process and verification to delete a row.

  22. Deleting Taxa (Rows): Step 1 User finds the desired taxon (row) to delete and then “Clicks” on the taxon only. (Column A – not whole row)

  23. Deleting Taxa (Rows): Step 2 User then “Clicks” on the “Delete Row” button in the upper right corner of the worksheet.

  24. Deleting Taxa (Rows): Verify Delete User then will Verify the Delete, by pressing Yes. If row or taxon is incorrect, then user can cancel delete by pressing NO.

  25. Deleting Taxa (Rows): Complete User then can inspect that the taxon (row) has been deleted. User is now finished with Delete.

  26. Program Development • Embedded formulae • Index Calculation • Metric Calculation • Disclaimer • References (This is all Technical and Legal Stuff)

  27. Embedded Formulae

  28. Embedded formulae – Dominance dI = Dominant Indicator =IF(ISTEXT(C10),1,0) Reflects “1” if entry is a letter

  29. Embedded formulae – Native Indicator Nat = Native Indicator =IF(NOT(ISBLANK(C12)),1,0) Reflects “1” if entry is Native

  30. Embedded formulae – FLEPPC Invasive Indicator Inv = Invasive Indicator =IF(NOT(ISBLANK(C11)),1,0) Reflects “1” if entry is FLEPPC Invasive Exotic

  31. Embedded formulae – Sensitivity Indicator Sens = Sensitivity Indicator =IF(((NOT(ISBLANK(C128))*AND($B128>=7))),1,0)Reflects “1” if entry is Sensitive and present

  32. Embedded formulae – Dominance Calculator Dom = Dominance Calculator =IF(C129="D",$B129,IF(C129="C",$B129/2,0)) Reflects CofC value if dominant, CofC / 2 if Co-dominant

  33. Index Calculation

  34. Index Calculation – Nat/Inv/Sens/Dom

  35. Percent Native – FDEP SOP LT7500-5.1 5.1 Calculate the % native taxa in a single sampling unit (pie slice) by dividing the number of native taxa by the total number of taxa in that sampling unit. Nativity status is determined by the Institute for Systematic Botany and is listed in Table 7500-1. =IF(AE$127 = 0, 0,((AG124+E122)/AE$127)*100) N Col 1 + N Col 2 / Section Total * 100 Divide by zero Protection

  36. Percent FLEPPC Invasive – FDEP SOP LT7500-5.2 5.2 Calculate the % FLEPPC invasive taxa in a single sampling unit (pie slice) by adding the number of FLEPPC Category I and II taxa and then dividing by the total number of taxa in that sampling unit. Category I and II lists are maintained by FLEPPC, the Florida Exotic Pest Plant Council, and are listed in Table 7500-1. =IF(AE$127 = 0, 0, ((AH124+F122)/AE$127)*100) I Col 1 + I Col 2 / Section Total * 100 Divide by zero Protection

  37. Percent Sensitive – FDEP SOP LT7500-5.3 5.3 Calculate the % sensitive taxa in a single sampling unit by summing the number of taxa with a C of C (Coefficient of Conservatism) score >= 7 and then dividing by the total number of taxa in that sampling unit. Refer to Table 7500-1 for a list of C of C scores. =IF(AE$127 = 0, 0, ((AI124+G122)/AE$127)*100) S Col 1 + S Col 2 / Section Total * 100 Divide by zero Protection

  38. C of C Dominance – FDEP SOP LT7500-5.4 5.4 Calculate the Dominant C of C for a single sampling unit by inserting the C of C for the dominant taxon or the average C of C for the 2 co-dominant taxa. If no dominant taxon was identified, this metric will be null, not zero. Refer to Table 7500-1 for a list of C of C scores. =(AJ124+H122) CofC Col 1 + CofC Col 2

  39. Missing C of C Scores If a taxon does not have a C of C score, then it just will not be considered for the % Sensitive metric or have its C of C used for the dominant metric. It is very rare that a dominant taxa will not have an assigned C of C score.

  40. Metric Calculations

  41. Metric Scoring – FDEP SOP LT7500-5.5

  42. 5.5 Metric scoring rules derived from the 5th and 95th percentiles using the most recent visit from each lake including lakes from the original development data set. Metric scores less than 0 are set equal to 0; scores greater than 1 are set equal to 1.

  43. NORTH SOUTH (Fore L.S., 2007 pp 97)

  44. Closer Look…

  45. Scoring Formulae Native=IF(AG128-54>44,1,IF(AG128<54,0,(AG128-54)/44)) Invasive=IF((AH129>36),0,1-(AH129/36)) Sensitive=IF(AC140="South", IF(AI130>24,1,AI130/24), IF(AI130>37,1,AI130/37)) C of C=IF(AC140="South", IF(AJ131>5.8,1,AJ131/5.8), IF(AJ131>7.2,1,AJ131/7.2))

  46. Over/Under flow protection > 1 < 0 Native=IF(AG128-54>44,1,IF(AG128<54,0,(AG128-54)/44)) Invasive=IF((AH129>36),0,1-(AH129/36)) Sensitive=IF(AC140="South", IF(AI130>24,1,AI130/24), IF(AI130>37,1,AI130/37)) C of C=IF(AC140="South", IF(AJ131>5.8,1,AJ131/5.8), IF(AJ131>7.2,1,AJ131/7.2))

  47. Metric Scoring – FDEP SOP LT7500-5.6,7

  48. Averaging Results Based on Existence of Dominance (N + I + S + D) ÷ 3 or 4 5.6 Average the results for the metrics within an individual sampling unit and multiply by 100. Note that you divide by 3 instead of 4 if the dominant (or co-dominant) C of C was not available. The result of this calculation must be between 0 and 100. 5.7 Average the LVI’s determined for all the sampling units (four units are sampled) to calculate the final LVI score for the lake.

  49. Final Scoring – Metric Interpretation

  50. Metric Scoring – FDEP SOP LT7500-5.8 =IF(AD143>77,"Category I - EXCEPTIONAL",IF(AD143>37,"Category II - HEALTHY","Category III - IMPAIRED"))

More Related