Select the value more approximated to the mean: How to do it

[Iexel]

How do I write the code in <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">VBA</acronym> to calculate from a list of numerical values (which I already set) which one it is more approximated to the mean?

Is there a signal to put on after If value ... mean or Is there other way to do this?

 

[mirabeau]

How do I write the code in <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">VBA</acronym> to calculate from a list of numerical values (which I already set) which one it is more approximated to the mean?

Is there a signal to put on after If value ... mean or Is there other way to do this?

here's one approach. The range including your numbers is defined in the second line. You can change this as you like, or get the code to determine it.

Sub test()
Const addr As String = "A1:A10"
Dim a As Range, c As Long, d As Long, j As Long
Set a = Range(addr)
On Error Resume Next
With Application
c = .Max(a) - .Min(a)
For j = 1 To a.Rows.Count
    If Abs(a(j, 1) - .Average(a)) < c Then
        c = Abs(a(j) - .Average(a))
        d = j
    End If
Next j
End With
On Error GoTo 0
a(d).Interior.Color = vbRed
End Sub

 

[Iexel]

Thanks. Your code is very clear, I'll do it.