You are here: Publications 
Water A User Guide for the Macroinvertebrate Community Index Online version
Appendix 1: Calculating the MCI: Excel Macros and User-defined Functions
The MCI, SQMCI and QMCI can be calculated manually on spreadsheets, but speed and accuracy are improved if macros or user-defined functions are used. Spreadsheets containing these macros and user-defined functions are available from the first author of this report (John Stark).
The following Excel macros enable MCI, SQMCI and QMCI values to be calculated on a spreadsheet that has coded abundance data entered as R, C, A, VA or VVA. Absences can be blank cells, or indicated by a dash [enter '- (open single quote followed by a dash) in the cell - this prevents Excel from treating the dash as a negative sign or subtraction]. The same macros can be used to calculate the MCI-sb, SQMCI-sb or QMCI-sb if soft-bottomed tolerance values are used.
MCI from coded abundance data
=SUMIF(D$8:D$135,">@",$C$8:$C$135)/COUNTIF(D$8:D$135,">@")*20
MCI from count data
=SUMIF(D$8:D$135,">0",$C$8:$C$135)/COUNTIF(D$8:D$135,">0")*20
SQMCI from coded abundance data
=(SUMIF(D$8:D$135,"=R",$C$8:$C$135)*1+SUMIF(D$8:D$135,"=C",$C$8:$C$135)*5+SUMIF(D$8:D$135,"=A",$C$8:$C$135)*20+SUMIF(D$8:D$135,"=VA",$C$8:$C$135)*100+SUMIF(D$8:D$135,"=VVA",$C$8:$C$135)*500)/(COUNTIF(D$8:D$135,"R")*1+COUNTIF(D$8:D$135,"C")*5+COUNTIF(D$8:D$135,"A")*20+COUNTIF(D$8:D$135,"VA")*100+COUNTIF(D$8:D$135,"VVA")*500)
QMCI from count data
=(SUMPRODUCT($C$8:$C$135,D$8:D$135))/SUM(D$8:D$135)
These macros are entered in a cell at the bottom of column D in an Excel spreadsheet. Tolerance values are in column C (C8:C135) and the first column of data is in column D (D8:D135). The absolute cell references (indicated by '$' signs in the above formulae) enable dragging to the right to calculate MCI, SQMCI and QMCI values for data in columns E, F, G, ... etc.
User-defined Excel functions for calculating biotic indices
The following Excel user-defined functions enable the MCI, SQMCI and QMCI values to be calculated on a spreadsheet. Coded abundance data are entered as R, C, A, VA or VVA. Absences can be blank cells, or indicated by a dash (enter '- in the cell). These functions can be used to calculate the MCI-sb, SQMCI-sb, or QMCI-sb if soft-bottomed tolerance values are used. Note that the functions require the macro security level in Excel to be set to medium or low.
These user-defined functions must be in memory (i.e. part of the spreadsheet on which the calculations are to be made, or in a separate open spreadsheet) in order to be used. To insert a calculated MCI value, place the cursor in the cell where the result is required (say in cell D136 below the first column of data) and click on <insert.function.user defined>. Double-click on the MCI function and enter the range of tolerance values (e.g. C8:C135) into the box labelled 'Scores'. This function argument can also be entered by highlighting the range of scores. Manually make these references absolute by adding $ signs (e.g. $C$8:$C$135). Click into the box to the right of the 'Data_column' function argument.
Finally, click anywhere on the spreadsheet in the column of data above the cell containing the function (say cell D20). Making the tolerance value references absolute (both row and column) enables additional MCI values to be calculated by dragging to the right below additional columns of data, and SQMCI or QMCI values (depending on the data type in the spreadsheet) can be calculated by dragging the function in D136 down one cell and manually changing the name from MCI to SQMCI or QMCI.
For example, assuming that you have a spreadsheet called "indices.xls" containing the user-defined functions for calculating MCI, SQMCI, and QMCI values, and the tolerance values are in column C (from row 8 to row 135). If you want to calculate the MCI for data in column D in cell D136, then in cell D136 you should have the following (the '20' after the 'D' can be any number).
=indices.XLS!MCI($C$8:$C$135,D20)
Dragging to the right simply changes the 'D' to 'E', 'F', 'G', etc. and calculates MCI values for data in each column. If you drag down from cell D136 into D137 and change the entry to
=indices.XLS!SQMCI($C$8:$C$135,D20)
or
=indices.XLS!QMCI($C$8:$C$135,D20)
depending on the data type, you will get SQMCI or QMCI values. These cells can be dragged to the right too, in order to calculate SQMCI or QMCI values for additional columns of data.
Change the number of significant figures - by convention none for MCI and two for SQMCI or QMCI.
Finally, select the rows containing these index calculations and select <copy> and <paste.special.values> to replace the functions with values. If this is not done, the calculated index values will only be displayed when indices.xls is in memory and will be lost otherwise.
A spreadsheet (indices.xls) containing user-defined functions for calculating MCI, SQMCI, and QMCI values is available from the first author of this report.
MCI from presence-absence, coded abundance or count data
Option Base 1
Dim score(), data$(), column()
Function MCI(scores, data_column)
'This function calculates MCI from presence-absence, count or coded abundance data
ReDim score(scores.Count), data$(scores.Count), column(scores.Count)
Row = scores.Row
ScoresCol = scores.column
DataCol = data_column.column
ScoringTaxa = 0
For i = 1 To scores.Count
' Read in the column of MCI taxa scores
score(i) = Cells(Row, ScoresCol)
' Read in the column of counts or coded abundance (i.e. R/C/A/VA/VVA) data
data$(i) = Cells(Row, DataCol)
Row = Row + 1
' test to see if counts or coded abundances
' blank cells and dashes (to indicate absent taxa) are < "0"
' and replace data values with 0 (for absent) or 1 (for present)
If data$(i) > "0" Then column(i) = 1 Else column(i) = 0
' only add to scoring taxa if there is an entry in the range 1 - 10 inclusive
' in the taxa scores column
If score(i) > 0 And score(i) < 11 Then ScoringTaxa = ScoringTaxa + column(i)
MCI = MCI + (score(i) * column(i))
Next i
MCI = MCI / ScoringTaxa * 20
End Function
SQMCI from coded-abundance data
Option Base 1
Dim scores(), column()
Function SQMCI(scores, data_column)
' This function calculates SQMCI from coded abundance data
' Criteria for assigning coded abundances when sorting samples
' R = 1 - 4 animals
' C = 5 - 19
' A = 20 - 99
' VA = 100 - 499
' VVA = 500+
ReDim score(scores.Count), data$(scores.Count)
Row = scores.Row
ScoresCol = scores.column
DataCol = data_column.column
For i = 1 To scores.Count
score(i) = Cells(Row, ScoresCol)
data$(i) = Cells(Row, DataCol)
Row = Row + 1
ScoringTaxa = ScoringTaxa + Sgn(Val(data$(i)))
Select Case data$(i)
Case "R"
R = R + score(i) * 1
TotalR = TotalR + 1
Case "C"
C = C + score(i) * 5
TotalC = TotalC + 5
Case "A"
A = A + score(i) * 20
TotalA = TotalA + 20
Case "VA"
VA = VA + score(i) * 100
TotalVA = TotalVA + 100
Case "VVA"
VVA = VVA + score(i) * 500
TotalVVA = TotalVVA + 500
Case Else
End Select
Next i
TotalCounts = TotalR + TotalC + TotalA + TotalVA + TotalVVA
SQMCI = (R + C + A + VA + VVA) / TotalCounts
End Function
QMCI from count data
Option Base 1
Dim scores(), column()
Function QMCI(scores, data_column)
'This function calculates QMCI from count data
ReDim score(scores.Count), data(scores.Count)
Row = scores.Row
ScoresCol = scores.column
DataCol = data_column.column
For i = 1 To scores.Count
score(i) = Cells(Row, ScoresCol)
data(i) = Cells(Row, DataCol)
Row = Row + 1
If InStr(data(i), "-") > 0 Then data(i) = 0
If score(i) > 0 And score(i) < 11 Then
ScoringTaxa = ScoringTaxa + Sgn(data(i))
TotalCounts = TotalCounts + data(i)
End If
Next i
For i = 1 To scores.Count
QMCI = QMCI + (score(i) * data(i) / TotalCounts)
Next i
End Function
