title: How to evaluate a condition dependent on statistics within groups of categories?
body: <p>Firstly, I'll show a minimal example of my data and the code I have so far, so it will be easier to explain my issue.</p>
  
  <p>Consider the following data:</p>
  
  <pre><code>ID  Esp         DBH     Cod
  55  E_grandis   9.00    
  55  E_grandis   9.71    7
  55  E_grandis   10.00   
  55  E_grandis   1.00    7
  55  E_grandis   7.00    7
  55  E_grandis           1
  </code></pre>
  
  <p>I am trying to verify if rows with Cod = 7 have values greater than the:</p>
  
  <pre><code> average of DBH - 1 * standard deviation of DBH.
  </code></pre>
  
  <p>In the example above the average of DBH is 7.34 and the standard deviation is 3.73. Therefore, DBHs values should not be greater than 3.61 (7.34 - 3.73) when they are tagged Cod 7.</p>
  
  <p>Cells D3 and D6 do not pass in the criteria because their DBHs (C3 and C6) are greater than 3.61. Among all rows with with Cod 7, only C5 is smaller than 3.61.</p>
  
  <p>I wrote the code below which displays a message box when such criteria is not met:</p>
  
  <pre><code>Sub Cod7()
  
  Dim msg As String 'msg box
  Dim ID As Range
  Dim dbh_stdev As Double 'standard deviation of dbh
  Dim dbh_avg As Double 'average of dbh
  Dim not_dominated As Double 'criteria threshold (upper bound)
  Dim cell_i As Range 'initial of array to compute average and standard deviation
  Dim cell_e As Range 'end of array to compute average and standard deviation
  
      msg = ""
      Set cell_i = Range("C2")
      Set cell_e = Range("C7")
  
      dbh_stdev = WorksheetFunction.StDev(Range(cell_i, cell_e)) 'dbh standard deviation
      dbh_avg = WorksheetFunction.Average(Range(cell_i, cell_e)) 'dbh average
      not_dominated = dbh_avg - dbh_stdev 'upper bound
  
  'searches cells with cod 7 on column Cod, and it displays a message box if
  'DBH is greater than the 'not_dominated' variable value
  For Each ID In Range("A2", Range("A2").End(xlDown))
      If ID.Offset(0, 3) = 7 And _
         ID.Offset(0, 2) &lt;&gt; 0 And _
         ID.Offset(0, 2) &gt; not_dominated Then
               msg = msg &amp; "Cod 7 on " &amp; ID.Offset(0, 3).Address() &amp; " is incorrect" &amp; vbLf
      End If
  Next ID
  
  If Len(msg) &gt; 0 Then MsgBox msg
  
  End Sub
  </code></pre>
  
  <p>Now the problem is that in my real data I have more than one category under the column Esp (specie), and I need to evaluate the criteria, calculating the average and standard deviation of DBHs inside each group of specie.<br>
  The groups of species are clustered, i.e., one specie occurs through adjacent rows.   </p>
  
  <p>For example, this is a minimal data with two categories under the Esp column: E_grandis and E_citriodora.</p>
  
  <pre><code>ID  Esp           DBH    Cod
  55  E_grandis     9.00  
  55  E_grandis     9.71   7
  55  E_grandis     10.00 
  55  E_grandis     1.00   7
  55  E_grandis     7.00   7
  55  E_grandis            1
  55  E_citriodora  3.00  
  55  E_citriodora  2.00   7
  55  E_citriodora  2.00   7
  55  E_citriodora         1      
  55  E_citriodora         1
  55  E_citriodora  0.50   7
  </code></pre>
  
  <p>The average of DBH in E_citriodora is 1.88 and the standard deviation is 1.03. Rows with Cod = 7 cannot have DBH greater than 0.85 (1.88-1.03). In this case, cells C9 e C10 don't pass the criteria and cell C13 passes.</p>
  
  <p>How can I adapt the code to apply such criteria within groups of 'Esp'?</p>