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

#### Iexel

##### New Member

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?

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

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.
Code:
``````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``````

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

Replies
0
Views
583
Replies
11
Views
298
Replies
0
Views
116
Replies
2
Views
1K
Replies
7
Views
189

1,207,169
Messages
6,076,908
Members
446,239
Latest member
Home Nest

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back