find the middle number in array


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 ,




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
Add to Discussion



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:

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
        i = Int(n / 2) + (n Mod 2)
        If (n Mod 2) Then
            MiddleNumber = Arr(i, 1)
            MiddleNumber = WorksheetFunction.Min(Arr(i, 1), Arr(i + 1, 1))
        End If
    End If
End Function


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

Answer the Question

You must create an account to use the forum. Create an Account or Login