Geek Answers Handbook

How to evaluate the state depending on statistics in category groups?

Firstly, I will show a minimal example of my data and code that I still have, so it will be easier for me to explain my problem.

Consider the following data:

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

I am trying to check if lines with Cod = 7 have values ​​greater than:

 average of DBH - 1 * standard deviation of DBH.

In the above example, the average DBH is 7.34 and the standard deviation is 3.73. Therefore, DBHs should not exceed 3.61 (7.34 - 3.73) when they are labeled with Cod 7.

Cells D3 and D6 do not qualify because their DBH (C3 and C6) is greater than 3.61. Of all the lines with Cod 7, only C5 is less than 3.61.

I wrote the code below that displays a message box when such criteria are not met:

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) <> 0 And _
       ID.Offset(0, 2) > not_dominated Then
             msg = msg & "Cod 7 on " & ID.Offset(0, 3).Address() & " is incorrect" & vbLf
    End If
Next ID

If Len(msg) > 0 Then MsgBox msg

End Sub

, Esp (specie), , DBH .
> , .. .

, Esp: E_grandis E_citriodora.

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

DBH E_citriodora 1,88, - 1,03. Cod = 7 DBH 0,85 (1,88-1,03). C9 e C10 , C13.

"Esp"?

+4
3

, , . : , .

, , ( , ID).

(cell_i) C2, , (cell_e): B, , cell_i ( CountIf, , ).

Set cell_i = cell_e.Offset(1) ..

, , , cell_i C2, cell_e C7, E_grandis B 6 1 cell_i, , 5 .

C8 C12. Etc.

( "" ). For, ( cell_i cell_e, groupRange), , , A.

Select, ccell groupRange .

Option Explicit

Public Sub Cod7()

    Dim msg As String 'msg box
    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
    Dim ccell As Range 'current cell
    Dim groupRange As Range

    msg = ""
    Set cell_i = Range("C2")

    Do While cell_i.Offset(, -2) <> ""

        Set cell_e = cell_i.Offset(WorksheetFunction.CountIf(Range("B:B"), cell_i.Offset(, -1).Value) - 1)

        Set groupRange = Range(cell_i, cell_e)
        groupRange.Select

        dbh_stdev = WorksheetFunction.StDev(groupRange) 'dbh standard deviation
        dbh_avg = WorksheetFunction.Average(groupRange) '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 ccell In groupRange
            ccell.Select
            If ccell.Offset(, 1).Value = 7 And _
                ccell.Value <> 0 And _
                ccell.Value > not_dominated Then
                     msg = msg & "Cod 7 on " & ccell.Offset(, 1).Address() & " is incorrect" & vbLf
            End If
        Next

        Set cell_i = cell_e.Offset(1)

    Loop

    If Len(msg) > 0 Then MsgBox msg

End Sub
+7

script E2 ( , ):

{=IF(AND(D2=7,C2>AVERAGEIF($B$2:$B$13,B2,$C$2:$C$13)-1* STDEV(IF($B$2:$B$13=B2,$C$2:$C$13))),"warning","")}

- ctrl-shift-enter

+4

, ESP. datapoint DBH, , DBH , , / DBH ESP , DBH min/max DBH ESP .

0

More articles:


All Articles