So I dont really want to just give you code to copy paste and let that be that, no one learns new things like that.

What are your reasons for wanting to turn this into a function? do you want to do this in VBA or is it just that you want to have something less verbose to type in?

The function provided has a number of unnecessary steps.
Firstly the check for less than 6 is repeated. This is easy to remove, although I am a little surprised you had it there, was there a reason or just bad copy/paste?

Then the check for even or odd numbers is not necessary if you understand that the average of the same number no matter how many instances of it you have is always itself. (12 + 12)/2 will always give 12.

The (50/100)* x is the same as x/2. his is both clearer and more compact.

It appears that the MOD 1 check is just there to check if the number is odd or even? Instead, we can use FLOOR and CEILING to give us the index to either side of the numbers that are not a full integer.

With these changes, lets see how the original function now looks:

=IF(
COUNT(C8:C57)<6,
"",
AVERAGE(
SMALL(C8:C57, FLOOR(COUNT(C8:C57)/2, 0)),
SMALL(C8:C57, CEILING(COUNT(C8:C57)/2, 0))
)
)


Much nicer.

but you wanted a VBA function right?

Public Function DoTheThing(r as Range, Threshold as long) as variant
Dim cnt as long
dim small1 as double, small2 as double
DoTheThing = ""
cnt = worksheetfunction.count(r)
If cnt < threshold then exit function
small1 = worksheetfunction.small(r, RoundDown(cnt/2, 0))
small2 = worksheetfunction.small(r, RoundUp(cnt/2, 0))
DoTheThing = (small1 + small2) / 2
end function

There is also a second argument to allow you to change the threshold for giving back a value, set this to 6 to duplicate the functionality of your original function.

I havent actually typed any of this into excel so if there is a bug I apologise, but surely you would be able to troubleshoot it from here right?