Dynamic Frequency Function VBA

[Excel_Kid1081]

Hello-

I am trying to create a frequency function that dynamically creates the bins and then counts the occurences for each....however, I am having some trouble getting what I have to work....

I know about the Partition function, however, my dataset will have negative values in it thus I need a custom function.

Function freq_bins(ret As Single, min As Single, max As Single, categories As Integer)
Dim Bins As Integer
Dim bin As Integer

Bins = (max - min) / categories

For i = 1 To 1
bin(i) = min
Next i

For i = 1 To categories
bin(i) = bin(1) + Bins
Next i

freq_bins = bin.Count

End Function

Any points in the right direction are greatly appreciated.

Thank you,

EK

 

[gemma-the-husky]

is the bin every discrete value or are they in ranges

ie how many bins do you need, based on the min and max values

what are you storing in each bin section, as this will have a bearing on it

 

[Excel_Kid1081]

thanks for the reply. the bin values will be discrete as I would like to determine the number of bins (that is why I have it as a input variable in the function "categories")

I will be storing in the bins the counts of each value in the table that fall within each just like the partition function except some of my values will be negative...

If I can just get some help on creating the bins I think a simple Aggregate Count of the records in the query along with a GROUP BY should give me what I am looking for...

thank you!!!

 

[Excel_Kid1081]

Here is some SQL I picked up from searching.....

SELECT COUNT(qryIndexRets.INDEX_RET) AS CountOfRets, Int(Min(qryIndexRets.INDEX_RET)) & "-" & (int(min(qryIndexRets.INDEX_RET)+max(qryIndexRets.INDEX_RET)-min(qryIndexRets.INDEX_RET))/5) as [group]
FROM qryIndexRets
GROUP BY Int(Min(qryIndexRets.INDEX_RET)) & "-" & (int(min(qryIndexRets.INDEX_RET)+(max(qryIndexRets.INDEX_RET)-min(qryIndexRets.INDEX_RET))/5)
ORDER BY Int(qryIndexRets.INDEX_RET);

If I can somehow get the [group] to be an additive from the min then I think this query will work....

 

[gemma-the-husky]

cant you just do a totals query to select and count the different numbers of each value in your table