[Question] Dynamic amount of averages

[technician12]

Hello.

rather complex question i assume, as i have no idea of how to go about it.

i have about 500 lines of values, i want to turn into about 10lines.
the 10 lines consisting of 10 different averages based on the 500 values.

With the first of the 10lines being the most common average
The second line being the second most common average and so on.

with hopefully, an easy way to "set value", with which to differentiate when a "new" average breakpoint, should be calculated/made.


EDIT just to make it even harder :banghead:
If only 6 averages are found within the "set value", is it then possible to only have 6 averages shown.

anyone know a way to start this ?

[JKwan]

Hopefully, this will get you a really good head start. This will create a list of unique values and FREQUENCY, in turn, your top 10 values


Option Explicit
Sub UniqueItems()
' This will display unique items using Column A
' The big difference of this routine is that it is using Dictionary
Dim myArray As Variant
Dim dDictionary As Object
Dim vItem As Variant
Dim Index As Long
Dim NewSheet As Worksheet
Dim sOutput As String

' you can use RANGE to assign to the array
'
' myArray = Range ("B2:B" & LastRow)
'
' or
myArray = ActiveSheet.[a1].Resize(ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row)
If IsEmptyArray(myArray) Then
MsgBox "You have nothing in Column A", vbCritical, "Nothing in Column A"
Else
Set dDictionary = CreateObject("scripting.dictionary")
For Index = 1 To UBound(myArray)
dDictionary(myArray(Index, 1)) = dDictionary(myArray(Index, 1)) + 1
Next

If SheetExists("Unique List") Then
Application.DisplayAlerts = False
Worksheets("Unique List").Delete
Application.DisplayAlerts = True
End If
Set NewSheet = Worksheets.Add
NewSheet.Name = "Unique List"

NewSheet.Select
With NewSheet
.[A1:B1] = Split("Item Frequency", " ")
.[A2].Resize(dDictionary.Count) = Application.Transpose(dDictionary.Keys)
.[B2].Resize(dDictionary.Count) = Application.Transpose(dDictionary.Items)
End With

' Output onto screen
With dDictionary
For Each vItem In .Keys
sOutput = sOutput & vbLf & vItem & " (" & .Item(vItem) & ")"
Next
End With
Set dDictionary = Nothing
End If
End Sub
Public Function IsEmptyArray(InputArray As Variant) As Boolean

On Error GoTo ErrHandler:
IsEmptyArray = Not (UBound(InputArray) >= 0)
Exit Function

ErrHandler:
IsEmptyArray = True
On Error GoTo 0
End Function
Function SheetExists(sName As String, Optional oWb As Workbook) As Boolean

'Returns true if sheet exists in the specified workbook.
'If no workbook supplied, the activeworkbook is used.

If oWb Is Nothing Then
Set oWb = ActiveWorkbook
End If

On Error Resume Next
SheetExists = CBool(Not oWb.Sheets(sName) Is Nothing)
On Error GoTo 0
End Function

[Paul_Hossler]

i have about 500 lines of values, i want to turn into about 10 lines.
the 10 lines consisting of 10 different averages based on the 500 values.


My simplistic brain tells me that if you take the average of 500 lines 10 times, you'll get the same number 10 times

So I know that there must be something I'm missing




With the first of the 10 lines being the most common average
The second line being the second most common average and so on.

What did I miss?


Can you attach a sample workbook?

[technician12]

Can you attach a sample workbook?

yes

also, if possible without VBA, i'd greatly appreciate it, as it would get far easier to get approved inhouse.

[Paul_Hossler]

Can you explain what you mean by



1. Sort all within ±500 of each other

2. the 10 ( or less ) most common averages, considering the ±500 interval of each other




With examples if you can

[technician12]

to be honest, i've found a much cleaner solution to my end goal, than using the system i requested here, but for pure interest i'll explain :)

i wanted the list of 500 values to sorted and averaged based on the value in I6 based on how common they were.

Example would be, if the value in I6 was 100.
then, it would look for the most common range spread, in the 500's values within ±100 and return the average of the 500 most commonly found within ±100 in M6, and the second most common in M7 and so on.