# find the middle number in array

0

Hi every one ,

Does anyone help with the vba code to find the middle number in array ?

attachment  is an example , :

1. if there are 2 number in the midlle then chose the smaller one ( see sample group B or C)
2. If group has only 1 data point then chose that data ( See group D)
3. If there is one middle number then take that number ( see group A)

Thanks in advance ,

-m

### Discussion

Hi and welcome M. FIrst, we are not going to do your homework for you, but we can help you and point you in the right direction.

Second, are you sure you want to use VBA for this or do you want to use a formula for this? If you need to use VBA, then how should it function? - on a list that the user selects? automatically on a specific range? Etc. Should it only work for a group of selected numbers?
cappymer1 (rep: 120) Jul 24, '17 at 4:51 am

0

Of course, cappymer1 is right about the home work, but it's my birthday today. So you are in luck. The following function is a UDF. Place it in a standard code module (not ThisWorkbook, and not one of the Sheet modules). Call it from the worksheet by entering something like this in a cell of your choice:

`` =MiddleNumber(B1:B9)``
``````Function MiddleNumber(Rng As Range) As Double

Dim Arr As Variant
Dim n As Long
Dim i As Long

Arr = Rng.Value
On Error Resume Next
n = UBound(Arr)
If Err Then
MiddleNumber = Arr
Else
i = Int(n / 2) + (n Mod 2)
If (n Mod 2) Then
MiddleNumber = Arr(i, 1)
Else
MiddleNumber = WorksheetFunction.Min(Arr(i, 1), Arr(i + 1, 1))
End If
End If
End Function
``````

### Discussion

Happy belated birthday Variatus!!! Glad to see you back on here; your solutions are, as always, incredibly helpful :)
don (rep: 1715) Jul 26, '17 at 4:36 am
Thank you!
Variatus (rep: 2968) Jul 26, '17 at 4:45 am