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

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"))