Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim j As Long
Dim iLastRow As Long
Dim nAve As Double
Dim nStdev As Double

nAve = Application.Average(Worksheets("Data").Columns(2))
nStdev = Application.StDev(Worksheets("Data").Columns(2))
With Worksheets("Anomalies")
.Range("A1").Value = "Company"
.Range("B1").Value = "Rating"
.Range("C1").Value = "Above Ave?" & vbLf & "(" & Format(nAve,
"#,##0.00") & ")"
.Range("D1").Value = "Above STDev?" & vbLf & "(" & Format(nStdev,
"#,##0.00") & ")"
End With

j = 1
With Worksheets("Data")

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 2 To iLastRow 'iLastRow to 1 Step -1
If .Cells(i, "B").Value nAve Or .Cells(i, "B").Value nStdev
Then
j = j + 1
Worksheets("Anomalies").Cells(j, "A").Value = .Cells(i,
TEST_COLUMN).Value
Worksheets("Anomalies").Cells(j, "B").Value = .Cells(i,
"B").Value
End If
If .Cells(i, "B").Value nAve Then
Worksheets("Anomalies").Cells(j, "C").Value = "Y"
End If
If .Cells(i, "B").Value nStdev Then
Worksheets("Anomalies").Cells(j, "D").Value = "Y"
End If

Next i

End With

End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Nick" wrote in message
...
Hello

Please help.

I'm trying to program Excel to run down a column of values, taking values
that are over average(values) + stdev(values) and populating another
worksheet with the adjacent reference for these values.

for example

If I have company registration numbers in one column and their credit risk
rating in another column. I want excel to populate another worksheet with
the
company names that have a credit risk rating which is above
average(values) +
stdev(values).

I can do this manually, but I want add a button so that my colleagues can
perform the same action with exactly the same result.

Any hints would be much appreciated and I would be will to make a donation

Many thanks in advance, Nick