I am trying to write a VBA function that would give me the average and stdev of a fractile of data (assuming total 5 fractiles). The data would be either a row vector or a Column vector. I have written down the following till now :
Function AverageFractile(ret As Range, fractile As Integer) As Variant
Dim lenRange As Integer
Dim startIndex As Integer
Dim endIndex As Integer
Dim subRange As Variant
'Arrange the range object in ascending Order, doesn't work
' ret = Sort(ret, XlSortOrder = xlAscending)
'Getting the indices that would be used to slice the input range to get the relevant fractile
lenRange = Application.WorksheetFunction.Max(ret.Rows.Count, ret.Columns.Count)
startIndex = (lenRange * (fractile - 1)) \ 5 + 1
endIndex = (lenRange * fractile) \ 5
' subRange = ret(Cells(startIndex,1),Cells(endIndex,1))
' This is not working
End Function
I am stuck at two places till now:- a) I am trying to sort the data, but the function Sort is not working, how do I sort the range object which is the input in ascending order?
b) How do I select a subrange from range object, so that I can calculate its average and Stdev?
Appreciate the help, I have been trying for a couple of hours now and haven't been able to work it out.
It's not uncommon (for me, at least) to need a reference to a sub-range of a larger range. The Range constructor (property? looks like a constructor to me in this context) appears to only accept string arguments of the "A4:G7" variety--not even "R4C1:R7C7" style, which is easier to code on the fly from row and column indices.
I suppose VBA does not allow overloading, but it's exasperating that MS doesn't see fit to provide useful functions for this kind of thing. AFAIK (which isn't that far for me in VBA) there is no range-like constructor that takes row and column indices, or cell pairs for that matter. The .Rows and .Columns properties don't do what I expected them to do either. Other than .Rows.Count and .Columns.Count, I'm not sure what they are good for (is my inexperience showing? That's ok--help me out.)
I got tired of writing the simple but tedious code to convert row and column indices to the "A4:G7" style strings inline, so I wrote a simple function to take care of it.
In the calling function:
Here's the getSubRange utility function:
Don't forget that the sub-range is a reference to part of the source range, not a copy of a part of it: any change you make to the sub-range is a change to the source range. But I guess that's obvious enough.
As I said in my comment, there's "too many questions" in your question. However, here is my answer to your "title" question: